Set this flag { 0 | 1 } in the following location of the 'Storage' section of the class: {BiasQueriesAsOutlier} > {BiasQueriesAsOutlier} If this flag is set to zero (0), the query optimizer assumes that queries will not normally select on a outlier value. If this flag is set to one (1), the query optimizer will assume that queries selecting on a outlier are not rare.

The approximate numbers of map blocks used by each SQL map NOTE: The size of all SQL map blocks is 2048 bytes (2K bytes).

2nd piece of the IDKey A unique ID counter which comprises the data row's IDKey

$LISTBUILD of the Properties that will be the data node of the {IndexName} The extra data is stored as part of the index to avoid reading from the Data Map

Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() The Number of Rows in the Table Based on TuneTable Data or set by the user

$LISTBUILD of the Properties that will make up the Subscripts of the {IndexName} Every Index will have ID as the final subscript

Set by joinIndices() Method $LISTBUILD of the Properties/Fields that comprise the Join Conditions This $LIST is used as a comparison against the the {IndexFields} $LIST in order to determine the viability of the specified {IndexName} and represented by the {JoinIndexFlag} field

Set by joinIndices() method to one of the following values: Flag has 5 values: 4 = Exact Match: An index exists where its fields match all fields from the JOIN conditions 3 = Leading Match: An index exists where its leading fields match all fields from the JOIN conditions, but there are additional fields in the index 2 = Contains Match: An index exists where its fields contains all fields from the JOIN conditions, but not the leading fields 1 = Partial Match: An index exists where its fields contains some of the fields from the JOIN conditions, but not the leading field 0 = No Match: No index exists to support the fields from the JOIN conditions

Set by the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }

Set by the TableScan class method Name of the Module that is doing the Table Scan

1st piece of the IDKey The value for this property indicates the SQL Statement Analysis Method producinc the result row: IndexUsage() => 'IU' TableScans() => 'TS' TempIndices() => 'TI' JoinIndices() => 'JI' OutlierIndices() => 'OI'

Set by outlierIndices() Method $LISTBUILD of the Properties/Fields that comprise the Outlier Conditions This $LIST is used as a comparison against the the {IndexFields} $LIST in order to determine the viability of the specified {IndexName} and represented by the {OutlierIndexFlag} field

Set by outlierIndices() Method Flag has 5 values: 4 = Exact Match: An index exists where its fields match all fields from the Outlier conditions 3 = Leading Match: An index exists where its leading fields match all fields from the Outlier conditions, but there are additional fields in the index 2 = Contains Match: An index exists where its fields contains all fields from the Outlier conditions, but not the leading fields 1 = Partial Match: An index exists where its fields contains some of the fields from the Outlier conditions, but not the leading field 0 = No Match: No index exists to support the fields from the Outlier conditions

Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): {OutlierSelectivity}:{OutlierValue} > {OutlierSelectivity}:{OutlierValue} This is a percentage for a single property value that appears much more frequently than the other data values within the whole set of rows. Normal selectivity is still the percentage of each non-outlier data value within the whole set of rows. For example, if the outlier selectivity is 80% and the regular selectivity is 1%, then in addition to the outlier value, you can expect to find about 20 ((10-.80)/.01) additional non-outlier values. NOTE: This value represented the percentage of outliers, but without the percent sign. Hence, if the outlier value is 75%, then it should be represented at .75

Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): {OutlierSelectivity}:{OutlierValue} > {OutlierSelectivity}:{OutlierValue} This is the value that should be used to compare against for conditions. If this Outlier Value is (represented in this context as the empty string "") and the conditions is IS NULL, then the Query Optimizer will use the {OutlierSelectivity} when constructing the Query Plan.

Set in the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() This is a Pointer Reference to the %SYS.PTools.UtilSQLStatements class which contains the SQL Statements found within InterSystems IRIS

The schema of the table being queried in the SQL Statement

The table being queried in the SQL Statement

Used by both IndexUsage() and TempIndices() class methods Number of times the index was used in the sql.

Method: clearSQLAnalysisDB Replaces: ClearResults (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Delete all of the data stored in the '%SYS.PTools.UtilSQLAnalysisDB' class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.UtilSQLAnalysisDB).clearSQLAnalysisDB(...) SQL: CALL %SYS_PTools.UtilSQLAnalysisDB_clearSQLAnalysisDB(...) SELECT %SYS_PTools.UtilSQLAnalysisDB_clearSQLAnalysisDB(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Delete all of the data stored in the '%SYS.PTools.UtilSQLAnalysisDB' class in the current namespace: set status=##class(%SYS.PTools.UtilSQLAnalysisDB).clearSQLAnalysisDB() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys") Parameters: ns - The namespace in which to clear SQL Index/Analysis Statstics [DEFAULT: Current Namespace] clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT: 0] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return the number of Stats rows deleted from the following class: %SYS.PTools.UtilSQLAnalysisDB [DEFAULT: 0] ptInfo - A Pass By Reference information array that returns detailed information to the user in the following format: ptInfo(category,variable)=value OR $LB(val1,...,valn) Where category = { "cnt" | "curr" | "term" | ... } variable = A var corresponding to the given 'category' Example: ptInfo("cnt","clearSQLAnalysisDB")=The number of rows deleted via this method RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a %Status code of either $$$OK or $$$ERROR() 1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: %SYS.PTools.UtilSQLAnalysisDB

Provide the current version for the UtilSQLAnalysisDB class/section of the Performance Tools (PTools) Application

Query/View: UtilSQLAnalysisDBView Replaces: SQLUtilStmtResultsView (%SYS.PTools.SQLUtilResults) [DEPRECATED] Status: New Query/View to replicate Backward-Compatible Query/View it 'Replaces' Purpose: Class View which returns all of the SQL Statements information stored in the '%SYS.PTools.UtilSQLAnalysisDB' class/table