 |
Qore SqlUtil Module Reference 1.9.2
|
|
Qore SqlUtil Module Reference 1.9.2
|
This part of SqlUtil contain some additional tools to handle subset of database administration. Common application developer usually does not need to handle anything described in this section on his own.
DBA tools are part of the AbstractDatabase (or Database) classes. Methods described below are wrappers around database specific commands.
Index rebuilding is quite controversial task and it is highly variable for each database server. And even worse - it's varying between database server versions too. Modern database servers usually do not need to rebuild indexes in regular operations, but there can be situations when it's required still. Like: testing DB features, index corruption as a result of HW failure, storage parameters changing etc.
The easiest criteria which can be quickly and easily given to you is it not to rebuild or coalesce any index unless you have a specific, proven reason for doing so.
rebuildIndex() methods always takes a index name or an SqlUtil::AbstractIndex instance as first argument. The second argument is an optional hash with rebuild index options. These options are specific for each database server. See driver-specific modules for more info.
Oracle backend implementation uses ALTER INDEX REBUILD statement internally.
| Option | Type | Description |
parallel | Boolean | Use parallel keyword in the statement if it's set to True |
logging | Boolean | Use logging keyword in the statement if it's set to True. By default nologging keyword is used. |
statistics | Boolean | Use compute statistics keyword in the stetement if it's set to True |
tablespace | String | Rebuild index in given tablespace |
cond_rebuild | Boolean | Rebuild index only when it meets INDEX_STATS criteria |
cond_maxheight | Integer | maximum index height before rebuilding index |
cond_maxleafpct | Integer | maximum percentage of deleted leaf nodes before rebuilding index |
PostgreSQL uses REINDEX INDEX statement internally.
There are no special options for PostgreSQL.
MySQL does not offer any command to rebuild an index. Exception MYSQL-REBUILD-INDEX-ERROR is thrown if is the rebuildIndex method called for MySQL.
Use reclaimSpace method for MySQL.
Database statistics are critical metadata used by SQL optimizers to build as good execution plans as possible. Some DB environments refresh statistics on regular basis, some not. All is usually highly configured by DBA.
The optional argument holds options for statistics. These options are specific for each database server. See driver-specific modules for more info.
Oracle uses DBMS_STATS package internally. With GATHER_SCHEMA_STATS or GATHER_TABLE_STATS depending on options set below.
Statistics are calculated in currently logged user's schema.
Please consult Oracle documentation for exact option meanings.
| Option | Type | Description |
tables | list of strings | If it's set, the table by table statistics are computed with GATHER_TABLE_STATS. Otherwise the full schema with GATHER_SCHEMA_STATS is used. |
estimate_percent | Integer | A percent as integer given. DBMS_STATS.AUTO_SAMPLE_SIZE is used as a default |
block_sample | Boolean | Whether use random block sampling |
method_opt | String | an optional size clause. By default "for all columns size auto" is used. |
degree | Integer | a degree of parallelism. Default is NULL. |
granularity | String | granularity of statistics to collect. Default is "ALL" |
cascade | Boolean | True = gather statistics on indexes as well, False = do not make statistics for indexes. By default "dbms_stats.auto_cascade" is used. |
stattab | String | user statistics table identifier describing where to save the current statistics |
statid | String | identifier to associate with these statistics within stattab |
options | String | further specification of which objects to gather statistics. By default "gather auto" |
statown | String | schema containing stattab (if different than ownname) |
Example: Compute statistics for enumerated tables only.
PostgreSQL uses ANALYZE TABLE internally.
| Option | Type | Description |
tables | list of strings | If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used. |
MySQL uses ANALYZE TABLE internally.
Please consult MySQL documentation for exact option meanings.
| Option | Type | Description |
tables | list of strings | If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used. |
no-binlog | Boolean | If True, the "no_write_to_binlog" is used. If False (default), the "local" keyword is used. |
Space reclaiming can be done if there was a huge deletion from some tables. As usual causes are DB-dependent.
The optional argument holds options for space management. These options are specific for each database server. See driver-specific modules for more info.
Oracle uses ALTER TABLE SHRINK SPACE CASCADE statement internally. So it requires enabled row movement.
Please consult Oracle documentation for more information.
| Option | Type | Description |
tables | list of strings | If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used. |
PostgreSQL uses VACUUM internally.
Please consult PostgreSQL documentation for exact option meanings.
| Option | Type | Description |
tables | list of strings | If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used. |
full | Boolean | True if full keyword should be used |
analyze | Boolean | True if analyze keyword should be used |
MySQL uses OPTIMIZE TABLE internally.
Please consult MySQL documentation for exact option meanings.
| Option | Type | Description |
tables | list of strings | If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used. |
no-binlog | Boolean | If True, the "no_write_to_binlog" is used. If False (default), the "local" keyword is used. |