14. Using the Query Optimizer : Database Statistics : Statistics and Global Temporary Tables
 
Share this page                  
Statistics and Global Temporary Tables
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, in particular 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.