Query Execution Planning for Parallel Left Inner Joins
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face limitations in processing speed due to hardware constraints, data storage methods, and restricted co-processing options, particularly when handling large volumes of data.
Innovation Solution
A parallelized database system architecture that includes a data input sub-system, data store and process sub-system, query and response sub-system, and administrative/configuration sub-system, utilizing distributed computing resources to optimize query execution and data management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If distributed computing resources are utilized, then processing speed and query execution efficiency are improved, but device complexity increases
Solution Approach 1:
The database system is divided into multiple independent sub-systems (data input sub-system, data store and process sub-system, query and response sub-system, administrative/configuration sub-system) that can operate in parallel across distributed computing resources. Each sub-system handles specific functions independently, enabling scalable processing speed improvement while managing complexity through modular architecture.
2Loss of time
If parallelized processing is implemented, then query execution time is reduced, but ease of operation deteriorates
Solution Approach 1:
The query and response sub-system acts as an intermediary layer between the user and the distributed data store and process sub-systems. This intermediary manages the complexity of parallelized processing by providing a unified interface for query submission and result retrieval, reducing query execution time through parallel processing while maintaining ease of operation through abstracted access methods.
3Reliability
If data is stored in distributed manner, then data integrity and concurrency handling are improved, but loss of information increases due to coordination overhead
Solution Approach 1:
Data is segmented and distributed across multiple independent storage units within the data store and process sub-system. Each segment can be accessed and modified independently, improving data integrity through distributed redundancy and concurrent access capabilities. The segmentation reduces coordination overhead by allowing parallel operations on different data segments without requiring centralized locking or synchronization.
Data Source
AI summary
A query and response sub-system is operable to obtain a query and to generate an execution plan for a left inner join function of the query. The execution plan includes: identify left data of a column of the left table based on a reference column that is common to both tables; forward the rows of left data to memory allocated for a result table; identify right data of a column of the right table based on the reference column; temporarily store rows of the right data in memory allocated for the right table; identify rows of matching data from the rows of right data based on matching data of the reference column; add the rows of matching data to the result table; identify rows of non-matching data from the rows of right data based on data of the reference column; and add NULL to the rows of non-matching data to the result table.