In-Memory Expression Units for Analytic Query Acceleration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database server performance is significantly impacted by computationally expensive query expressions that are repeatedly evaluated over large datasets, as existing solutions like materialized views do not capture intermediary expression results or internal computations residing solely in memory during query execution.
Innovation Solution
Implementing in-memory units such as in-memory columnar units (IMCUs) and in-memory expression units (IMEUs) to cache pre-computed results of frequently used expressions and internal computations, allowing subsequent queries to access cached results instead of re-evaluating them, thereby reducing resource consumption and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If materialized views are used to capture final query results, then query execution time is reduced for final results, but intermediary expression results and internal computations are not captured, leading to repeated computations of hash values and other intermediate results
Solution Approach 1:
The patent segments the query execution process into multiple components: final query results, intermediary expression results, and internal computations. Each segment is captured and stored separately in in-memory columnar units, allowing selective reuse of intermediate results without requiring storage of complete query execution states. This segmentation enables the system to capture hash values and other intermediate computations that materialized views alone cannot preserve.
Solution Approach 2:
The system performs preliminary computation and storage of intermediary expression results during query execution. Hash values and intermediate computation results are computed and stored in in-memory columnar units during the initial query processing, making them available for reuse in subsequent queries without requiring re-computation. This preliminary action prevents the loss of valuable intermediate results.
2Adaptability or versatility
If computationally expensive expressions are repeatedly evaluated over large datasets, then query flexibility and correctness are maintained, but database server performance is significantly impacted due to resource consumption
Solution Approach 1:
The patent applies local quality by storing intermediary expression results in in-memory columnar units with specific characteristics optimized for their reuse: compression schemes tailored to intermediate data patterns, retention policies based on query frequency analysis, and memory allocation prioritized for frequently accessed intermediate results. This local optimization maintains query flexibility while improving server performance by reducing redundant computations of expensive expressions.
3Use of energy by moving object
If in-memory units are used to cache pre-computed results, then resource consumption is reduced and query performance is improved, but additional memory storage requirements and system complexity are introduced
Solution Approach 1:
The in-memory columnar units serve multiple functions: they store both final query results and intermediary expression results, act as both cache and computation engine, and support both exact and approximate queries. This multi-functionality reduces the need for separate systems for each function, thereby managing complexity while providing comprehensive resource optimization across different query types and execution scenarios.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Techniques are described for materializing pre-computed results of expressions. In an embodiment, a set of one or more column units are stored in volatile or non-volatile memory. Each column unit corresponds to a column that belongs to an on-disk table within a database managed by a database server instance and includes data items from the corresponding column. A set of one or more virtual column units, and data that associates the set of one or more column units with the set of one or more virtual column units, are also stored in memory. The set of one or more virtual column units includes a particular virtual column unit storing results that are derived by evaluating an expression on at least one column of the on-disk table.