Autonomic Index Creation via Policy-Based Automation

Solution Overview

Problem

Database systems rely on manual methods for creating indexes, which are inefficient and time-consuming, as users must manually decide when and how to build indexes based on query performance data.

Innovation Solution

An index advice record engine generates advice records, and an autonomic index creation mechanism automates the process of creating indexes based on user-defined policies, determining when to create indexes based on criteria such as query frequency and performance metrics.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If manual methods are used for creating indexes, then users have control over index creation decisions, but the process is time-consuming and inefficient

Engineering Contradiction:

Improveindex creation efficiencyVSAvoidtime required for manual index creation

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The system enables self-service by implementing an autonomic index creation mechanism that automatically generates and applies indexes based on query workloads without requiring manual user intervention. The mechanism monitors database queries, identifies indexing opportunities, and creates indexes autonomously, allowing the system to serve itself rather than relying on external user actions.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system performs preliminary action by proactively creating indexes before they are explicitly requested by users. The autonomic mechanism continuously analyzes query patterns and preemptively generates indexes that will improve future query performance, rather than waiting for users to manually identify and create them after performance problems arise.

Inventive Principle:

Principle #10Preliminary action

2Ease of operation

If manual index creation is used, then users can make informed decisions about when to build indexes, but the process requires significant user effort and expertise

Engineering Contradiction:

Improveease of index creationVSAvoidcomplexity of manual index management process

Core Design Contradiction:

Ease of operationVSDevice complexity

Solution Approach 1:

The system enables self-service by implementing an autonomic index creation mechanism that automatically generates and applies indexes based on query workloads without requiring manual user intervention. The mechanism monitors database queries, identifies indexing opportunities, and creates indexes autonomously, allowing the system to serve itself rather than relying on external user actions.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system introduces an intermediary component - the autonomic index creation mechanism - that acts as a mediator between the database workload and the index management process. This intermediary automatically translates query patterns into index creation decisions, eliminating the need for users to directly manage indexes while maintaining optimal performance.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Productivity

If automation is implemented for index creation, then time and effort are reduced, but the system requires autonomous decision-making capabilities

Engineering Contradiction:

Improveindex creation speedVSAvoidautonomic index creation capability

Core Design Contradiction:

ProductivityVSExtent of automation

Solution Approach 1:

The system enables self-service by implementing an autonomic index creation mechanism that automatically generates and applies indexes based on query workloads without requiring manual user intervention. The mechanism monitors database queries, identifies indexing opportunities, and creates indexes autonomously, allowing the system to serve itself rather than relying on external user actions.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system implements feedback by continuously monitoring database query workloads and using this information to drive index creation decisions. The autonomic mechanism observes query patterns, evaluates their performance impact, and adjusts index creation accordingly, creating a closed-loop system where performance data feeds back into optimization actions.

Inventive Principle:

Principle #23Feedback

Data Source

PatentUS8838573B2Autonomic index creation

Publication Date: 2014.09.16 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

An index advice record engine generates and stores index advice records. An index advice policy mechanism allows a user to define an index advice policy that specifies criteria for autonomic index creation. An autonomic index creation mechanism reads the index advice records, compares this information with the criteria in the user-defined index advice policies, and determines whether an index should be created based on the information in the index advice records and the index advice policies. By automating the process of creating indexes according to user-defined policies, the preferred embodiments alleviate human users from most of the work of manually creating indexes.