OLAP Query Processing in Column Store Data Warehouses
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current OLAP query processing methods face performance bottlenecks due to low hard disk I/O performance and inefficient query processing technologies, particularly in column store data warehouses, where multiple column scans and high memory bandwidth consumption hinder real-time high-performance analytical processing.
Innovation Solution
A multi-dimensional OLAP query processing method based on a star-join model that performs bitmap filtering, group-by operations, and aggregate calculations through one-pass column scans, using address mapping and predicate vectors to optimize join operations and reduce memory usage, allowing direct access to fact table measure attributes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If column data query processing technology is adopted, then I/O efficiency of projection operation is improved, but additional I/O access overhead is increased due to scanning foreign key columns twice
Solution Approach 1:
The patent applies preliminary action by pre-computing and materializing join indexes before the actual query execution. The join index is built in advance using a hash table structure that maps foreign key values to primary key values, eliminating the need to scan foreign key columns multiple times during query processing. This pre-computation resolves the contradiction by sacrificing some preprocessing time to eliminate repeated I/O access during actual queries.
Solution Approach 2:
The patent creates a copy of the join relationship data in the form of a materialized join index stored in memory. Instead of repeatedly accessing and scanning the original foreign key columns in the fact table, the system maintains a copied representation of the join semantics that can be quickly consulted. This copying approach improves I/O efficiency while avoiding the overhead of multiple scans.
2Productivity
If dynamic column/row conversion query processing technology is adopted, then I/O efficiency of projection operation is improved, but memory bandwidth resources are consumed and processing performance is degraded
Solution Approach 1:
The patent pre-computes join results and materializes them in a hash table structure before query execution. By performing the join operation in advance and storing the results in an optimized format, the system avoids the need to dynamically convert between column and row storage formats during query processing. This eliminates the memory bandwidth consumption associated with dynamic conversion while maintaining the I/O efficiency benefits of columnar storage.
3Device complexity
If materialized join method is adopted, then complexity of query processing is reduced and parallel speedup ratio is increased, but larger space is required
Solution Approach 1:
The patent applies local quality by materializing only the specific join relationships that are needed for the given query, rather than pre-computing and storing all possible join results. The hash table-based join index is constructed selectively based on the query's requirements, storing only the relevant foreign key to primary key mappings. This selective materialization reduces the space requirement while still achieving the benefits of reduced query complexity and improved parallel processing.
Data Source
AI summary
A multi-dimensional OLAP query processing method oriented to a column store data warehouse is described. With this method, an OLAP query is divided into a bitmap filtering operation, a group-by operation and an aggregate operation. In the bitmap filtering operation, a predicate is first executed on a dimension table to generate a predicate vector bitmap, and a join operation is converted, through address mapping of a surrogate key, into a direct dimension table tuple access operation; in the group-by operation, a fact table tuple satisfying a filtering condition is pre-generated into a group-by unit according to a group-by attribute in an SQL command and is allocated with an increasing ID; and in the aggregate operation, group-by aggregate calculation is performed according to a group item of a fact table filtering group-by vector through one-pass column scan on a fact table measure attribute.