Machine Learning Index Advisor for OLTP Query and DML Balance

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Optimizing index selection in database management systems for online transaction processing (OLTP) workloads is challenging due to the need for balancing performance and storage, as creating too few or too many indexes can lead to suboptimal query performance and excessive maintenance, and existing methods rely on heuristic cost estimates that may not accurately reflect real execution times.

Innovation Solution

A machine learning-based index advisor that generates recommendations for creating and dropping indexes by using feature vectors from query plans and offline-trained models, considering both query and DML performance, and providing performance and storage estimates, without requiring retraining for new datasets.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Speed

If too many indexes are created to improve query performance, then SELECT statement performance is improved, but index maintenance during DML operations increases excessively

Engineering Contradiction:

Improvequery performanceVSAvoidDML operation performance

Core Design Contradiction:

SpeedVSProductivity

Solution Approach 1:

The system dynamically adjusts index-related parameters based on workload characteristics. The machine learning model analyzes query patterns and automatically determines the optimal number and type of indexes, changing the system configuration from static to adaptive to resolve the contradiction between query performance and DML maintenance overhead

Inventive Principle:

Principle #35Parameter changes

Solution Approach 2:

The index advisor system automatically monitors workload patterns and self-adjusts index recommendations without requiring manual intervention. The system serves itself by using its own performance data to refine index suggestions, eliminating the need for DBAs to manually balance query performance against maintenance costs

Inventive Principle:

Principle #25Self-service

2Speed

If more indexes are created to improve query performance, then data retrieval speed is improved, but storage requirements increase

Engineering Contradiction:

Improvedata retrieval speedVSAvoidstorage space

Core Design Contradiction:

SpeedVSQuantity of substance

Solution Approach 1:

Instead of creating indexes for all possible query scenarios, the system applies partial action by selecting only the most critical indexes based on actual workload analysis. The machine learning model identifies the subset of indexes that provide the greatest performance benefit relative to storage cost, avoiding the excessive creation of unnecessary indexes

Inventive Principle:

Principle #16Partial or excessive action

3Speed

If manual index selection is performed to optimize performance, then query performance can be improved, but the complexity and time required for DBA tasks increases

Engineering Contradiction:

Improvequery performanceVSAvoidDBA task complexity

Core Design Contradiction:

SpeedVSDevice complexity

Solution Approach 1:

The system performs self-service by automatically analyzing workload patterns and generating index recommendations without requiring deep DBA expertise. The machine learning model encapsulates complex optimization logic, allowing the system to optimize itself while reducing the skill burden on database administrators

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The patent replaces the mechanical manual process of index selection with an automated machine learning-based system. Instead of DBAs manually analyzing query patterns and making decisions, the system uses algorithms to automatically perform the analysis and generate recommendations, substituting human effort with computational automation

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

4Ease of operation

If heuristic cost estimates are used for index selection, then the process is simple, but the accuracy of performance prediction is insufficient

Engineering Contradiction:

Improveindex selection simplicityVSAvoidperformance prediction accuracy

Core Design Contradiction:

Ease of operationVSMeasurement precision

Solution Approach 1:

The system replaces simple heuristic estimation with machine learning-based prediction. The ML model learns from historical performance data to provide accurate predictions of how different index configurations will affect query performance, substituting rough estimates with data-driven predictions while maintaining automated operation

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS20250328445A1Index Advisor For Online Transaction Processing Workloads In Database Management Systems

Publication Date: 2025.10.23 ORACLE INT CORP

AI summary

A machine learning (ML) based index advisor is provided to help optimize database systems for better cost and performance. The index advisor considers both the performance of queries and the cost of maintaining the indexes. It also provides performance and storage estimates, as well as explanations for the recommendations that are generated. The index advisor generates an index recommendation by generating a set of candidate indexes and applying a trained ML model to operations in the workload and each candidate index to determine a predicted performance benefit. The index advisor determines a total performance benefit for each candidate index.