Hybrid Memory Disk Table Partitioning for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face challenges in achieving high response speed and efficient storage capacity utilization due to the limitations of memory-based databases, which are not suitable for large data storage due to memory size limitations, and disk-based databases are hindered by IO performance issues.
Innovation Solution
The extension of table partitioning technology to enable hybrid use of disk and memory tablespaces, allowing for data discrimination and optimal scan plan creation based on storage differences, with latest data stored in memory and old data transferred to disk, thereby optimizing storage usage and query response.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in memory-based database, then query response speed is improved, but storage capacity is limited
Solution Approach 1:
The database storage is segmented into multiple tablespaces with different storage media (memory tablespace and disk tablespace). Different partitions of the same logical table can be stored in different tablespaces, allowing the system to simultaneously utilize both fast memory storage for recent data and large-capacity disk storage for historical data, thus resolving the contradiction between query speed and storage capacity.
Solution Approach 2:
Different parts of the data (recent vs. historical) are stored with different local qualities - recent data is stored in high-speed memory tablespace for fast access, while historical data is stored in high-capacity disk tablespace. This local differentiation allows the system to optimize for both speed and capacity in different data regions.
2Quantity of substance
If data is stored in disk-based database, then storage capacity is increased, but IO performance deteriorates
Solution Approach 1:
The storage system is segmented into memory tablespace and disk tablespace. By partitioning tables and storing different segments in different tablespaces, the system can maintain large storage capacity on disk while keeping frequently accessed recent data in memory, thus improving IO performance without sacrificing storage capacity.
Solution Approach 2:
The memory tablespace acts as an intermediary between the query processing system and the disk tablespace. Recently accessed data is kept in the memory tablespace, serving as a buffer that mediates between fast query processing requirements and large-capacity disk storage, thereby improving overall IO performance.
3Adaptability or versatility
If table partitioning is extended to hybrid storage, then data discrimination and optimal scan plan creation are enabled, but device complexity increases
Solution Approach 1:
The table partitioning mechanism is extended to work universally across both memory and disk tablespaces. The same partitioning logic and metadata management structure are used regardless of the underlying storage medium, allowing the system to maintain data discrimination capabilities while managing complexity through a unified approach rather than separate mechanisms for each storage type.
Data Source
AI summary
The present invention provides an apparatus and method for processing a query in a relational database system having a hybrid storage of a memory tablespace and a disk tablespace, with the configuration of the present invention, by extending a table partitioning technology in order for the relational database system to use the disk tablespace and memory tablespace in a hybrid manner, it is possible to implement data discrimination due to different data storages in one logical table and create scan plans in compliance with the difference in the storages, thereby establishing an optimal scan plan to the query intended to access the storages having different properties.