Database Query Processing for Remote Data Store Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Accessing and processing large objects in remote object stores can be expensive in terms of network bandwidth and processing resources, leading to high latency and potential network overload due to the need for repeated data retrieval and processing.
Innovation Solution
The implementation of external read reduction logic within a database management system's parsing engine, which cooperates with an optimizer to reduce the number of reads from remote data stores by creating a common spool block that combines projections and selections from multiple query blocks, allowing for efficient query execution by rewriting query blocks to reference a spool containing the common data rather than the original external tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is repeatedly retrieved from remote object stores for multiple query blocks, then query processing can be performed, but network bandwidth consumption increases and processing latency increases
Solution Approach 1:
The patent combines multiple query blocks that reference the same external table into a single unified query block. This merging eliminates redundant data retrieval operations by consolidating repeated external table references into one shared reference, thereby reducing network bandwidth consumption while maintaining query processing capability.
Solution Approach 2:
The system performs preliminary analysis during query parsing to identify and consolidate repeated external table references before query execution. By detecting and merging redundant references in advance, the system prepares an optimized query plan that minimizes network I/O operations, reducing both bandwidth consumption and processing latency.
2Reliability
If multiple query blocks reference the same external table, then comprehensive query results can be obtained, but processing overhead increases due to repeated data retrieval
Solution Approach 1:
Multiple query blocks referencing the same external table are merged into a single unified query block. This consolidation ensures that the external table is accessed only once while still satisfying all query requirements, thereby maintaining result completeness while eliminating redundant access operations that cause processing delays.
Solution Approach 2:
The patent creates internal representations or copies of query block structures that reference external tables. By working with these internal copies during optimization and merging operations, the system can restructure queries to eliminate redundant external references without losing the semantic meaning or completeness of the original query requirements.
3Ease of operation
If the parsing engine processes each query block independently, then query structure is preserved, but redundant external table reads occur
Solution Approach 1:
The parsing engine is enhanced with multi-functionality: it既可以process individual query blocks independently to preserve structure,又可以perform cross-block analysis to identify and merge redundant external table references. This universal capability allows the system to maintain query structure integrity while simultaneously optimizing network I/O efficiency through intelligent consolidation.
Data Source
AI summary
In some examples, a database system identifies a plurality of query portions in a database query that contain references to a first external table, the first external table being based on data from a remote data store coupled to the database system over a network. The database system creates a common spool portion that includes projections and selections of the plurality of query portions, and rewrites the plurality of query portions into rewritten query portions that refer to a spool containing an output of the common spool portion. For execution of the database query, the database system determines, as part of optimizer planning, whether to use the plurality of query portions or the common spool portion and the rewritten query portions.