Non-clustered Column Store Update Mechanism
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face inefficiencies in updating and deleting data in column stores due to the spread of row data across multiple column arrays, lack of row organization for efficient key-based retrieval, and inefficient compression, which complicates operations like scans and updates.
Innovation Solution
Implementing a system that maintains data in a plurality of structures including a column store, a delete bitmap, and a delta store, where the delta store tracks propagation states and the delete buffer indicates logically deleted rows, allowing for efficient updates and deletions by segregating data based on temperature and using key values for access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is stored in a traditional column store with row data spread across multiple column arrays, then analytical query processing is efficient, but update and delete operations become inefficient
Solution Approach 1:
The patent segments the data storage into three distinct structures: a column store for analytical queries, a delete bitmap for tracking deleted rows, and a delta store for pending updates. This segmentation allows each structure to serve its specific function efficiently, resolving the contradiction between update efficiency and analytical query performance by preventing update operations from interfering with the column store's optimized structure
Solution Approach 2:
The patent introduces intermediary structures (delta store and delete bitmap) that mediate between update operations and the column store. These intermediaries buffer changes before applying them to the column store, allowing update operations to proceed efficiently without immediately disrupting the column store's organization, thus maintaining both update efficiency and analytical query performance
2Quantity of substance
If row data is spread across multiple column arrays for compression efficiency, then storage efficiency is improved, but key-based retrieval becomes inefficient
Solution Approach 1:
The delta store acts as an intermediary for key-based retrieval operations. When a key-based lookup is needed, the system first checks the delta store (which maintains row-oriented organization suitable for key access) before accessing the column store. This intermediary approach enables efficient key-based retrieval without compromising the compression efficiency of the column store
Solution Approach 2:
The patent applies different organizational qualities to different data structures: the column store uses columnar organization optimized for compression and analytical queries, while the delta store uses row-oriented organization optimized for key-based retrieval. This local quality differentiation allows each structure to excel at its specific operation type without compromising the other
3Productivity
If a simple column store structure is used for analytical queries, then query performance is improved, but handling both transactional and analytical workloads becomes difficult
Solution Approach 1:
The patent creates a multi-functional data management system where the same infrastructure handles both transactional (OLTP) and analytical (OLAP) workloads. The column store handles analytical queries efficiently, while the delta store and delete bitmap handle transactional updates and deletions. This universal system resolves the contradiction by allowing each component to specialize while working together to support diverse workload types
4Reliability
If full scans are performed for update operations in a column store, then data consistency is maintained, but processing time increases significantly
Solution Approach 1:
The system performs preliminary actions by maintaining a delta store that captures update operations before they are applied to the column store. This preliminary buffering allows the system to track changes efficiently without immediately scanning the entire column store, thus maintaining data consistency while significantly reducing update processing time
Solution Approach 2:
The delta store and delete bitmap serve as intermediaries that track data consistency without requiring full scans of the column store. These intermediaries maintain a record of pending updates and deletions, allowing the system to ensure consistency through targeted checks rather than exhaustive scans, thereby reducing processing time while maintaining reliability
Data Source
AI summary
The processing of transaction oriented data tends to be row-oriented, while the processing of analytical operations tends to be column-oriented. Various systems, sometimes referred to as operational data warehouses, may comprise mechanisms adapted for use in scenarios where both transactional data processing and analytical queries are to be performed efficiently. The operational data warehouse (ODW) may perform and update data efficiently by maintaining a table in structures comprising a column store, a delta store, a delete bitmap, and a delete buffer. In this environment, key values may be associated for each row such that the ODW may more efficiently seek rows. Further, rows may also be excluded from a column store based at least in part on a filter criterion. The filtering criterion may be used to filter out rows based on a created predicate set by a user or the system.