docs.intersystems.com
Home / InterSystems SQL Reference / SQL Commands / TUNE TABLE

InterSystems SQL Reference
TUNE TABLE
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


Tunes a table based on representative data.
Synopsis
TUNE TABLE tablename [ tune_options ]
Arguments
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 system 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.
To execute a TUNE TABLE statement, the user must have the %ALTER_TABLE SQL system privilege. To execute TUNE TABLE the user must have %ALTER privilege on the table being tuned.
If TUNE TABLE is successful, it sets SQLCODE = 0. If the specified tablename does not exist, TUNE TABLE issues an SQLCODE -30 error. If the user does not have %ALTER privileges for the specified table, TUNE TABLE issues an SQLCODE -99 error.
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 Options
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
  }
See Also


Previous section           Next section
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-04-23 13:43:23