Multi-Dimensional Database Architecture for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face inefficiencies in processing complex queries due to the high number of join operations required, especially in large and complex databases, leading to increased processing delays and resource consumption, while also struggling to optimize both OLTP and OLAP queries and maintain data consistency.
Innovation Solution
A database management system that stores data in two dimensions, with one copy in a modified row format and another in a column format, allowing for sequential access and minimizing random access operations, and converts queries to determine the optimal storage format for processing, thereby reducing the need for join operations and enhancing throughput.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is stored in normalized tables with multiple relationships, then data redundancy is minimized and data independence is improved, but the number of join operations increases significantly leading to processing delays
Solution Approach 1:
The patent segments the database into two distinct storage formats: a first data store in modified row format for OLTP operations and a second data store in column format for OLAP operations. This segmentation allows each store to be optimized for its specific workload, reducing join operations for analytical queries while maintaining normalized data structure for transactional integrity.
Solution Approach 2:
The patent introduces a dimensional transformation by storing data in both row-based and column-based formats simultaneously. This dimensional approach enables the system to leverage the advantages of both storage models: row format for transactional operations requiring data independence and column format for analytical queries requiring reduced join operations, thereby resolving the contradiction between data consistency and processing speed.
2Productivity
If data is stored in row format with all related data, then sequential access is improved and random access operations are minimized, but storage space consumption increases
Solution Approach 1:
The patent divides the storage system into two separate data stores: one for row-format data and one for column-format data. This segmentation allows the system to store data in the format that minimizes storage space (column format) while still providing row-format access paths for sequential access operations, thereby resolving the contradiction between throughput and storage consumption.
Solution Approach 2:
The patent creates a universal storage system where data is stored in multiple formats simultaneously, allowing the same data to serve multiple access patterns. The column-format store provides space efficiency while the row-format store provides sequential access performance, making the system multi-functional and capable of optimizing for different workloads as needed.
3Loss of information
If complex queries are processed with multiple join operations, then data retrieval completeness is improved, but processing time and resource consumption increase
Solution Approach 1:
The patent creates a copy of the data in column format that can be used to answer analytical queries without requiring multiple join operations. This copy allows the system to retrieve complete data information more efficiently by reading from the column store in a single operation, thereby reducing processing time while maintaining data retrieval completeness.
Solution Approach 2:
The patent performs preliminary action by pre-organizing data in column format during the loading process, so that when analytical queries are executed, the data is already arranged in an optimization-friendly structure. This preliminary organization eliminates the need for complex join operations during query execution, reducing processing delay while maintaining complete data retrieval.
Data Source
AI summary
A database management system stores data form a plurality of logical tables as independent copies in different format. In one specific example the system organizes tables into table groups and de-normalizes the table in each table group. It also organizes data in a second format including vertical column containers that store all the data for one attribute, there being one column container for each table and each attribute within that table. The receipt of a query causes the system to develop a query plan that can interact with either or both of the container sets.