Columnar Storage Assignment in Online Transactional Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face inefficiencies in assigning database elements to columnar storage, as current methods lack effective criteria for determining which elements to store in columnar format, leading to suboptimal query performance and resource utilization.
Innovation Solution
A computer-implemented method that analyzes database elements based on table dimension, entity relationship, compatibility with schema structure, and query workload to automatically or manually assign them to columnar storage, using access statistics and relational data structures to determine the most suitable storage type.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database elements are assigned to columnar storage without effective criteria, then storage flexibility is maintained, but query performance and resource utilization deteriorate
Solution Approach 1:
The system automatically analyzes database elements and assigns them to columnar storage based on query patterns and schema structure without requiring manual intervention. The database management system self-evaluates which elements benefit from columnar storage and performs the assignment autonomously, resolving the contradiction between achieving optimal query performance and avoiding complex manual assignment processes
Solution Approach 2:
The system continuously monitors query workloads and access statistics, using this feedback to dynamically determine which database elements should be assigned to columnar storage. This feedback mechanism enables the system to adapt to changing query patterns and maintain optimal query performance automatically
2Manufacturing precision
If manual assignment of database elements to columnar storage is used, then assignment precision can be controlled, but operation complexity and time consumption increase
Solution Approach 1:
The system provides automatic assignment capability that eliminates the need for manual intervention while maintaining high assignment precision through systematic analysis of query patterns and schema structure. This self-service approach resolves the contradiction by achieving precise assignment automatically without requiring user expertise or time investment
Solution Approach 2:
The system performs preliminary analysis of database elements, query workloads, and schema structures to pre-determine optimal storage assignments before actual queries are executed. This preliminary action ensures precise assignment is made in advance, eliminating the need for manual intervention during query execution
3Reliability
If all database elements are assigned to columnar storage, then query performance may improve, but resource utilization and storage efficiency worsen
Solution Approach 1:
The system applies columnar storage selectively to specific database elements based on their individual characteristics, query patterns, and schema structure rather than uniformly to all elements. This local quality approach ensures that only elements that benefit from columnar storage are assigned to it, optimizing query performance for those elements while avoiding unnecessary resource consumption for elements that would be better served by other storage methods
Solution Approach 2:
The system dynamically changes the storage assignment parameters for different database elements based on analyzed query patterns and schema structures. By adjusting which elements are assigned to columnar storage based on changing parameters like query frequency and data access patterns, the system optimizes the balance between query performance improvement and resource consumption
Data Source
AI summary
A method, apparatus, and computer program product for assigning data to columnar storage is disclosed. In one aspect of the invention, a computer implemented method is provided comprising analyzing, on one or more computers, a database stored in a storage system accessible from the one or more computers. The method also comprises determining, on one or more computers, one or more database elements from the database to be assigned to a columnar storage in the database and assigning, on one or more computers, the one or more database elements to the columnar storage in the database. The one or more database elements to be assigned to the columnar storage is determined based on at least one of a table dimension, entity relationship, compatibility with a specified schema structure, relational data structure of the database, access statistics of the database element or incoming query workload of the database element.