728x90
Question
How can you use a SQL statement to find out the latest UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM commands that were run on a table?
Answer
INTRODUCTION
You can use a SQL statement to obtain information regarding each of the most recent UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM commands that ran against a table.
The type of information you can obtain from the most recent update statistics is the following:
- The date on which it ran
- The mode used (high or medium)
- The column(s) it ran against
- The resolution used
- The confidence level used
STEPS
You can run this SELECT statement from any application:
- SELECT UNIQUE tabname,colname,constructed,mode,
resolution,confidence
FROM systable s t, syscolumns c, sysdistrib d
WHERE d.tabid = c.tabi d AND d.colno = c.colno A ND
d.tabid = t.tabid AND
t.tabname=' tablename '
ORDER BY 1,2;
- tablename
- The name of the table you want the distribution information for.
The SELECT statement will output columns. The following table indicates their meaning:
Column | Description |
tabname | The name of the table |
colname | The name of the particular column in the table that the distribution had ran. |
constructed | The date when the UPDATE STATISTICS statement had ran. (The date of construction of the data distribution.) |
mode | What mode the UPDATE STATISTICS used. H = HIGH M = MEDIUM There will never be an L because UPDATE STATISTICS LOW does not construct a data distribution. |
resolution | The resolution used by the UPDATE STATISTICS statement |
confidence | The confidence level used by the UPDATE STATISTICS statement |
- Note: The confidence level for distributions run in high mode is irrelevant so it should always be set to zero.
Example:
This example shows that UPDATE STATISTICS was last run on 8 July 2003. The column named code of the table named state had its distribution updated in High mode. It used a resolution of .5. The mode was high so there is no confidence level.
The column named sname in the same table had its distribution built in medium mode. It used a resolution of 2.5 and a confidence level of approximately 0.95.
- tabname state
colname code
constructed 07/08/2003
mode H
resolution 0.5
confidence 0.00
tabname state
colname sname
constructed 07/08/2003
mode M
resolution 2.500000000000
confidence 0.94999999
You can also find this information from the UNIX or Windows command line using the dbschema utility (see Related Information section).
COMMON PROBLEMS
Symptom: You run the SELECT previously mentioned and you see this message
- No rows found is returned.
Resolution: It means the table does not have data distribution. You can run the UPDATE STATISTICS command for the table to generate the data distribution.
728x90