Statistics in Text Files
The optimization process can directly read a set of optimizer statistics from an external text file, rapidly updating the statistics for specific database tables. This can be useful when:
• Information is being moved from one database to another (for example, using copydb), and you want to copy the statistics for the tables as well.
• You know the distribution of the data in a table and want to read or input these values directly, instead of letting the optimization process generate them for you.
The actual table data is ignored. This gives you a modeling ability, because the table can actually be empty but there are statistics that indicate the presence of data and its distribution. The query optimizer uses those false statistics to determine a QEP. For more information, see
Query Execution Plans. This gives the DBA the ability to verify correctness of QEPs without having to load data into tables.
The text file read by the optimization process can be created in one of two ways:
• When displaying statistics, you can unload statistics that already exist in the database and use the generated file as input to the optimization process.
• You can create an input file from scratch or by editing a file created when displaying statistics.
Unload Optimizer Statistics to a Text File
You can unload optimizer statistics to a text file. The generated file is in an appropriate format so that it can be used as input to the optimization process. This allows:
• Statistics to be easily moved from one database to another
• A default text file to be created if you are generating your own statistics
Use the statdump -o flag. This command dumps all statistics from database mydb into the file stats.out:
statdump -o stats.out mydb
You can also use Director or VDBA.
Unload Statistics for Selected Tables or Columns
To unload statistics for selected tables or columns, use the statdump -r and -a flags.
This command unloads statistics for only the arel table and col1 of the brel table:
statdump -o stats.out mydb -rarel -rbrel -acol1
In VDBA, use the Read Statistics from Server File option with the Specify Tables and Specify Columns check boxes in the Display Statistics dialog. For example, if you want the stats.out file to contain statistics for the entire arel table and the col1 column in the brel table, enable the Specify Tables check box, and choose only the arel and brel tables from the Specify Tables dialog. Enable the Specify Columns check box and choose only the col1 column for brel from the Specify Columns dialog.
Sample Text File Statistics
A sample output file generated using the Direct Output to Server File option of the Display Statistics dialog is shown below. This same text file can be used as input to the optimization process, as described in the next section, Loading Optimizer Statistics from a Text File:
*** statistics for database demodb version: 00850
*** table brel rows:151 pages:3 overflow pages:1
*** column col1 of type integer (length:4, scale:0, nullable)
date:2000_02_24 16:04:37 GMT unique values:132.000
repetition factor:1.144 unique flag:N complete flag:0
domain:0 histogram cells:16 null count:0.0000000 value length:4
cell: 0 count:0.0000000 repf:0.0000000 value: 0
cell: 1 count:0.0728477 repf:1.3750000 value: 23
cell: 2 count:0.0728477 repf:1.8333334 value: 31
cell: 3 count:0.0728477 repf:1.3750000 value: 59
cell: 4 count:0.0728477 repf:1.1000000 value: 138
cell: 5 count:0.0728477 repf:1.0000000 value: 151
cell: 6 count:0.0728477 repf:1.0000000 value: 162
cell: 7 count:0.0728477 repf:1.0000000 value: 173
cell: 8 count:0.0662252 repf:1.2500000 value: 181
cell: 9 count:0.0662252 repf:1.1111112 value: 193
cell: 10 count:0.0662252 repf:1.2500000 value: 202
cell: 11 count:0.0662252 repf:1.0000000 value: 214
cell: 12 count:0.0662252 repf:1.0000000 value: 224
cell: 13 count:0.0662252 repf:1.0000000 value: 236
cell: 14 count:0.0662252 repf:1.2500000 value: 256
cell: 15 count:0.0264901 repf:1.0000000 value: 261
Load Optimizer Statistics from a Text File
To load optimizer statistics from a text file, use the optimizedb -i (input) flag, which will load one or more sets of values from the file.
For example, if the file arelbrel.dat contains statistics for the arel and brel tables, these can be loaded into the database using the command:
optimizedb -i arelbrel.dat mydb
In Director, use the Generate Statistics dialog, Input/Output File page, Statistics Input Filename field.
In VDBA, use the Read Statistics from Server File option in the Optimize Database dialog.
Load Statistics for Selected Tables or Columns
If the input file contains statistics for multiple tables, you can load selected tables or columns by specifying the optimizedb ‑r (read selected tables) and ‑a (read selected columns) flags.
For example, if the file arelbrel.dat contains statistics for the arel and brel tables, only the statistics for arel can be loaded into the database using the command:
optimizedb -i arelbrel.dat mydb -rarel
Only statistics for column col3 of the table are loaded with the following command:
optimizedb -i arelbrel.dat mydb -rarel -acol3
In VDBA, use the Read Statistics from Server File option with the Specify Tables and Specify Columns check boxes in the Optimize Database dialog.
Update Row and Page Counts
The input file for the optimization process contains information about the number of rows, as well as primary and overflow page counts in a table. However, because these values are critical to correct operation, these input values are normally disregarded when creating statistics, leaving the catalog values untouched.
You can force the values in the input file to be used when loading the statistics by specifying the ‑zp flag. For example:
optimizedb -i arelbrel.dat -zp mydb -rarel
In VDBA, enable the Read Row and Page check box in the Optimize Database dialog.
IMPORTANT! This option must be used with extreme care, because it sets critical values.
This option can be useful for certain specialized processing, such as query modeling and performance problem debugging. Bear in mind that the row count value can be modified for the table and its indexes. However, the page count is modified for the table only-the index page count values remains unchanged.
Copy a Table and Associated Statistics
You can copy a table and its associated optimizer statistics from one database to another using copydb and statistics that have been unloaded to a text file. This is usually much faster than copying only the table and rerunning the optimization process to recreate the statistics.
Note: Copying a table and its statistics makes sense only if the statistics are up-to-date.
First, unload the table and its statistics to text files, as described in the steps below:
1. Enter the following command to generate copy.in and copy.out scripts for the arel table:
copydb olddb arel
2. Copy the are1 table out of the olddb database:
sql olddb <copy.out
3. Unload the statistics for the are1 table to a text file named are1.dat:
statdump -o arel.dat olddb -rarel
(Or use Director or VDBA.)
Next, copy the table and statistics back into the new database:
4. Copy the are1 table into the new database:
sql newdb <copy.in
5. Load the statistics for the are1 table from the text file are1.dat in Step 3 of the previous example:
optimizedb -i arel.dat newdb -rarel
(Or use Director or VDBA.)