SQL Hint-Based Data Quality Checks in Query Execution Plans
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data quality check methods in database management systems are inefficient, leading to increased processing times, resource usage, and inaccurate results due to separate execution from main queries, lack of reusability, and disruptive data processing flows.
Innovation Solution
Implementing data quality checks using structured query language hints within query execution plans, allowing the query optimizer to integrate and execute data quality checks alongside main queries, ensuring completion is dependent on successful data quality checks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data quality checks are performed separately from main queries, then data quality can be evaluated, but processing time and resource usage increase
Solution Approach 1:
The patent combines data quality checks with main queries by integrating quality check instructions into query execution plans. The query optimizer modifies the execution plan to include both the original query operations and data quality validation operations as a unified workflow, allowing simultaneous execution rather than separate sequential processing.
Solution Approach 2:
The query execution plan is enhanced to serve multiple functions: it executes the main data retrieval query while simultaneously performing data quality checks. This multi-functional approach allows a single execution plan to handle both data access and quality validation, eliminating the need for separate check processes.
2Reliability
If data quality checks are performed separately from main queries, then data quality can be evaluated, but resource consumption increases
Solution Approach 1:
The patent merges data quality check operations with main query execution operations into a single integrated process. By combining these operations, the system avoids duplicating resource-intensive activities such as data scanning and processing, thereby reducing overall resource consumption while maintaining comprehensive quality evaluation.
Solution Approach 2:
The data quality check system leverages the existing query execution infrastructure and data access pathways. Instead of creating separate dedicated check processes that would consume additional resources, the system uses the query execution plan's inherent data access mechanisms to perform quality checks, making the system self-sufficient and resource-efficient.
3Productivity
If data quality checks are integrated into query execution plans, then processing efficiency improves, but query optimizer complexity increases
Solution Approach 1:
The query optimizer performs preliminary processing by incorporating data quality check instructions into the query execution plan before actual query execution. This advance preparation includes identifying quality check requirements, integrating them with query operations, and optimizing the combined workflow, thereby simplifying the execution phase while maintaining efficiency.
Solution Approach 2:
The patent introduces an intermediary mechanism in the form of quality check instructions that act as mediators between the query optimizer and the execution engine. These instructions serve as a standardized interface that translates quality requirements into executable operations, managing the complexity of integration without burdening either the optimizer or the execution engine with excessive complexity.
4Loss of time
If data quality checks are integrated into query execution plans, then processing time reduces, but query execution plan complexity increases
Solution Approach 1:
The query execution plan is prepared in advance with quality check operations pre-integrated and optimized. By performing the integration and optimization work during the plan generation phase rather than during execution, the system reduces actual processing time while managing complexity through upfront preparation.
Solution Approach 2:
The integrated execution plan maintains continuous useful action by seamlessly interweaving quality check operations with data retrieval operations. Rather than introducing discrete interruptive check steps, the plan creates a continuous workflow where quality validation occurs naturally during data access operations, reducing overall execution time while managing complexity through flow integration.
Data Source
AI summary
A data quality check using a structured query language hint is described. A query optimizer of a database management system may receive a data quality check instruction as part of a database query statement for a database query. The query optimizer may modify a query execution plan for the database query based on the data quality check instruction so that completion of the database query is dependent on a result of a data quality check defined by the data quality check instruction. The database management system may execute the modified query execution plan.