# US 6,321,218 B1 · Automatically determining data that is best suited for index tuning

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 6,321,218 B1
- Document ID: US6321218B1
- Status: Granted
- Kind: B1
- Application number: 09/256,810
- Filed: 1999-02-24
- Granted: 2001-11-20
- Earliest claimed: 1999-02-24
- Assignees: Oracle Corporation
- Inventors: Todd Guay; Gregory Smith; Gaylen Royal; Cheryl Mrozienski
- Web page: https://patentgrep.com/patent/US6321218B1

## CPC

- G06F 16/2272: {Management thereof}
- Y10S 707/99932: Access augmentation or optimizing
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)
- Y10S: TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS

## Abstract

An approach for automatically determining database tables that are best suited for index tuning involves analyzing query performance data that specifies an average amount of time required to execute a plurality of queries on the database tables. One or more particular queries are selected from the plurality of queries based upon the query performance data. Data tables required to process the one or more particular queries are selected for index tuning.

## Claims

### Claim 1

A method for automatically determining, from a plurality of bodies of data, which bodies of data are being accessed inefficiently, relative to other bodies of data from the plurality of bodies of data, the method comprising the computer implemented steps of:
examining query performance data that indicates performance of a plurality of queries that access the plurality of bodies of data; and
selecting, based upon both the query performance data and a set of one or more candidate selection factors, particular bodies of data from the plurality of bodies of data that are being accessed inefficiently relative to other bodies of data from the plurality of bodies of data.

### Claim 2 (depends on 1)

The method as recited in claim 1, wherein:
the method further comprises the step of selecting particular queries from the plurality of queries based on one or more query selection criteria, and
the set of one or more candidate selection factors includes whether a body of data is accessed during the processing of any of the particular queries.

### Claim 3 (depends on 2)

The method as recited in claim 2, wherein:
the query performance data specifies an amount of time required to execute each of the plurality of queries, and
the step of selecting particular queries from the plurality of queries based upon one or more query selection criteria includes selecting particular queries from the plurality of queries based upon the amount of time required to execute each of the plurality of queries.

### Claim 4 (depends on 2)

The method as recited in claim 2, wherein:
the query performance data specifies an average number of input/output operations (I/Os) required to execute each of the plurality of queries, and
the step of selecting particular queries from the plurality of based upon one or more query selection criteria includes selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries.

### Claim 5 (depends on 4)

The method as recited in claim 4, wherein the steps of selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries includes selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries and a threshold number of I/Os per query.

### Claim 6 (depends on 2)

The method as recited in claim 2, wherein:
the set of one or more candidate selection factors further includes whether a body of data has been designated by a user, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data has been designated by a user and is accessed during the processing of any of the particular queries.

### Claim 7 (depends on 2)

The method as recited in claim 2, wherein:
the set of one or more candidate selection factors further includes whether a current number of input/output operations (I/Os) per query execution is within a specified range of a prior average number of I/Os per query execution, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data has been accessed during the processing of any of the particular queries and whether a current number of I/Os per query execution for the body of data is within a specified range of a prior average number of I/Os per query execution for the body of data.

### Claim 8 (depends on 2)

The method as recited in claim 2, wherein:
the set of one or more candidate selection factors includes whether a body of data has been accessed by at least specified number of queries during a specified period of time, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data is accessed at least a specified number of the particular queries during a specified period of time.

### Claim 9 (depends on 2)

The method as recited in claim 2, wherein:
the set of one or more candidate selection factors include whether the number of the particular queries that accessed a body of data during a first specified period of time within a specified range of the number of the particular queries that accessed the body of data during a second specified period of time, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether the number of the particular queries that accessed the body of data during a first specified period of time is within a specified range of the number of the particular queries that accessed the body of data during a second specified period of time.

### Claim 10

A method for performing index tuning on particular tables from a plurality of tables in a database system, the method comprising the computer-implemented steps of:
selecting particular queries from a plurality of queries based upon the performance of the plurality of queries;
identifying a set of one or more tables accessed during the execution of the particular queries;
selecting particular tables from the set of one or more tables based upon table selection criteria; and
performing index tuning on the particular tables.

### Claim 11

A computer-readable medium carrying one or more sequences of one or more instructions for automatically determining, from a plurality of bodies of data, which bodies of data are being accessed inefficiently, relative to other bodies of data from the plurality of bodies of data, the one or more sequences of one or more instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:
examining query performance data that indicates performance of a plurality of queries that access the plurality of bodies of data; and
selecting, based upon both the query performance data and a set of one or more candidate selection factors, particular bodies of data from the plurality of bodies of data that are being accessed inefficiently relative to other bodies of data from the plurality of bodies of data.

### Claim 12 (depends on 11)

The computer-readable medium as recited in claim 11, wherein:
the instructions include instructions for
selecting particular queries from the plurality of queries based on one or more query selection criteria, and
the set of one or more candidate selection factors includes whether a body of data is accessed during the processing of any of the particular queries.

### Claim 13 (depends on 12)

The computer-readable medium as recited in claim 12, wherein:
the query performance data specifies an amount of time required to execute each of the plurality of queries, and
the step of selecting particular queries from the plurality of queries based upon one or more query selection criteria includes selecting particular queries from the plurality of queries based upon the amount of time required to execute each of the plurality of queries.

### Claim 14 (depends on 12)

The computer-readable medium as recited in claim 12, wherein:
the query performance data specifies an average number of input/output operations (I/Os) required to execute each of the plurality of queries, and
the step of selecting particular queries from the plurality of based upon one or more query selection criteria includes selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries.

### Claim 15 (depends on 14)

The computer-readable medium as recited in claim 14, wherein the steps of selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries includes selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries and a threshold number of I/Os per query.

### Claim 16 (depends on 12)

The computer-readable medium as recited in claim 12, wherein:
the set of one or more candidate selection factors further includes whether a body of data has been designated by a user, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data has been designated by a user and is accessed during the processing of any of the particular queries.

### Claim 17 (depends on 12)

The computer-readable medium as recited in claim 12, wherein:
the set of one or more candidate selection factors further includes whether a current number of input/output operations (I/Os) per query execution is within a specified range of a prior average number of I/Os per query execution, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data has been accessed during the processing of any of the particular queries and whether a current number of I/Os per query execution for the body of data is within a specified range of a prior average number of I/Os per query execution for the body of data.

### Claim 18 (depends on 12)

The computer-readable medium as recited in claim 12, wherein:
the set of one or more candidate selection factors includes whether a body of data has been accessed by at least specified number of queries during a specified period of time, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data is accessed at least a specified number of the particular queries during a specified period of time.

### Claim 19 (depends on 12)

The computer-readable medium as recited in claim 12, wherein:
the set of one or more candidate selection factors include whether the number of the particular queries that accessed a body of data during a first specified period of time within a specified range of the number of the particular queries that accessed the body of data during a second specified period of time, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether the number of the particular queries that accessed the body of data during a first specified period of time is within a specified range of the number of the particular queries that accessed the body of data during a second specified period of time.

### Claim 20

A computer system for automatically determining, from a plurality of bodies of data, which bodies of data are being accessed inefficiently, relative to other bodies of data from the plurality of bodies of data, the computer system comprising: one or more processors; and
a memory coupled to the one or more processors and containing one or more sequences of one or more instructions which, when executed by the one or more processors, cause the one or more processors to perform the steps of:
examining query performance data that indicates performance of a plurality of queries that access the plurality of bodies of data; and
selecting, based upon both the query performance data and a set of one or more candidate selection factors, particular bodies of data from the plurality of bodies of data that are being accessed inefficiently relative to other bodies of data from the plurality of bodies of data.

### Claim 21 (depends on 20)

The computer system as recited in claim 20, wherein:
the instructions include instructions for
selecting particular queries from the plurality of queries based on one or more query selection criteria, and
the set of one or more candidate selection factors includes whether a body of data is accessed during the processing of any of the particular queries.

### Claim 22 (depends on 21)

The computer system as recited in claim 21, wherein:
the query performance data specifies an amount of time required to execute each of the plurality of queries, and
the step of selecting particular queries from the plurality of queries based upon one or more query selection criteria includes selecting particular queries from the plurality of queries based upon the amount of time required to execute each of the plurality of queries.

### Claim 23 (depends on 21)

The computer system as recited in claim 21, wherein:
the query performance data specifies an average number of input/output operations (I/Os) required to execute each of the plurality of queries, and
the step of selecting particular queries from the plurality of based upon one or more query selection criteria includes selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries.

### Claim 24 (depends on 23)

The computer system as recited in claim 23, wherein the steps of selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries includes selecting particular queries from the plurality of queries based upon the average number of I/Os required to execute each of the plurality of queries and a threshold number of I/Os per query.

### Claim 25 (depends on 21)

The computer system as recited in claim 21, wherein:
the set of one or more candidate selection factors further includes whether a body of data has been designated by a user, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data has been designated by a user and is accessed during the processing of any of the particular queries.

### Claim 26 (depends on 21)

The computer system as recited in claim 21, wherein:
the set of one or more candidate selection factors further includes whether a current number of input/output operations (I/Os) per query execution is within a specified range of a prior average number of I/Os per query execution, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data has been accessed during the processing of any of the particular queries and whether a current number of I/Os per query execution for the body of data is within a specified range of a prior average number of I/Os per query execution for the body of data.

### Claim 27 (depends on 21)

The computer system as recited in claim 21, wherein:
the set of one or more candidate selection factors includes whether a body of data has been accessed by at least specified number of queries during a specified period of time, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether a body of data is accessed at least a specified number of the particular queries during a specified period of time.

### Claim 28 (depends on 21)

The computer system as recited in claim 21, wherein:
the set of one or more candidate selection factors include whether the number of the particular queries that accessed a body of data during a first specified period of time within a specified range of the number of the particular queries that accessed the body of data during a second specified period of time, and
the step of selecting particular bodies of data from the plurality of bodies of data is performed based upon whether the number of the particular queries that accessed the body of data during a first specified period of time is within a specified range of the number of the particular queries that accessed the body of data during a second specified period of time.
