Database Cluster View System for Materialized View Compaction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Background maintenance services for databases function independently, leading to suboptimal performance and high costs due to unnecessary re-clustering and materialized view maintenance triggered by minimal data changes, as they are unaware of each other's operations.
Innovation Solution
A cluster view system is implemented to coordinate clustering and compaction processes, identifying unnecessary maintenance jobs and avoiding scheduling them, while performing integrated tasks like materialized view compaction and clustering within a single operation to maintain data integrity and reduce computational resources used.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If background maintenance services function independently to perform clustering and materialized view maintenance, then data organization and query performance are improved, but computational resources are wasted and costs increase due to redundant operations
Solution Approach 1:
The patent merges the clustering service and materialized view maintenance service into a coordinated system where the clustering service identifies clustered columns and the maintenance service uses this information to skip unnecessary maintenance operations on those columns, thereby eliminating redundant computational work while maintaining data organization quality
Solution Approach 2:
The patent implements a feedback mechanism where the clustering service provides information about clustered columns to the materialized view maintenance service, which then adjusts its maintenance schedule and operations based on this feedback to avoid redundant work on already-clustered data
2Reliability
If materialized view maintenance is performed frequently to ensure data accuracy, then data integrity is improved, but database performance degrades due to excessive maintenance operations
Solution Approach 1:
The patent applies partial action by performing maintenance operations only on non-clustered columns rather than all columns in the materialized view, reducing the frequency and scope of maintenance while still ensuring data integrity for the necessary portions of the data
Solution Approach 2:
The patent combines clustering information with materialized view maintenance logic to create an integrated maintenance strategy that considers both data integrity requirements and performance optimization by avoiding redundant operations on clustered data
3Speed
If clustering operations are performed on all columns to maximize query efficiency, then query speed is improved, but system complexity and operational overhead increase
Solution Approach 1:
The patent applies local quality by identifying specific columns that benefit from clustering based on query patterns and data characteristics, rather than uniformly clustering all columns, thereby optimizing query performance for critical paths while reducing overall system complexity
Solution Approach 2:
The patent implements dynamic column identification where the clustering service continuously monitors query patterns and data characteristics to adaptively determine which columns should be clustered, allowing the system to optimize for query speed while managing complexity through intelligent, data-driven decisions
Data Source
AI summary
Methods, systems, and computer programs are presented for providing a cluster view method of a database to perform compaction and clustering of database objects, such as database materialized view. A cluster view system identifies a materialized view including data from one or more base tables, a portion of the data of the materialized view including stale data. The cluster view system performs an integrated task within a maintenance operation on a database, the integrated task including compacting the materialized view, the maintenance operation including clustering the materialized view, and stores the compacted and clustered materialized view in the database.