Database Physical Design via Transaction Workload Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems fail to separate the logical and physical levels effectively, leading to performance issues due to data clustering decisions being made at the logical level, which affects transaction efficiency and requires modifications to transactions when changes are made at the physical level.
Innovation Solution
Implementing a computer-based process for data clustering through partitioning, replication, and encoding to minimize access costs, using strategies like vertical and horizontal partitioning, attribute replication, and encoding, while maintaining an intermediate layer to decouple the logical and physical schema, ensuring changes at the physical level are transparent to the logical level.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If data clustering is performed at the logical level to simplify design, then the design process becomes easier, but transaction performance deteriorates due to suboptimal clustering decisions
Solution Approach 1:
The patent segments the database design process into two distinct levels: logical level for design simplicity and physical level for performance optimization. The logical schema remains unchanged while the physical schema applies clustering strategies based on transaction workload analysis, allowing each level to fulfill its specific purpose without compromise
Solution Approach 2:
The patent introduces an intermediate physical schema layer between the logical schema and the actual data storage. This intermediate layer contains clustering information derived from transaction workload analysis, acting as a mediator that translates logical design requirements into physically optimized storage structures without affecting the logical design process
2Productivity
If physical clustering is optimized based on transaction workload, then transaction performance improves, but the complexity of the physical schema increases
Solution Approach 1:
The patent makes the physical schema dynamic by deriving clustering strategies from actual transaction workload characteristics. The physical schema adapts to different transaction patterns (read-intensive, write-intensive, mixed workloads) through automated analysis, allowing the system to optimize for current usage patterns without manual intervention or complex static design decisions
3Loss of time
If changes are made to the physical schema to optimize performance, then access cost is reduced, but modifications are required in the logical schema and transactions
Solution Approach 1:
The patent extracts clustering information from the logical schema into a separate physical schema layer. By taking out the physical optimization details from the logical design, changes in the physical schema (such as clustering strategies) can be made independently without affecting the logical schema or requiring transaction modifications, thus reducing data access cost while maintaining schema flexibility
4Ease of manufacture
If jumbo relations are created to represent entities with all attributes, then the logical design becomes simpler, but access efficiency deteriorates due to retrieving unnecessary data
Solution Approach 1:
The patent segments the attributes of jumbo relations based on transaction workload analysis. By dividing the physical storage of relation attributes according to actual access patterns, the system maintains the simple logical design of jumbo relations while physically organizing data to retrieve only necessary attributes, thereby reducing data access overhead without complicating the logical design
Data Source
AI summary
A process is disclosed for using the computer for physical database design, which is based on transaction workload, and minimizes the cost of retrieval and modification for a set of transactions on a database.