Key Column Statistics
Key column statistics create full or minmax statistics on key or indexed columns only.
These statistics are generated by specifying the -zk flag in the optimizedb command.
The effect of this command is the same as running optimizedb and specifying key and index columns for a table with the -a flag. This method saves the user some work because optimizedb determines from catalogs which columns are keys and indexed.
Note: Key column statistics cannot be created using the CREATE STATISTICS statement.
Examples: Create Statistics on Key or Indexed Columns Only
This command generates full statistics for all key and indexed columns in the employee table of the empdata database:
optimizedb -zk empdata -remployee
In Director:
1. Open the Generate Statistic dialog.
2. Select Generate Statistics on Keys/Indexes Columns Only (the default)
3. Select Specify Tables, and then click the Tables button.
4. Select the employee table and then click OK.
5. De-select Generate Statistics on Subset of Data, and then click OK.
This command generates minmax statistics on a 1% sampling:
optimizedb -zx -zk -zs1.0 empdata -remployee
In Director:
1. Open the Generate Statistic dialog.
2. Select Generate Statistics on Keys/Indexes Columns Only (the default)
3. Select Specify Tables, and then click the Tables button.
4. Select the employee table and then click OK.
5. Select Generate Statistics on Subset of Data, set the Precision Level at 1.
6. Click Options under Select a page.
7. Select Determine the Min/Max Values for each Column, and then click OK.
All key and indexed columns in the table are processed regardless of any column designations. For example, assume that dno is a data column and kno is a key column in the employee table. The following example for generating full statistics is the same as the first example in this section, except that in addition to key and indexed columns, statistics are generated also for the dno column:
optimizedb -zk empdata -remployee -adno -akno
In Director:
1. Open the Generate Statistic dialog.
2. Select Generate Statistics on Keys/Indexes Columns Only
3. Select Specify Tables, and then click the Tables button.
4. Select the employee table and click OK.
5. Select Specify Columns, and then click the Columns button.
6. Select the dno column and kno column, and then click OK.
You do not have to select the kno column because it is a key column and we selected Generate Statistics on Keys/Index Columns Only
7. De-select Generate Statistics on Subset of Data, and then click OK.