Inline LOB Storage for Database Query Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face performance issues when accessing large objects (LOBs) due to the overhead of frequent LOB column references in queries, which leads to decreased query performance and increased storage consumption.
Innovation Solution
A computer-implemented method that identifies frequently accessed LOB data, caches its locations, stabilizes them in an index, and saves frequently accessed portions of LOB data inline with a base row, reducing the need for additional I/O operations and improving query efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If LOB data is stored in separate LOB storage objects, then storage capacity and data manipulation flexibility are improved, but query performance deteriorates due to frequent additional I/O operations
Solution Approach 1:
The patent segments LOB data into two storage locations: a base row containing frequently accessed portions (hot data) and a LOB storage object containing the complete LOB data. This segmentation allows the system to serve frequent access requests from the base row (avoiding I/O operations) while maintaining the ability to access complete LOB data from the storage object when needed, thus resolving the contradiction between query performance and data manipulation flexibility.
2Reliability
If LOB data is accessed multiple times in a query, then data retrieval completeness is improved, but query performance decreases due to repeated I/O operations
Solution Approach 1:
The patent applies preliminary action by pre-loading frequently accessed LOB portions into the base row during data insertion or update operations. When a query later references these LOB portions, the system can retrieve them directly from the base row without performing I/O operations, thereby maintaining data retrieval completeness while significantly improving query performance.
3Productivity
If LOB data is stored inline with base rows, then query performance is improved by eliminating additional I/O, but storage consumption increases and row compression capability is reduced
Solution Approach 1:
The patent applies local quality by differentiating the storage treatment for different portions of LOB data based on their access frequency. Frequently accessed portions are stored inline with base rows (optimized for query performance), while less frequently accessed portions remain in the LOB storage object (optimized for storage efficiency). This localized differentiation resolves the contradiction between query performance and storage consumption.
Data Source
AI summary
Computer implemented methods, systems, and computer program products include program code executing on a processor(s) identifies in a query from a requestor, a predicate(s) comprising a partial access of large object (LOB) data from a LOB. The processor(s) caches a location of the partial access of the LOB data in a buffer, labeling the location with predicates. The processor(s) determines, based on comparing a pre-set access count to a count of accesses for the location that the location is a frequently accessed location and stabilizes the location in an index. The processor(s) determines that the location meets a length threshold and a frequency threshold and saves the LOB data requested in the partial access of the LOB data inline with a base row.