How to Associate “Model” Histograms with Global Temporary Tables
Associating “model” histograms with global temporary tables can help alleviate the generation of inefficient query plans that can typically occur with large global temporary tables or tables with columns having skewed value distributions.
To associate “model” histograms with global temporary tables, follow these steps:
1. Create a persistent table with the same name as the global temporary table being modeled. The schema qualifier for the table must be either the user ID of the executing user of the application creating and accessing the global temporary table, or the special user ID “_gtt_model”. Its column definitions must include at least those from the global temporary table for which histograms are to be built. The column names and types must exactly match those of the global temporary table.
2. Populate the persistent table with a set of rows, which is representative of a typical instance of the global temporary table.
3. Run optimizedb on those columns of the persistent table for which histograms are desired (typically, the columns contained in WHERE clauses in any referencing queries).
4. After the histograms have been built, the persistent table can be emptied of rows, to release the space it occupies. This must be done with a DELETE FROM xxx statement, to delete the rows but leave the catalog definition (and histograms).
When the query optimizer analyzes WHERE clause predicates with columns from a global temporary table, it looks for the catalog definition of a similarly named persistent table with a schema qualifier matching the ID of the executing user or _gtt_model. If one is found, it looks for histograms on similarly named columns whose type and length exactly match those of the global temporary table columns. If these conditions are satisfied, it uses the model histograms.
Not all faulty query plans involving global temporary tables can be improved this way. The modeling technique depends on the fact that all or most instances of the global temporary table have similar value distributions in the histogrammed columns. If this is not true, a single instance of the table (as with the model persistent table) will not be representative of them all, and can improve the query plans in some executions of the application, but degrade other executions.