Skip to main content
Previous sectionNext section

TUNE TABLE

Tunes a table based on representative data.

Synopsis

TUNE TABLE tablename [ tune_options ]

Arguments

Argument Description
tablename The name of an existing table to be tuned. 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.

Description

The TUNE TABLE command tunes 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 blocksize, selectivity, and extent size of the table, 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 tuned values can 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 tuned values 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.

Privileges

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

  • %KEEP_UP_TO_DATE: if not specified (the default), the up-to-date flag on the modified class definition is not set. This indicates that the class definition is out of date and is flagged for recompile. If specified, the class definition remains flagged as up-to-date. This is the preferred option when making changes to statistics on a live system, because it makes it less likely that a table class definition will be recompiled.

  • %CLEAR_VALUES: if specified, calls TuneTable with ClearValues=1. When ClearValues=1, the existing SELECTIVITY, EXTENTSIZE, etc. values are cleared from the class and table definition. The default is ClearValues=0, so not specifying this option provides the default TuneTable behavior.

  • %SAMPLE_PERCENT percentage: if specified, calls TuneTable with a percentage value passed to the SamplePercent argument of TuneTable. This specifies the percentage of rows of the table to be used for sampling the data for the TuneTable utility. This percentage can be specified as .## or ##%; for example, either .12 or 12% will cause TuneTable 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 when calling TuneTable. 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 TuneTable regardless of the %SAMPLE_PERCENT value.

  • %RECOMPILE_CQ: if specified, calls TuneTable with RecompileCQ=1. When RecompileCQ=1, instead of just purging cached queries for the table that was tuned, TuneTable will instead recompile the cached query classes using the new Tune Table statistics. The default is RecompileCQ=0, so not specifying this option would provide the default TuneTable 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.

For further details on these options refer to the $SYSTEM.SQL.TuneTable() method.

Cached Queries

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:

For further details, refer to Tune Table in the “Optimizing Tables” chapter of the SQL Optimization Guide.

Examples

The following Dynamic SQL example tunes a table:

  TRY {
  SET mysql = "TUNE TABLE Sample.MyTest %KEEP_UP_TO_DATE"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mysql)
     IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
     IF rset.%SQLCODE=0 { WRITE !,"Executed Tune Table",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  RETURN
  }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
              RETURN
  }
Copy code to clipboard

See Also