TUNE TABLE (SQL)
TUNE TABLE tablename [ tune_options ]
|tablename||The name of an existing table from which to gather statistics. The table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.|
|tune_options||Optional — If specified, one or more TUNE TABLE options, specified in any order, separated by spaces. These tune_options are not case sensitive.|
The TUNE TABLE command gathers the statistics of an existing table based on the data currently in the table. This data should be representative of the data expected when the table is fully populated.
TUNE TABLE calculates and sets the BlockCount and extent size of the table, as well as the selectivity for each field, based on representative data. Normally, TUNE TABLE sets one or more of these values, and purges all cached queries that use this persistent class (table) so that queries will use these new values. However, if TUNE TABLE does not change any of these values (for example, if the data has not changed since the last time TUNE TABLE was run against this table) cached queries are not purged and the table’s class definition is not flagged for recompile.
TUNE TABLE updates the SQL table definition (and therefore requires privileges to alter the table definition). Commonly, TUNE TABLE also updates the corresponding persistent class definition. This allows the gathered statistics to be used by the query optimizer without requiring a class compilation. However, if a class is deployed, TUNE TABLE only updates the SQL table definition; the query optimizer indirectly uses the gathered statistics from the table definition.
If TUNE TABLE is successful, it sets SQLCODE = 0. If the specified tablename does not exist, TUNE TABLE issues an SQLCODE -30 error.
The TUNE TABLE command is a privileged operation. The user must have %ALTER_TABLE administrative privilege to execute TUNE TABLE. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %ALTER_TABLE privileges. You can use the GRANT command to assign %ALTER_TABLE privileges to a user or role, if you hold appropriate granting privileges. Administrative privileges are namespace-specific. For further details, refer to Privileges in Using InterSystems SQL.
The user must have %ALTER privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. Otherwise, the user must be granted %ALTER privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' is not privileged for the operation. You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can use the GRANT command to assign %ALTER privilege to a specified table. For further details, refer to Privileges in Using InterSystems SQL.
TUNE TABLE Options
%CLEAR_VALUES: if specified the existing SELECTIVITY, EXTENTSIZE, etc. values are cleared from the class and table definition. Not specifying this option provides the default Tune Table behavior.
%SAMPLE_PERCENT percentage: specifies the percentage of rows of the table to be used for sampling the data for the Tune Table utility. This percentage can be specified as .## or ##%; for example, either .12 or 12% will cause to use 12% of the rows in the table when sampling the data. Specify percentage with a value greater than 0 and less than or equal to 100%; a value out of this range issues an SQLCODE -1 error. This value does not usually need to be specified. Only specify this value when potential outlier values for a field are not evenly distributed among rows throughout the table. Note, for any table with an extentsize < 1000 rows, the entire extent will be used by Tune Table regardless of the %SAMPLE_PERCENT value.
%RECOMPILE_CQ: if specified, instead of just purging cached queries for the table that was tuned, Tune Table will instead recompile the cached query classes using the new Tune Table statistics. Not specifying this option provides the default Tune Table behavior.
If the specified tune_options value does not exist, TUNE TABLE issues an SQLCODE -25 error. If the same tune_options value is specified twice, TUNE TABLE issues an SQLCODE -326 error.
Executing TUNE TABLE creates a cached query. The Show Plan display indicates that no Query Plan is created. No SQL Statement is created. The cached query is general to the namespace; it is not listed for the specific table. You can re-run the same TUNE TABLE statement using the cached query.
Executing TUNE TABLE purges all existing cached queries for the specified table, including the cached query for the previous execution of TUNE TABLE. You can optionally have TUNE TABLE recompile all of these cached queries with the new Tune Table values.
If running TUNE TABLE does not change any Tune Table values, cached queries are not purged.
Other Ways to Run Tune Table
There are two other interfaces for running Tune Table:
Using the Management Portal SQL interface Actions drop-down list, which allows you to run Tune Table on a single table or on all of the tables in a schema.
Invoking the $SYSTEM.SQL.Stats.Table.GatherTableStats()Opens in a new tab method for a single table, or all tables in the current namespace.
For further details, refer to Tune Table in the “Optimizing Tables” chapter of the SQL Optimization Guide.
The following example gathers table statistics and recompiles cached query classes based on the newly gathered statistics:
TUNE TABLE Sample.MyTest %RECOMPILE_CQ
Tune Table in the “Optimizing Tables” chapter of the SQL Optimization Guide
ExtentSize, Selectivity, and BlockCount in the “Optimizing Tables” chapter of the SQL Optimization Guide