Qore SqlUtil Module Reference  1.4
DBA Management

Introduction to DB Administration

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.

Warning
Database administration is complex task and it requires knowledge in various areas of IT. Always ask your DBA if and when you can use these tools.

DBA tools are part of the AbstractDatabase (or Database) classes. Methods described below are wrappers around database specific commands.

Index Management

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.

new Database(ds).rebuildIndex("ix_foo_bar");

Oracle Options

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 Options

PostgreSQL uses REINDEX INDEX statement internally.

There are no special options for PostgreSQL.

MySQL Options

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

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 Options

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.

hash opts = (
"tables": ("workflows", "workflow_instance"),
);
db.computeStatistics(opts);

PostgreSQL Options

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 Options

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 Management

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 Options

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 Options

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 Options

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.