Relational Database Join Query Scan Bypass

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Relational database systems face inefficiencies due to poorly framed machine-generated queries that result in redundant table scans, leading to increased CPU usage, memory consumption, and output delays, especially in large databases with millions of records.

Innovation Solution

A relational database system equipped with a query analyzer and optimization engine that identifies and bypasses unnecessary scans by comparing predicates, using a dummy node or input to output semantically correct results, thereby reducing CPU usage, memory usage, and input/output operations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If machine-generated queries are executed on massive databases, then data extraction capability is improved, but redundant table scans increase CPU usage and memory consumption

Engineering Contradiction:

Improvedata extraction capabilityVSAvoidCPU usage

Core Design Contradiction:

ProductivityVSUse of energy by moving object

Solution Approach 1:

The query analyzer performs preliminary analysis of join operation queries to identify redundant table scans before execution. By comparing predicates and determining scan necessity in advance, the system prevents unnecessary CPU cycles from being spent on redundant operations, thus improving productivity while reducing CPU usage.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The optimization engine extracts and removes redundant table scan operations from the query execution plan. By identifying scans that are unnecessary due to predicate evaluation results (always true or always false), the system eliminates wasted CPU cycles and memory consumption while preserving the essential data extraction functionality.

Inventive Principle:

Principle #2Taking out (Extraction)

2Productivity

If machine-generated queries are executed on massive databases, then data extraction capability is improved, but redundant table scans increase memory consumption

Engineering Contradiction:

Improvedata extraction capabilityVSAvoidmemory consumption

Core Design Contradiction:

ProductivityVSQuantity of substance

Solution Approach 1:

The query analyzer performs preliminary analysis of join operation queries to identify redundant table scans before execution. By comparing predicates and determining scan necessity in advance, the system prevents unnecessary memory allocation and consumption from redundant operations, thus improving productivity while reducing memory usage.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The optimization engine extracts and removes redundant table scan operations from the query execution plan. By identifying scans that are unnecessary due to predicate evaluation results (always true or always false), the system eliminates wasted memory consumption while preserving the essential data extraction functionality.

Inventive Principle:

Principle #2Taking out (Extraction)

3Reliability

If redundant table scans are performed, then complete data processing is ensured, but output delays increase

Engineering Contradiction:

Improvedata processing completenessVSAvoidoutput delay

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The query analyzer performs preliminary analysis to determine which table scans are redundant before query execution. By evaluating predicates in advance and identifying scans that will not contribute to meaningful results, the system eliminates unnecessary processing steps that cause output delays while ensuring that essential scans are performed for data processing completeness.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The optimization engine extracts and removes redundant table scan operations from the query execution plan. By identifying scans that are unnecessary due to predicate evaluation results, the system eliminates wasted processing time and output delays while preserving the completeness of essential data processing operations.

Inventive Principle:

Principle #2Taking out (Extraction)

4Measurement precision

If all tables are scanned in join operations, then query result accuracy is maintained, but input/output operations increase

Engineering Contradiction:

Improvequery result accuracyVSAvoidinput/output operations

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The query analyzer performs preliminary analysis of join operation queries to identify redundant table scans before execution. By comparing predicates and determining scan necessity in advance, the system performs only essential I/O operations while maintaining query result accuracy, thus improving productivity without sacrificing measurement precision.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The optimization engine extracts and removes redundant table scan operations from the query execution plan. By identifying scans that are unnecessary due to predicate evaluation results, the system eliminates wasted input/output operations while preserving the accuracy of query results through selective scanning of only necessary tables.

Inventive Principle:

Principle #2Taking out (Extraction)

Data Source

PatentUS11216463B2Relational database system join query table scan bypass

Publication Date: 2022.01.04 HEWLETT PACKARD ENTERPRISE DEV LP

AI summary

A relational database system for performing a join operation between a first table and a second table may include a query analyzer and an optimization engine. The query analyzer may compare predicates of a join operation query for an American National Standards Institute (ANSI) compliant database. The optimization engine, based upon comparison of the predicates, is to bypass a scan of the second table as part of outputting semantically correct results for the join operation.