Adaptive Filter Index for DML Query Change Detection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for determining query result changes due to DML operations are inefficient and scalable, particularly in large database systems, as they require extensive resource usage and can lead to performance and latency issues, especially when monitoring multiple queries across numerous tables.
Innovation Solution
An adaptive filter index is implemented to efficiently determine which queries are affected by DML operations by generating filter conditions and using a filter index data structure to identify candidate queries, reducing the need for extensive re-evaluation and improving scalability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the trigger approach is used to monitor query result changes, then users can be informed of changes continuously, but the system complexity and resource consumption increase significantly due to requiring separate triggers for each table
Solution Approach 1:
The patent segments the monitoring function by creating separate trigger modules for different types of DML operations (INSERT, UPDATE, DELETE) on different table types (heap tables, index-organized tables). Each trigger is responsible for specific change detection tasks, allowing independent management and optimization of each trigger module rather than requiring a single complex trigger system to handle all scenarios.
Solution Approach 2:
The patent introduces intermediate data structures (change tracking buffers, notification queues) that mediate between the triggers and the query evaluation system. These intermediaries buffer and organize change information before passing it to query evaluators, reducing the direct coupling and complexity between triggers and query processing while maintaining reliable change detection.
2Speed
If DML triggers are invoked while transactions are still active to enable continuous monitoring, then real-time detection is achieved, but correctness issues arise due to visibility problems with active transaction states
Solution Approach 1:
The patent performs preliminary capture of change information in triggers while transactions are active, but delays the actual query evaluation and result comparison until after transaction commit. Change data is buffered and held in intermediate structures during the transaction, ensuring that when evaluation occurs, it uses the committed state rather than the intermediate active state, thus maintaining correctness while preserving real-time monitoring capability.
3Reliability
If all queries are re-evaluated after every DML operation to ensure accurate change detection, then complete accuracy is achieved, but system performance and throughput deteriorate significantly
Solution Approach 1:
The patent implements partial re-evaluation by first using triggers to identify only the specific rows and tables that have changed, then using this filtered information to determine which queries need re-evaluation. Instead of re-evaluating all queries, the system performs partial evaluation only on queries that reference changed data, significantly reducing the evaluation burden while maintaining accurate change detection for affected queries.
Solution Approach 2:
The patent performs preliminary filtering of change information in triggers to identify only relevant changed rows and tables before query evaluation. This preliminary action creates a filtered set of change data that is then used to selectively invoke query re-evaluation only for queries that could be affected, avoiding the excessive action of evaluating all queries and thus preserving OLTP throughput while maintaining detection accuracy.
4Reliability
If log mining is used to identify changes for query evaluation, then a source of changes is obtained, but severe scalability issues and high I/O costs occur due to scanning redo logs for every transaction
Solution Approach 1:
The patent performs preliminary capture of change information at the source (in DML triggers during transaction processing) rather than mining logs after transactions complete. This preliminary action records changes in real-time as they occur, eliminating the need for subsequent log scanning to identify changes. The change information is already captured and buffered when transactions commit, making log mining unnecessary and thus eliminating the I/O overhead and time loss associated with scanning redo logs.
Data Source
AI summary
Techniques are disclosed for creating and using a filter index in order to identify registered queries whose result sets are likely to have been changed by changes made to tables. The filter index entries are based on filter conditions. The filter conditions are created based on predicates contained in the registered queries. The filter conditions may include exclusive predicates and join predicates. Join predicates that join a table T1 with a table T2 may be instantiated by replacing references to table T2 with values extracted from table T2. Various techniques are described for recognizing situations in which a query can be included in, or excluded from, the set of likely-changed queries, without fully evaluating the filter conditions that correspond to the queries.