Sparse Data Index Table for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional indexing methods are inefficient for sparse data sets, leading to significant space overhead and query cost without substantial reduction, especially when dealing with large datasets or sparse data tables where many entries contain 'NULL' values, making full table scans necessary.
Innovation Solution
Implementing a sparse data index table with a bitmap index extended to include 'don't care' values, using ternary and quaternary matching to efficiently scan and match rows, reducing memory usage and query costs by optimizing index table structure and operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional indexing methods are used on sparse data sets, then query performance may be improved, but space overhead increases significantly
Solution Approach 1:
The patent extracts only the non-NULL values from sparse data columns to create the index, rather than indexing all values including NULLs. This selective extraction reduces the index size while maintaining query effectiveness for non-NULL values.
Solution Approach 2:
The index is segmented into separate structures for NULL values and non-NULL values. The patent uses a hybrid index structure that divides the indexing task into two parts: handling NULL values separately and indexing only non-NULL values in a compressed format.
2Productivity
If traditional indexing is applied to sparse data, then some query efficiency is gained, but query cost does not reduce substantially
Solution Approach 1:
The patent applies partial indexing by only indexing non-NULL values rather than all values. This partial action is sufficient to improve query performance for non-NULL lookups while avoiding the overhead of indexing NULL values that would not contribute to query efficiency.
Solution Approach 2:
The patent changes the indexing parameter from indexing all values to indexing only non-NULL values. It also employs variable-length encoding for non-NULL values, adapting the storage format to the actual data distribution in sparse columns.
3Reliability
If full table scans are performed on sparse data tables, then all rows are examined, but time consumption increases significantly
Solution Approach 1:
The patent performs preliminary indexing of non-NULL values before query execution. This preliminary action creates a lookup structure that can be quickly searched, avoiding the need to scan the entire table during query processing.
Solution Approach 2:
The patent introduces an intermediary index structure that mediates between the query and the full table. This hybrid index acts as a mediator that quickly identifies relevant rows, reducing the need for full table scans while maintaining completeness of examination.
Data Source
AI summary
A set of queries on a collection of data records is received. A set of subconditions required to support the set of queries is determined. An index table is maintained, wherein an entry in the index table indicates a subcondition status associated with a data record in the collection of data records. A specific query is received indicating a specified set of subcondition statuses. The specified set of subcondition statuses is matched against the index table. A result set is returned corresponding to matched rows in the index table, wherein the matched rows match the specified set of subcondition statuses.