Plan Tree History Recording for Sporadic Query Caching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing multiple query optimization methods are limited to situations where multiple queries are issued simultaneously, leading to increased waiting times and restricted applicability in online analytical processing (OLAP) due to the assumption of a known set of queries to be optimized.
Innovation Solution
A data processing device that records and reuses the execution results of plan trees by assigning unique numbers to nodes and tracking their appearance frequency, allowing for the caching and reuse of common partial plan trees across queries issued at different times.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple query optimization is applied to batch queries, then query execution time is reduced, but the method cannot be applied to queries issued at different times
Solution Approach 1:
The system performs preliminary actions by recording and storing execution results of plan trees in advance. When a query is issued, the system checks the history recording unit to see if a previously executed plan tree matches the current query's plan tree. If a match is found, the stored execution result is reused, eliminating the need to re-execute the plan tree. This preliminary recording and storage of execution results enables the system to handle queries issued at different times efficiently, resolving the contradiction between execution speed and adaptability to different timing scenarios.
2Ease of operation
If queries are executed sequentially without sharing, then each query is processed independently, but execution time increases when similar queries are issued
Solution Approach 1:
The system creates and stores copies of execution results from plan trees in the history recording unit. When a similar query is issued, instead of executing the plan tree again, the system retrieves the stored copy of the execution result from history. This copying mechanism maintains the independence of query processing (each query can be handled separately) while dramatically reducing execution time by reusing previously computed results, thus resolving the contradiction between independent processing and time efficiency.
3Productivity
If the system waits for subsequent queries to arrive before optimizing, then all queries can be optimized together, but waiting time increases
Solution Approach 1:
The system implements self-service by automatically recording execution results of plan trees in the history recording unit as they are executed. Each plan tree execution result is stored with its specific information (such as node identifiers and structure characteristics). When a new query arrives, the system autonomously checks whether its plan tree matches any previously recorded plan trees and reuses the corresponding execution result if a match is found. This self-service mechanism eliminates the need to wait for multiple queries to accumulate before optimization can occur, resolving the contradiction between batch optimization efficiency and waiting time.
Data Source
AI summary
A data processing device includes: a recording unit (21) that records, as a history of a plan tree of each issued query, an execution result of the plan tree, a history of specific information for specifying each node of the plan tree, and an appearance frequency at which the plan tree has appeared in the past; and a cache reuse unit (25) that obtains specific information corresponding to an execution plan being executed, to refer to the history of the plan tree by using the obtained specific information as a key, and reuses the execution result of the plan tree of the obtained specific information when the obtained specific information exists.