Query Optimization via Module Sampling in Parallel Databases

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

In large relational database systems, optimizing queries to reduce resource utilization is crucial for performance improvement, especially in decision support and data mining applications, where the penalty for a suboptimal query is high, but existing methods lack accuracy in determining the most efficient execution plan due to incomplete data distribution statistics across multiple processing modules.

Innovation Solution

A method that specifies module group characteristics, identifies corresponding modules, samples statistics from these modules, and optimizes the execution plan based on the sampled statistics to determine the most resource-conserving plan, ensuring accurate distribution of data across processing modules.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If query optimization is performed in relational database systems with large volumes of data, then resource utilization is reduced and performance is improved, but the complexity of determining the most efficient execution plan increases due to incomplete data distribution statistics across multiple processing modules

Engineering Contradiction:

Improvequery performanceVSAvoidexecution plan optimization complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent applies partial action by sampling statistics from a subset of processing modules rather than requiring complete statistics from all modules. The optimizer samples data distribution statistics from a representative sample of modules to make execution plan decisions, which reduces the complexity of gathering and processing complete statistics while still achieving sufficient accuracy for optimization.

Inventive Principle:

Principle #16Partial or excessive action

2Adaptability or versatility

If data is stored on multiple processing modules, then system scalability and parallel processing capability are improved, but the accuracy of data distribution statistics decreases making it harder to determine optimal execution plans

Engineering Contradiction:

Improvesystem scalabilityVSAvoiddata distribution statistics accuracy

Core Design Contradiction:

Adaptability or versatilityVSMeasurement precision

Solution Approach 1:

The patent uses sampling to create a representative copy of data distribution statistics from multiple processing modules. Instead of requiring complete and accurate statistics from all modules, the system samples statistics from a representative subset that mirrors the overall data distribution pattern, enabling accurate optimization decisions while maintaining system scalability.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The system performs partial sampling of statistics from a subset of processing modules rather than collecting complete statistics from all modules. This partial action approach maintains sufficient statistical accuracy for optimization while reducing the overhead of gathering comprehensive data across all scalable modules.

Inventive Principle:

Principle #16Partial or excessive action

3Measurement precision

If complete data distribution statistics are collected from all processing modules, then execution plan optimization accuracy is improved, but the time and resources required for statistics gathering and processing increase

Engineering Contradiction:

Improveexecution plan optimization accuracyVSAvoidstatistics gathering time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The patent applies partial action by collecting and processing statistics from only a sample of processing modules rather than all modules. This sampling approach reduces the time and computational resources required for statistics gathering while maintaining sufficient accuracy for effective execution plan optimization.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The system extracts and samples only the necessary statistical information from processing modules rather than collecting complete data distribution statistics from all modules. This extraction approach focuses on gathering only the key statistical parameters needed for optimization decisions, significantly reducing processing time and resource consumption.

Inventive Principle:

Principle #2Taking out (Extraction)

Data Source

PatentUS7877372B1Method and system for querying tables stored on multiple processing modules

Publication Date: 2011.01.25 TERADATA US INC

AI summary

A method, computer program, and database system are disclosed for querying tables stored on multiple processing modules. The method includes specifying module group characteristics. A plurality of modules corresponding to the module group characteristics are then identified. The identified modules are sampled for statistics concerning at least one table specified in a query. An execution plan for the query is optimized based at least in part on the sampled statistics.