The query optimizer includes a heuristic that searches for common tables in different fragments of a query. For example, in the query SELECT ... FROM a, b, c WHERE a.x = b.y AND ... UNION SELECT ... FROM a, b, d WHERE a.x = b.y and ..., the "a, b" join is shared between the union’ed selects. In such cases query plans are built that materialize the common portions once and cache them in memory to be reused. This “reuse” heuristic can greatly improve the performance of some queries.

In rare cases, the cached fragments may overflow the available memory. The heuristic attempts to avoid overflow by using estimates of the size of the cached results, but you can also turn off the feature by using the SET NOREUSE statement. SET NOREUSE stays in effect until the end of the session or until a SET REUSE statement is issued to reinstate the default behavior.

If you want the NOREUSE behavior to be the default for the server, you can set the parameter opf_reuse to OFF in config.dat. You must then use the SET REUSE statement to enable the heuristic for a given session.

The parameter opf_maxreuse in config.dat specifies the maximum estimated size of a reuse fragment (as a nonnegative number of bytes) that can be cached. If the estimate for a potential reuse fragment of a query exceeds that amount, the results of the reuse fragment are not cached and are materialized as many times as referenced in a given query. The default value for opf_maxreuse is 100 MB. If it is explicitly set to 0, fragment size is not checked and the reuse feature is always attempted.

The main points to check when evaluating a QEP are as follows:

Optimizer statistics can be reviewed and processed by several utilities. You can:

You can view statistics that have been collected. Use the following methods:

The usual output is based on statistics generated by the optimization process, as described inDatabase Statistics.

By default, optimizer statistics are shown for all tables and columns in the current database, but you can view statistics for specific table columns.

The following command lists statistics for the empno column of the emp table:

In Director or VDBA, use the Specify Tables and Specify Columns check boxes in the Display Statistics dialog.

To delete statistics use any of these methods:

You can specify the precision with which floating point numbers are displayed in the statistics by enabling the Set Precision Level to check box and entering a value in the corresponding edit control to determine the number of decimal digits in the text format of the floating-point numbers.

For example, assume a table, t_float, is defined with a column named c_float of type float, and that the following statements are used to insert values (all of which are approximately 1.0):

You can create statistics for this table using the optimization procedure described in Database Statistics.

With its default floating point precision, the standard output is show seven places after the decimal point. For greater precision, you can enable the Set Precision Level check box and enter a larger value.

For example, specifying a precision level of 14 generates output like the following, in which there is sufficient precision to maintain a visible difference in the values:

This can be useful when statistics are output to a text file or input from a text file. For more information, see Statistics in Text Files. When reading statistics from a text file, the optimization process assumes that all cell values are in ascending order. You can use the Set Precision Level option to preserve sufficient precision for floating point numbers.

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:

The actual table data is ignored. This gives you a modeling ability, because the table can 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:

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:

Use the statdump -o flag. This command dumps all statistics from database mydb into the file stats.out:

You can also use Director or VDBA.

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:

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.

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, 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:

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.

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:

Only statistics for column col3 of the table are loaded with the following command:

In VDBA, use the Read Statistics from Server File option with the Specify Tables and Specify Columns check boxes in the Optimize Database dialog.

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:

In VDBA, enable the Read Row and Page check box in the Optimize Database dialog.

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.

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.

First, unload the table and its statistics to text files, as described in the steps below:

Next, copy the table and statistics back into the new database:

The optimization process allows you to create sampled optimizer statistics on database tables. For large tables, sampled statistics are usually much faster to generate than full statistics, and if the percentage of rows to be sampled is chosen appropriately, they can be nearly as accurate.

Sampled statistics are generated by only looking at a certain percentage of the rows in a table. The percentage must be chosen so that all significant variations in the data distribution are likely to be sampled.

The sampled rows are selected by randomly generating values for the tuple identifier (tid), so tid values are required to support this functionality.

To specify sampled statistics and the percentage of rows to be sampled, use the optimizedb -zs flag:

where n is a real number indicating the percentage of rows to be sampled, from 1 to 100.

The following example optimizes the table bigtable, sampling 3% of the rows:

In Director, on the Generate Statistics dialog, select Generate Statistics on Subset of Data, and then specify a Precision Level.

In VDBA, in the Optimize Database dialog, select Statistics on Subset of Data, and then specify a Percentage.

When sampling, the query optimizer chooses rows randomly from the base table and inserts them into a temporary table. Rows are selected in such a way that uniqueness of column values are preserved (conceptually, when sampling, a row can be selected not more than once). Full statistics, or minmax if requested, are created on the temporary table and stored in the catalogs as statistics for the base table. The temporary table is deleted. Be sure you have enough free disk space to store this temporary table, and that create_table permission has been granted to the user running the optimization process.

You have control over the percentage of rows that are sampled. It is worthwhile to experiment with this percentage. When the percentages are too small for a good sampling, the statistics created change as percentage figures change. As you increase the percentage, eventually a plateau is reached where the statistics begin coming out almost the same. The smallest percentage that provides stable statistics is the most efficient number.

The optimizer usually calculates costs from statistics on individual columns. However, it is possible for Vector to create and use histograms created from the concatenation of several columns. Such histograms are called composite histograms.

Composite histograms are useful in ad hoc query applications in which there are WHERE clause restrictions on varying combinations of columns. Such applications can have a variety of secondary indexes constructed on different permutations of the same columns with the goal of allowing the query optimizer to pick an index tailored to the specific combination of restrictions used in any one query.

For example, consider a table X with columns A, B, C, D, E, etc. and secondary indexes defined on (A, B, C), (B, C, D), (B, A, E). Consider a query with a WHERE clause such as “A = 25 and B = 30 and E = 99”. With histograms on the individual columns, the query optimizer finds it difficult to differentiate the cost of solving the query using the (A, B, C) index and the (B, A, E) index. This is because of the technique used to determine the combined effect of several restrictions on the same table. However, with composite histograms defined on each index, the optimizer combines the three restrictions into a single restriction on the concatenated key values, and the (B, A, E) index clearly produces the best-looking query plan.

Composite histograms can be created on any collection of two or more columns. The columns can be, but need not be, storage structure or secondary index key columns. Any one column can participate in multiple composite histograms.

When optimizing a query, the optimizer looks for the longest (that is, the most columns) composite histogram whose leading columns are equality predicates in the query. All the columns in that histogram are then marked “statistics found,” and the process repeats with remaining columns in the query.

Finally, all remaining columns not already covered by a composite histogram are estimated using standard single-column statistics.