Dimensional Zonemap for Relational Database I/O Pruning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database management systems face performance bottlenecks during star queries due to wasteful disk scanning of fact tables, as existing clustering methods only focus on columns of the fact table, neglecting the impact of filter predicates on dimension tables.
Innovation Solution
The approach involves clustering a fact table based on values from one or more columns of dimension tables, storing rows in sorted order, and generating a dimensional zonemap to facilitate I/O pruning by dividing the fact table into zones, allowing the database server to skip irrelevant disk blocks during scans based on filter predicates on dimension columns.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If fact tables are clustered based only on fact table columns, then storage organization is simplified, but query performance deteriorates due to wasteful disk scanning when filter predicates are applied to dimension tables
Solution Approach 1:
The patent extends the clustering dimension from fact table columns to include dimension table columns by creating a composite clustering key that incorporates both fact table primary key and dimension table clustering columns. This multi-dimensional clustering approach allows the system to maintain simple storage organization while enabling efficient query performance when filter predicates are applied to dimension tables, as related fact table rows are physically stored near each other based on dimension column values.
2Reliability
If all disk blocks of fact table are scanned during star queries, then data retrieval completeness is ensured, but I/O efficiency deteriorates due to reading irrelevant blocks
Solution Approach 1:
The patent performs preliminary clustering of fact table rows based on dimension table column values before query execution. By pre-organizing rows so that those with matching dimension column values are stored contiguously, the system enables efficient I/O pruning during queries - the query optimizer can skip entire disk blocks that cannot contain matching rows, ensuring data retrieval completeness while dramatically reducing I/O energy consumption.
3Adaptability or versatility
If fact table rows are stored in arbitrary order, then data insertion flexibility is maintained, but join performance deteriorates due to increased rows involved in star joins
Solution Approach 1:
The patent segments the fact table storage by creating clustered indexes that divide rows into groups based on dimension table column values. Each segment contains rows with matching dimension values stored contiguously, which maintains insertion flexibility through index structures while dramatically improving star join performance by reducing the number of rows that need to be processed during joins.
Data Source
AI summary
Techniques are provided for generating a “dimensional zonemap” that allows a database server to avoid scanning disk blocks of a fact table based on filter predicates in a query that qualify one or more dimension tables. The zonemap divides the fact table into sets of contiguous disk blocks referred to as “zones”. For each zone, a minimum value and a maximum value for each of one or more “zoned” columns of the dimension tables is determined and maintained in the zonemap. For a query that contains a filter predicate on a zoned column, the predicate value can be compared to the minimum value and maximum value maintained for a zone for that zoned column to determine whether a scan of the disk blocks of the zone can be skipped.