Session-Level Demographics for Temporary Table Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Collecting statistics for temporary tables in databases is challenging, hindering the production of optimal plans and query performance.
Innovation Solution
Saving a subset of the demographics of the data source in session-level memory as the demographics of temporary tables, allowing these saved demographics to be used for optimizing the execution of database requests involving temporary tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If statistics are collected for temporary tables, then query optimization is improved, but the complexity of the database system increases
Solution Approach 1:
The system performs preliminary action by collecting and saving demographics of temporary tables during the CREATE TABLE AS SELECT operation. The demographics are captured in advance and stored in session-level memory, so that when the temporary table is subsequently queried, the optimization can immediately use the pre-collected statistics without requiring additional data collection steps.
Solution Approach 2:
The system creates a copy of the demographics data from the source table and stores it in session-level memory associated with the temporary table. This copied demographic information is then used for optimization purposes, allowing the system to maintain optimization capabilities without permanently storing complex statistical data structures.
2Productivity
If demographics are saved in session level memory, then subsequent query execution is optimized, but memory consumption increases
Solution Approach 1:
The system extracts only the essential demographic information from the source table and saves it in session-level memory. By taking out only the necessary statistical summaries (such as column demographics, histograms, and cardinality estimates) rather than storing complete table data, the system minimizes memory consumption while maintaining sufficient information for query optimization.
Solution Approach 2:
The system changes the state of demographic data from full table statistics to summarized demographic parameters. By transforming detailed statistical information into condensed demographic summaries that can be efficiently stored in memory, the system reduces the quantity of data stored while preserving the essential information needed for optimization.
3Reliability
If statistics are collected for temporary tables, then execution plans are optimized, but the time required for data collection increases
Solution Approach 1:
The system performs the time-consuming demographic collection action during the CREATE TABLE AS SELECT operation itself, which is already executing. By capturing demographics in advance as part of the table creation process, the system eliminates the need for separate, additional data collection steps that would otherwise be required before optimization can proceed.
Solution Approach 2:
The system merges the demographic collection operation with the existing CREATE TABLE AS SELECT operation. Instead of performing demographics collection as a separate, time-consuming step, the system combines both operations into a single execution flow, thereby reducing total time while maintaining execution plan quality.
Data Source
AI summary
Statistics for temporary tables are used in optimizing the execution of a database request. A first temporary table is created using data from a data source as the result of performing an operation while executing the request. A subset of the demographics of the data source is saved in a session level memory as the demographics of the first temporary table. The saved demographics are used in optimizing the execution of the portion of the request involving the first temporary table.