Vector uses a query optimizer to develop sophisticated query execution strategies. The query optimizer makes use of basic information such as row size, number of rows, primary key fields and indexes defined, and more specific data-related information such as the amount of data duplication in a column.

The data-related information is available for use by the query optimizer only after statistics (see Database Statistics) have been generated for the database. Without knowing exactly what data you have stored in your table the query optimizer can only guess what your data looks like.

Consider the following examples:

In each query, the guess is that few rows can qualify. In the first query, this guess is probably correct because employee numbers are usually unique. In the second query, however, this guess is probably incorrect because a company typically has as many males as females.

Why do restricted assumptions about your query make a performance difference? For a single-table, keyed retrieval where you are specifying the key, there is probably no difference at all. The key is used to retrieve your data. However, in a multi-table query with several restrictions, knowing what your data looks like can help determine the best way to execute your query. The following example shows why:

There are many ways of executing this query. If appropriate keys exist, the probable choice is to execute the query in one of these two ways:

The difference between these two possibilities is the order in which the tables are joined. Which method is preferable? Only if you knew exactly how many employees made $50,000, how many buildings were in California, and how many departments were in each building, can you pick the best strategy.

The best (that is, the fastest) query execution strategy can be determined only by having an idea of what your data looks like—how many rows qualify from the restriction, and how many rows join from table to table.

Query Execution Plans (QEPs) (see Query Execution Plans), generated by the query optimizer each time you perform a query, illustrate how a query is executed. By optimizing your database, you can optimize the QEPs that are generated, thereby making your queries more efficient.

Generating statistics for a database optimizes it, which affects the speed of query processing. More complete and accurate statistics generally result in more efficient query execution strategies, which further result in faster system performance.

The extent of the statistics to be generated for a database can be modified by various options, including restricting the tables and columns that are used.

You can generate database statistics using any of the following methods:

Vector automatically constructs histograms from live data. After a server is started, the histograms are added as required, but left in cache for later reuse.

The histograms are built from sample data maintained in memory by the min-max indexes. This provides accurate histograms with little server overhead.

Histograms are automatically generated only for those columns that do not already have histograms stored in the catalog.

A typical use strategy is to create histograms (with optimizedb or CREATE STATS) for columns whose distribution does not change, and then let Vector generate the new histograms on the other, more dynamic, columns for every server cycle.

Alternatively, if you do not want to use optimizedb or CREATE STATS, you can simply let Vector automatically build histograms on all columns.

This feature is enabled or disabled by the setting on the opf_autostats DBMS Server configuration parameter in config.dat, which is set to VECTOR (automatically generates histograms for Vector tables) by default. In addition, the opf_autostats_rebuild parameter can be set to trigger rebuilding of such histograms. For example, opf_autostats_rebuild=0.1 means that if the table data has been increased or decreased by 10% since the last histograms built by autostats, a new set of histograms will be built automatically. The default value is 0.0, which prevents rebuilding.

If a database has not been optimized, the query optimizer assumes that:

If these assumptions are not valid for your data, you must optimize the database by generating statistics for it.

Optimizing a database generally requires disk space, because system catalog tables are created.

While the optimization process is running, the locking system takes an exclusive lock for a brief period on the user table being optimized. Whenever possible, tables on which statistics are created are not locked while statistics are gathered. This means that updates to the optimized table are not disabled for extended periods. However, it is recommended that you optimize the database during off-hours.

When running optimizedb from the command line, the “-o filename” option can be used to write the statistics to an external file rather than to the system catalogs, to not require any catalog locks. At a later, more convenient time, the statistics can be loaded into the catalog with the “-i filename” option, using the same external file. Optimizedb -o requires read locks and optimizedb -i requires a brief exclusive lock on the user table. For more information on the optimizedb command, see the Command Reference.

Because optimizing a database adds column statistics and histogram information to the system catalogs, you should run the system modification operation on your database after optimizing it.

Running system modification modifies the system tables in the database to optimize catalog access. You should do this on a database periodically to maintain peak performance.

To run system modification, use any of the following methods:

Examples:

Modify the system tables in the empdata database:

Modify only the system tables affected by optimizedb:

When you optimize a database, the following information is collected:

The query optimizer can use this information to calculate the cost of a QEP.

When optimizing a database, you can create several types and levels of statistics by specifying options.

First, you can specify what set of data the statistics are gathered on:

Next, either of the following can be created, based on the selected data set. This division determines how much information about the distribution of data the statistics can hold:

When generating statistics for a database, by default all rows of the selected tables are used in the generation of statistics. These non-sampled statistics represent the most accurate statistics possible, because all data is considered.

When the base table is large, you may want to use sampled statistics. With a sufficient sampling, statistics created are almost identical to statistics created on the full table. The processing for sampled statistics is discussed in greater detail in Sampled Optimizer Statistics.

When optimizing a database, full statistics are generated by default.

For example, generate full statistics for all columns in all tables in the empdata database:

Full statistics carry the most information about data distribution (unless the data is modified significantly after statistics are collected).

The cost of their creation (in terms of system resources used), however, is the highest of all types. For each selected column the table is scanned once, and the column values are retrieved in a sorted order. Depending on the availability of indexes on the selected columns, a sort can be required, increasing the cost even further.

The process of generating such complete and accurate statistics can require some time, but there are several ways to adjust this.

You can shorten the process of creating full statistics by creating them on sampled data.

This example generates full statistics with a sampling of 0.5% rows of the emp table:

This example generates full statistics with a sampling of 1% rows of the emp table:

Minmax statistics are “cheaper” than full statistics to create. In most cases they require only one scan of the entire table. Statistics created have information only about minimum and maximum values for a column. This can be acceptable if the distribution of values in the column is reasonably even. However, if the values of a column are skewed, minmax statistics can mislead the query optimizer and result in poor query plan choices.

This example generates statistics with only minimum and maximum values for the employee table:

In Director:

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.

This command generates full statistics for all key and indexed columns in the employee table of the empdata database:

This command generates minmax statistics on a 1% sampling:

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:

Statistics can be read in from a text file. The input file must conform to a certain format, which is identical to that produced when you direct output to a file when displaying statistics. Display Optimizer Statistics provides more information.

The file can be edited to reflect changes in data distribution as required, before submitting the file for use during the optimization process. However, this can potentially mislead the query optimizer into generating poor query plans. Manually editing statistics must be done only if you have a full understanding of the data and how the statistics are used in Vector.

Details on creating and using text files as input when optimizing a database are provided in Statistics in Text Files.

Collecting statistics is generally a time-consuming process because a large amount of data must be scanned. The techniques described so far—except for Key Column Statistics—collect statistics on all columns of the indicated tables.

It is not necessary, however, to choose all columns in all tables in your database when optimizing. The query optimizer uses statistics on a column only if the column is needed to restrict data, if it is specified in a join, or if it is in a GROUP BY clause (to estimate the number of groups). Therefore, we recommend to limit creation of statistics only to those columns used in a WHERE or GROUP BY clause.

The DBA or table owner usually understands the table structure and content and is able to predict how the various columns are used in queries. Thus, someone familiar with the table can identify columns that are used in the WHERE or GROUP BY clause.

Given these queries:

Candidate columns for optimization are:

emp table:  dept 
dept table:  dname, bldg 
bldg table:  bldg

Based on their use in these sample queries, there is no reason to obtain statistics on employee name, age, salary, or building address. These columns are listed in the target list only, not the WHERE clause of the query.

Columns used in the WHERE clause are often indexed to speed up joins and execution of constraints. If this is the case, specify the Gen Statistics on Keys/Index option to create statistics on key (that is, indexed) columns. However, it is often equally as important to create statistics on non‑indexed columns referenced in WHERE clauses.

When you optimize a database, output is generated to show the statistics.

For example, if the Print Histogram option was enabled when optimizing the database, and you chose to optimize the name and sex columns of the emp table, the following output is typical:

The items in the histogram are as follows:

The number of unique values the column has is calculated. The count listed for each cell is the fraction of all the values falling between the lower and upper boundaries of the cell. Statistics for the sex column show that there are no rows with values less than or equal to ‘E,’ 0.06510% of rows with values equal to ‘F,’ no rows with values in the ‘G’ to ‘L’ range, and 99.93% of the rows with values equal to ‘M.’ The cell count includes those rows whose column values are greater than the lower cell bound but less than or equal to the upper cell bound. All cell counts must add to 1.0, representing 100% of the table rows.

Looking at the cells for the name column, you see that between the lower bound cell 0, “Abbot \037”, and cell 1, “Abbot”, 6.25% of the employee’s names are located:

A restriction such as the following brings back about 6.25% of the rows in the table:

The character cell value \037 at the end of the string is octal for the ASCII character that is one less than the blank. Therefore, cell 0 in the name example represents the value immediately preceding ‘Abbot’ in cell 1. This indicates that the count for cell 1 includes all rows whose name column is exactly ‘Abbot.’

In addition to the count and value, each cell of a histogram also contains a repetition factor (labeled “repf” in the statistics output). This is the average number of rows per unique value for each cell, or the “per-cell” repetition factor. The query optimizer uses these values to compute more accurate estimates of the number of rows that result from a join. This is distinct from the repetition factor for the whole column displayed in the header portion of the statistics output.

A histogram can have up to 15,000 cells. The first cell of a histogram is always an “empty” cell, with count = 0.0. It serves as the lower boundary for all values in the histogram. Thus, all values in the column are greater than the value in the first cell. This first cell is usually not included when discussing number of cells, but it is included when statistics are displayed.

A histogram in which there is a separate cell for each distinct column value is known as an “exact” histogram. If there are more distinct values in the column than cells in the histogram, some sets of contiguous values must be merged into a single cell. Histograms in which some cells represent multiple column values are known as “inexact” histograms.

You can control the number of cells used, even for inexact histograms. You can choose to set the number of inexact cells to the same number you chose for an exact histogram, or to another number that seems appropriate. If your data is unevenly distributed, the data distribution cannot be apparent when merged into an inexact histogram with the default 100 cells. Increasing the number of cells can help.

You can control the number of cells your data is merged into even if you go above the maximum number of histogram cells you requested. You can choose to set the default merging number to the same number you chose for the maximum, or a lesser number, if the default of 100 cells seems inappropriate. If your data is unevenly distributed, the data distribution cannot be apparent when merged into the default 100 cells and controlling the merging factor can help.

To control the maximum histogram cells, use the Max Cells “Exact” Histogram option in Director or VDBA. The maximum value accepted is 14,999. You can control the number of cells that your data is merged into if you go beyond the maximum number of unique values using the Max Cells “Inexact” Histogram option. By default, the number of cells used when merging into an inexact histogram is 100, and the maximum value is 14,999.

Using the CREATE STATISTICS command, the maximum histogram cells can be specified using the WITH MAXCELLS= option.

For example, set the maximum number of unique histogram cells to 200, and if there are more than 200 unique values, merge the histogram into 200 cells. To do this, set both the Max Cells “Exact” Histogram and the Max Cells “Inexact” Histogram options to 200.

Set the maximum number of unique histogram cells to 100, and if there are more than 100 unique values, merge the histogram into 50 cells. To do this, set Max Cells “Exact” Histogram to 100 and Max Cells “Inexact” Histogram to 50.

When using these options, remember that the goal is to accurately reflect the distribution of your data so that there can be an accurate estimate of the resultant number of rows from queries that restrict on these columns. The query optimizer uses linear interpolation techniques to compute row estimates from an inexact histogram and the more cells it has to work with, the more accurate are the resulting estimates. The cost of building a histogram is not dependent on the number of cells it contains and is not a factor when determining how many cells to request.

Because global temporary tables only exist for the duration of a session, Optimize Database cannot be used to gather statistical information about them. Without histograms, the query optimizer has no knowledge about the value distributions of the columns in a global temporary table. Vector maintains a reasonably accurate row count for global temporary tables, and this row count can be used by the query optimizer to compile a query which accesses a global temporary table.

The row counts alone are usually enough to permit the compilation of efficient query plans from queries that reference global temporary tables because they often contain relatively small data volumes. The lack of histograms on global temporary tables, however, can cause poor estimates of the number of rows that result from the application of restriction or join predicates. These poor estimates can in turn cause the generation of inefficient query plans. Inefficient query plans typically occur with large global temporary tables or tables with columns having skewed value distributions, which are not handled well by the default estimation algorithms of the query optimizer.

For Vector global temporary tables, the CREATE STATISTICS statement can be used to generate histograms on any or all columns when run from the same session that uses the temporary table. The statistics are kept in memory for the duration of the existence of the temporary table and are used for the optimization of any query involving the table.

Optimization does not necessarily need to be run whenever data is changed or added to the database. Optimization collects statistics that represent percentages of data in ranges and repetition factors. For instance, the statistics collected on employee gender show that 49% of the employees are female and 51% are male. Unless this percentage shifts dramatically, there is no need to rerun optimization on this column, even if the total number of employees changes.

You must rerun optimization if there are modifications to the database that alter the following:

For example, if you had run complete statistics on the empno column early in your company’s history, your repetition factor is correct because all employees still have unique employee numbers. If you used ranges of employee numbers in any way, as you added new employees your histogram information is less accurate.

If your company originally had 100 employees, 10% of the employees have employee numbers greater than 90. If the company hired an additional 100 employees, 55% of the employees have employee numbers greater than 90, but the original histogram information does not reflect this.

Columns that show this type of “receding end” growth and are used in range queries can periodically need to have optimization run on them (exact match on employee number is not affected, because the information that says all employee numbers are unique is still correct).

Even if the statistics are not up-to-date, the query results are still correct.

If statistics are available on a column referenced in a WHERE clause, the query optimizer uses the information to choose the most efficient QEP. Understanding how this information is used can be helpful in analyzing query performance. For more information, see Query Execution Plans.

Two QEPs showing the effect of optimization are presented here. The first is a QEP before optimizing; the second shows the same query after optimization. The query used is a join, where both the r and s tables use the B-tree storage structure:

Before obtaining statistics, the optimizer chooses a full sort-merge (FSM) join, because it assumes that 10% of each table satisfies the qualification “a > 4000,” as shown in the QEP diagram below:

After obtaining statistics, the optimizer chooses a Key join, because only one row satisfies the qualification “a > 4000,” as shown in the QEP diagram below:

The cost of the key join is significantly less than the cost of the FSM join because the join involves far fewer rows.