%SYSTEM.SQL.Stats.Table
abstract class %SYSTEM.SQL.Stats.Table extends %SYSTEM.Help
Method Inventory
- ClearSchemaStats()
- ClearTableStats()
- Export()
- GatherSchemaStats()
- GatherTableStats()
- Import()
- SetExtentSize()
- SetFieldSelectivity()
Methods
classmethod ClearSchemaStats(schema As %Library.String) as %Status
Clear the table stats (selectivity, histogram info, extent size, etc.) for all classes/tables and their properties/fields within a schema.
Parameters:
Parameters:
- schema
- Name of a schema to tune tables. If the schema name is omitted, the default schema is used.
Examples:
- Do $SYSTEM.SQL.Stats.Table.ClearSchemaStats("MedLab")
- Do $SYSTEM.SQL.Stats.Table.ClearSchemaStats("") ; Clear the table stats for SQLUser schema
classmethod ClearTableStats(table As %Library.String) as %Status
Clear the table stats (selectivity, histogram info, extent size, etc.) for a class/table and its properties/fields.
Parameter:
Parameter:
- table
- Name of a table or "*" to Clear the table stats for all tables in the current namespace. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.
Examples:
- Do $SYSTEM.SQL.Stats.Table.ClearTableStats("MedLab.Patient")
- Do $SYSTEM.SQL.Stats.Table.ClearTableStats("""Medical Lab"".Patient")
- Do $SYSTEM.SQL.Stats.Table.ClearTableStats("IscPerson") ; Clear the table stats for SQLUser.IscPerson
classmethod Export(pFilename As %Library.String, pSchemaFilter As %Library.String = "", pTableFilter As %Library.String = "", pDisplay As %Library.Boolean = 1) as %Library.Status
Export extentsize and selectivity for tables/fields to an XML file.
Generated file can be loaded using $SYSTEM.SQL.Stats.Table.Import().
Parameter:
Parameter:
- pFilename
- Name of the file to output the table(s) tuning statistics to.
- pSchemaFilter
- Filter to limit the schemas output. The default is "", which means there is no filter applied and all schemas in the namespace are exported. pSchemaFilter uses '_' to signify any single character, '*' to signify 0 through N characters, and ' to signify NOT.
- pTableFilter
- Filter to limit the tables output. The default is "", which means there is no filter applied and all tables in the specified schemas are exported. pTableFilter uses '_' to signify any single character, '*' to signify 0 through N characters, and ' to signify NOT.
- pDisplay
- TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is TRUE (1).
Examples:
- Do $SYSTEM.SQL.Stats.Table.Export("C:\AllStats.xml") // Exports TuneTable Statistics for all schemas/tables in the current namespace
- Do $SYSTEM.SQL.Stats.Table.Export("C:\SampleStats.xml","Sample") // Exports TuneTable Statistics for all Sample.* tables in the current namespace
- Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePStats.xml","Sample","P*") // Exports TuneTable Statistics for all Sample.P* in the current namespace
- Do $SYSTEM.SQL.Stats.Table.Export("C:\SamplePersonStats.xml","Sample","Person") // Exports TuneTable Statistics for table Sample.Person in the current namespace
classmethod GatherSchemaStats(schema As %Library.String, logFile As %Library.String = "") as %Status
Calculate and update extentsize and selectivity for all classes/tables and their properties/fields within a schema.
Parameters:
Parameters:
- schema
- Name of a schema to tune tables. If the schema name is omitted, the default schema is used.
- logFile
- Optional name of a file to log the output of the TuneTable utility to. If logFile isn't supplied, the output will go to the current device.
Examples:
- Do $SYSTEM.SQL.Stats.Table.GatherSchemaStats("MedLab","TuneLog.txt")
- Do $SYSTEM.SQL.Stats.Table.GatherSchemaStats("""Medical Lab""")
- Do $SYSTEM.SQL.Stats.Table.GatherSchemaStats("") ; Tunes SQLUser schema
classmethod GatherTableStats(table As %Library.String, logFile As %Library.String = "") as %Status
Calculate and update extentsize and selectivity for a class/table and its properties/fields.
Parameter:
Parameter:
- table
- Name of a table or "*" to tune all tables in the current namespace. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.
- logFile
- Optional name of a file to log the output of the TuneTable utility to. If logFile isn't supplied, the output will go to the current device.
Examples:
- Do $SYSTEM.SQL.Stats.Table.GatherTableStats("MedLab.Patient","Tune.log")
- Do $SYSTEM.SQL.Stats.Table.GatherTableStats("""Medical Lab"".Patient")
- Do $SYSTEM.SQL.Stats.Table.GatherTableStats("IscPerson") ; Tunes SQLUser.IscPerson
classmethod Import(pFilename As %Library.String, pDisplay As %Library.Boolean = 1, pClearCurrentStats As %Library.Boolean = 0) as %Library.Status
Import extentsize, selectivity, blockcount for a table and its fields from a file created by $SYSTEM.SQL.Stats.Table.Export().
Parameter:
Parameter:
- pFilename
- Name of the file to output the table(s) tuning statistics to.
- pDisplay
- TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is TRUE (1).
- pClearCurrentStats
- TRUE(1)/FALSE(0) flag. If TRUE(1), any EXTENTSIZE, SELECTIVITY, BLOCKCOUNT, etc. will be cleared from the existing table prior to importing the stats. This can be used if you want to completely clear stats that are not specified in the import file instead of leaving them defined in class/table. The default is FALSE (0)
Examples:
- Do $SYSTEM.SQL.Stats.Table.Import("C:\AllStats.xml") // Import TuneTable Statistics for all schemas/tables that were exported with the $SYSTEM.SQL.Stats.Table.Export() to the AllStats.xml file
classmethod SetExtentSize(schema As %Library.String, tablename As %Library.String, newextentsize As %Library.String) as %Library.Status
Set the EXTENTSIZE of a table to the value of the given extentsize
Parameter:
- schema
- Name of the table's schema. Default is the default schema.
- tablename
- Name of the table the field is in (required).
- newextentsize
- New extent size value for the field (required). The extent size of a table is the number of rows. Must be a positive number.
Returns:
- Status Code
Example:
- Do $SYSTEM.SQL.Stats.Table.SetExtentSize("Sample","Person",135)
- Status Code
classmethod SetFieldSelectivity(schema As %Library.String, tablename As %Library.String, fieldname As %Library.String, selectivity As %Library.String) as %Library.Status
Set the SELECTIVITY of a field and property to the value of the given selectivity
Parameter:Status Code
Parameter:
- schema
- Name of the table's schema. Default is the default schema.
- tablename
- Name of the table the field is in (required).
- fieldname
- Name of the field to set the SELECTIVITY for (required).
- selectivity
- New selectivity value for the field (required). The selectivity of a property specifies the approximate frequency of specific values within the entire distribution of values.
The Selectivity value for a column is generally the percentage of rows within a table that would be returned as a result of query searching for a typical value of the column.
For example, suppose a table contains a Gender column whose value is evenly distributed between "M" and "F". The Selectivity value for the Gender column would be 50%, as a
typical query based on Gender would find 50% of the rows within the table. The Selectivity value for a more unique property, such as TaxID, is typically a small percentage
that varies according to the size of the table.
Examples of values you can specify here are:
- "10%" - Means that typical values for this column will return 10% of the rows in the table
- "1" - Means this field is unique. For any given value, it will return 1 row from the table.
- A pure number will calculate the selectivity as EXTENTSIZE/selectivity. For example, if EXTENTSIZE is 100000 and selectivity is 1000, this will set the selectivity to 1%. - "NUMROWS" - This is the same as specifying "1", it means the field is unique. This is allowed for legacy support of M/SQL tables that have been converted to class definitions.
- NUMROWS/positive_integer - This will calculate the SQL SELECTIVITY as EXTENTSIZE/positive_integer. For example if EXTENTSIZE is 100000 and you specify NUMROWS/5000, this will set the SQL SELECTIVITY to 20, which means for a typical value for the field, 20 rows of the table will be returned. This is allowed for legacy support of M/SQL tables that have been converted to class definitions.
- There is no validation of the value you enter for the SELECTIVITY. If you enter something not recognized as a valid SELECTIVITY, such as the string "nonsense", it will be turned into a value of 0. If the SQL query processor sees a SELECTIVITY of 0, it will attempt to come up with a typical SELECTIVITY value for the field based on how many rows are in the table and whether or not the field is a reference column, is part of the IDKEY field, has a VALUELIST specification, etc.
Returns:
Example:
- Do $SYSTEM.SQL.Stats.Table.SetFieldSelectivity("MedLab","Patient","Home_Phone","2.5%")