Database Query Optimization via Compile-Time Path Resolution

Solution Overview

Problem

Database systems face inefficiencies in resolving resource paths and evaluating queries due to the computational overhead of using extensible indexing mechanisms, particularly when dealing with hierarchical data types, as they require separate evaluation mechanisms for procedural and query languages, leading to increased processing time and resource utilization.

Innovation Solution

Optimizing extensible operators by evaluating them using native database functions instead of extensible indexing mechanisms, and resolving resource paths at compile time to store mappings within query cursors, reducing the need for repeated lookups and conversions between different language evaluation mechanisms.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If extensible indexing mechanisms are used to support complex data types, then adaptability is improved, but device complexity and processing time increase

Engineering Contradiction:

Improvesupport for complex data typesVSAvoidseparate evaluation mechanisms

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent extracts the resource path resolution function from the query execution phase and moves it to the compile time phase. By resolving resource paths during compilation and storing the mappings in cursor data structures, the system eliminates the need for repeated resolution operations during query execution, thereby reducing the complexity of separate evaluation mechanisms while maintaining support for complex data types through extensible indexes.

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent applies preliminary action by resolving resource paths at compile time before query execution. The compiler resolves resource paths and stores the mappings in cursor data structures, so that when queries are executed, the resource path resolution is already completed. This preliminary resolution eliminates the need for repeated resolution operations during query execution, reducing processing time and system complexity.

Inventive Principle:

Principle #10Preliminary action

2Adaptability or versatility

If resource paths are resolved at execution time, then adaptability is maintained, but loss of time increases

Engineering Contradiction:

Improvequery execution flexibilityVSAvoidcomputational overhead

Core Design Contradiction:

Adaptability or versatilityVSLoss of time

Solution Approach 1:

The patent applies preliminary action by resolving resource paths at compile time before query execution. The compiler resolves resource paths and stores the mappings in cursor data structures, so that when queries are executed, the resource path resolution is already completed. This preliminary resolution eliminates the need for repeated resolution operations during query execution, reducing processing time while maintaining query flexibility through the cursor-based execution mechanism.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent creates a copy of the resource path mapping information and stores it in cursor data structures. Instead of resolving resource paths repeatedly during query execution, the system uses pre-resolved path mappings stored in cursors, which are reused across multiple query executions. This copying approach eliminates redundant computation while maintaining the ability to handle various query scenarios.

Inventive Principle:

Principle #26Copying

3Adaptability or versatility

If extensible operators are evaluated using extensible indexing mechanisms, then adaptability is maintained, but productivity decreases

Engineering Contradiction:

Improveoperator evaluation capabilityVSAvoidquery execution speed

Core Design Contradiction:

Adaptability or versatilityVSProductivity

Solution Approach 1:

The patent extracts the resource path resolution function from the query execution phase and moves it to the compile time phase. By resolving resource paths during compilation and storing the mappings in cursor data structures, the system eliminates the need for repeated resolution operations during query execution, thereby reducing the complexity of separate evaluation mechanisms while maintaining support for complex data types through extensible indexes.

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent replaces the mechanical process of repeated resource path resolution and extensible index evaluation with a more efficient system that uses pre-resolved path mappings stored in cursors. The query execution mechanism substitutes the time-consuming extensible indexing evaluation with direct access to pre-computed path mappings, significantly improving query execution speed while maintaining the ability to handle complex data types.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS9146961B2Techniques for efficient queries on a file system-like repository

Publication Date: 2015.09.29 ORACLE INT CORP

AI summary

Techniques are provided to achieve performance improvements for path-based access to hierarchical data and for utilizing an extensible indexing framework. Extensible operators may be optimized by evaluating the operators using functions native to the database system instead of using functions defined through an extensible indexing mechanism. Furthermore, the database system may resolve a resource path of a query when the query is compiled, and may store the mapping of the resource path to one or more resources in a cursor for the query execution plan to avoid resolving the resource path each time the cursor is used to run the query. The cursor may be made dependent on the one or more resources to which the resource path refers at compile time. This takes advantage of the observation that the mapping of the resource path to particular resources rarely changes between evaluations of a particular query.