Database Table Reorganization Using Sparse Index Bitmaps
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems lack the ability to physically reorganize tables according to multiple sparse indexes, leading to suboptimal performance due to high randomness of I/O operations, as rows selected by different indexes are spread across multiple disk blocks, resulting in inefficient read operations.
Innovation Solution
A method is introduced to reorganize a database table by constructing a bitmap for each row entry, where bit positions correspond to referencing sparse indexes, and sorting the rows based on the bitmap values, with the most significant bits assigned to the most popular or frequently used indexes, thereby grouping rows associated with each sparse index more closely together.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple sparse indexes are created to tune application queries, then query performance can be improved, but I/O operation randomness increases causing suboptimal performance
Solution Approach 1:
The system performs preliminary reorganization of the table by constructing bitmaps for each row based on which sparse indexes reference the row, then sorting rows according to these bitmaps before queries are executed. This preliminary action groups rows that are frequently accessed together through different sparse indexes into adjacent blocks, reducing I/O randomness during actual query execution.
2Speed
If rows are physically stored according to a single index, then I/O time is minimized for that index, but rows selected by multiple sparse indexes become scattered across disk blocks
Solution Approach 1:
The invention merges the ordering criteria of multiple sparse indexes into a unified physical storage arrangement. By constructing a bitmap that combines references from all sparse indexes and sorting rows based on this combined bitmap, the system creates a unified ordering that satisfies multiple indexes simultaneously, causing rows relevant to different indexes to be stored in adjacent blocks rather than scattered throughout the table.
3Device complexity
If traditional single-index sorting is used, then table organization is simple, but random I/O operations increase when multiple sparse indexes are used
Solution Approach 1:
The system changes the organizing parameter from a single index key to a composite parameter based on bitmaps representing multiple index references. Each row is assigned a bitmap value indicating which sparse indexes reference it, and rows are sorted by these bitmap values. This parameter transformation enables the table to be organized in a way that reduces random I/O operations when querying through multiple sparse indexes.
Data Source
AI summary
A method, system and computer program product are provided for reorganizing a database table according to multiple sparse indexes, wherein the reorganized table has superior I/O performance attributes versus the original table. More specifically, the table is reorganized such that random I/O is minimized by more tightly grouping rows in the table associated with each of the referencing sparse indexes together. This enables more associated rows from a given table relevant to a sparse index to be read into RAM for a given I/O operation.