# US 2009/0276394 A1 · Obtaining a plan for executing a query in a relational database

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

- Number: US 2009/0276394 A1
- Document ID: US20090276394A1
- Status: Published application
- Kind: A1
- Application number: 12/114,913
- Filed: 2008-05-05
- Published: 2009-11-05
- Earliest claimed: 2008-05-05
- Assignees: International Business Machines Corporation
- Inventors: Robert J. Bestgen; Wei Hu; Shantan Kethireddy; Andrew P. Passe; Ulrich Thiemann
- Web page: https://patentgrep.com/patent/US20090276394A1

## CPC

- G06F 16/24552: {Database cache management}
- G06F 16/24542: {Plan optimisation}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A plan for executing a query in a relational database is obtained. A query for accessing data in the relational database is received. The query specifies N tables in the relational database from which data is to be retrieved. A determination is made whether a syntax of the query matches a syntax of a plan in a plan cache for executing the query. Matches are identified between generic table formats of the N tables specified in the query to generic table formats of N tables specified in the plan responsive to the syntax of the query matching the syntax of a plan in the plan cache for executing the query. The plan for executing the query is obtained based on whether the syntax of the query matches the syntax of the plan and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

## Claims

### Claim 1

A method for obtaining a plan for exeuting a query in a relational database, comprising:
receiving a query for data in the relational database, wherein the query specifies N tables in the relational database from which data is to be retrieved, where N is an integer;
determining whether syntax of the query matches syntax of a plan in a plan cache for executing the query;
responsive to the syntax of the query matching the syntax of a plan in the plan cache for executing the query, identifying matches between generic table formats of the N tables specified in the query to generic table formats of N tables specified in the plan; and
obtaining a plan for executing the query based on whether the syntax of the query matches the syntax of the plan and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

### Claim 2 (depends on 1)

The method of claim 1, wherein a generic table format describes a table at an abstract level including information regarding a physical format of the table and statistical data.

### Claim 3 (depends on 1)

The method of claim 1, wherein the step of obtaining a plan comprises using the plan in the plan cache for executing the query or generating a new plan for executing the query based on the whether the syntax of the query matches the syntax of the plan in the plan cache for executing the query and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

### Claim 4 (depends on 1)

The method of claim 1, wherein the N tables specified in the plan have hierarchy constraints, the N tables specified in the query have hierarchy constraints, and the step of identifying matches comprises:
determining whether the hierarchy constraints of the N tables specified in the plan match the hierarchy constraints of the N tables specified in the query.

### Claim 5 (depends on 4)

The method of claim 4, wherein tables having matching hierarchy constraints have matching generic table formats.

### Claim 6 (depends on 4)

The method of claim 4, further comprising determining whether the generic table formats of the N tables specified in the plan match the generic table formats of the N tables specified in the query responsive to a determination that the hierarchy constraints of the N tables specified in the plan do not match the hierarchy constraints of the N tables specified in the query.

### Claim 7 (depends on 6)

The method of claim 6, wherein the step of obtaining the plan comprises using the plan in the plan cache responsive to a determination that the hierarchy constraints of the N tables specified in the plan match the hierarchy constraints of the N tables specified in the query or a determination that the generic table formats of the N tables specified in the plan match the generic table formats of the N tables specified in the query.

### Claim 8 (depends on 6)

The method of claim 6, wherein the step of obtaining the plan comprises generating a new plan responsive to:
the syntax of the query not matching the syntax of the plan; or
a determination that the hierarchy constraints of the N tables specified in the plan do not match the hierarchy constraints of the N tables specified in the query and a determination that the generic table formats of the N tables specified in the plan do not match the generic table format of the N tables specified in the query.

### Claim 9

A system for obtaining a plan for exeuting a query in a relational database, the system comprising:
a query engine for receiving a query for accessing data in the relational database, wherein the query specifies N tables in the relational database from which data is to be retrieved, where N is an integer, determining whether a syntax of the query matches a syntax of a plan in a plan cache for executing the query, and, responsive to the syntax of the query matching the syntax of a plan in the plan cache for executing the query, identifying matches between generic table formats of the N tables specified in the query to generic table formats of N tables specified in the plan; and
a query optimizer for obtaining a plan for executing the query based on whether the syntax of the query matches the syntax of the plan and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

### Claim 10 (depends on 9)

The system of claim 9, wherein a generic table format describes a table at an abstract level including information regarding a physical format of the table and statistical data.

### Claim 11 (depends on 9)

The system of claim 9, wherein the query optimizer uses the plan in the plan cache for executing the query or generating a new plan for executing the query based on the whether the syntax of the query matches the syntax of the plan in the plan cache for executing the query and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

### Claim 12 (depends on 9)

The system of claim 9, wherein the N tables specified in the plan have hierarchy constraints, the N tables specified in the query have hierarchy constraints, and the query tool identifies matches by:
determining whether the hierarchy constraints of the N tables specified in the plan match the hierarchy constraints of the N tables specified in the query.

### Claim 13 (depends on 12)

The system of claim 12, wherein tables having matching hierarchy constraints have matching generic table formats.

### Claim 14 (depends on 12)

The system of claim 12, wherein the query tool determines whether the generic table formats of the N tables specified in the plan match the generic table formats of the N tables specified in the query responsive to a determination that the hierarchy constraints of the N tables specified in the plan do not match the hierarchy constraints of the N tables specified in the query.

### Claim 15

A computer program product for obtaining a plan for exeuting a query in a relational database, comprising a computer usable medium having a computer readable program, wherein the computer readable program, when executed on a computer, causes the computer to:
receiving a query for data in the relational database, wherein the query specifies N tables in the relational database from which data is to be retrieved, where N is an integer;
determine whether a syntax of the query matches a syntax of a plan in a plan cache for executing the query;
responsive to the syntax of the query matching the syntax of a plan in the plan cache for executing the query, identify matches between generic table formats of the N tables specified in the query to generic table formats of N tables specified in the plan; and
obtain a plan for executing the query based on whether the syntax of the query matches the syntax of the plan and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

### Claim 16 (depends on 15)

The computer program product of claim 15, wherein a generic table format describes a table at an abstract level including information regarding a physical format of the table and statistical data.

### Claim 17 (depends on 15)

The computer program product claim 15, wherein the plan for executing the query is obtained by using the plan in the plan cache for executing the query or generating a new plan for executing the query based on the whether the syntax of the query matches the syntax of the plan in the plan cache for executing the query and based on identified matches between the generic table formats of the N tables specified in the query to the generic table formats of the N tables specified in the plan.

### Claim 18 (depends on 15)

The computer program product of claim 15, wherein the N tables specified in the plan have hierarchy constraints, the N tables specified in the query have hierarchy constraints, and the step of identifying matches comprises:
determining whether the hierarchy constraints of the N tables specified in the plan match the hierarchy constraints of the N tables specified in the query.

### Claim 19 (depends on 18)

The computer program product of claim 18, wherein tables having matching hierarchy constraints have matching generic table formats.

### Claim 20 (depends on 18)

The computer program product of claim 18, further comprising determining whether the generic table formats of the N tables specified in the plan match the generic table formats of the N tables specified in the query responsive to a determination that the hierarchy constraints of the N tables specified in the plan do not match the hierarchy constraints of the N tables specified in the query.
