Partition Query Processing Using Min/Max Row Positions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern database systems face challenges in efficiently performing data modification operations like DELETE, UPDATE, or MERGE that affect only a small fraction of rows across multiple partitions, leading to significant performance overhead due to the need for scanning and rewriting large amounts of unchanged data.
Innovation Solution
The implementation of min/max decisive row positions stored in a file header allows for determining whether column statistics need to be recomputed without scanning the entire partition, optimizing performance by tracking minimum and maximum values for each column.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional data modification operations (DELETE, UPDATE, MERGE) are performed on partitions, then data can be modified, but scanning and rewriting large amounts of unchanged data causes significant performance overhead
Solution Approach 1:
The patent stores min/max row positions in the partition header before data modification operations. This preliminary action allows the system to quickly determine whether statistics need recomputation by checking if modified rows affect these boundary positions, eliminating the need to scan entire partitions during modification operations.
Solution Approach 2:
The patent extracts only the necessary information (min/max row positions) from the partition header to determine statistical validity, rather than scanning all data. This extraction approach allows the system to make decisions about statistic recomputation based on minimal data inspection, significantly reducing processing time.
2Measurement precision
If column statistics are recomputed after data modification, then accurate metadata statistics are maintained, but this requires scanning the entire partition which reduces performance
Solution Approach 1:
The system performs preliminary action by storing min/max row positions in the partition header before modifications. After data modification, the system checks whether the modified rows affect these pre-stored positions. If they don't, statistics remain valid without recomputation, maintaining accuracy while avoiding unnecessary scanning.
Solution Approach 2:
The partition header structure serves itself by automatically providing the information needed to determine statistic validity. The min/max row positions stored in the header enable the system to self-determine whether recomputation is necessary, eliminating the need for external scanning operations.
3Reliability
If the entire partition is scanned to determine if statistics need recomputation, then accurate determination is achieved, but this creates significant performance overhead for small modifications
Solution Approach 1:
The patent extracts only the critical min/max row position information from the partition header to make reliability determinations. By taking out only this essential information rather than scanning the entire partition, the system achieves reliable statistic validity determination with minimal time investment.
Solution Approach 2:
The system applies partial action by checking only the min/max row positions stored in the header rather than performing complete partition scanning. This partial approach provides sufficient reliability for statistic validity determination without the excessive time cost of full scans, especially for small modifications.
Data Source
AI summary
The subject technology provides modifying data in a partition stores a version identifier and a set of physical row positions in a header, including minimum and maximum value occurrences for each column. When a query to modify data is received, the subject technology determines which rows to modify, checks eligibility for skipping partition scanning, and compares the to-be-modified positions with the stored positions. If no stored positions are modified, the subject technology skips scanning and maintains existing statistics, and creates a new combined partition without recalculating statistics. If modifications are made, the subject technology evaluates eligibility for processing using delta files and a bitset, or performs a copy-on-write process if ineligible.