System for optimizing query processing in a relational database

• US 5,091,852 A
• Filed: 01/25/1989
• Issued: 02/25/1992
• Est. Priority Date: 01/29/1988
• Status: Expired due to Term

• Current Assignee: Hitachi, Ltd.
• Original Assignee: Hitachi, Ltd.
• Inventors: Tsuchida, Masashi, Ohmachi, Kazuhiko
• Primary Examiner(s): Lee, Thomas C.
• Assistant Examiner(s): Lim, Krisna
• Application Number:
  US07/302,307
• Time in Patent Office: 1,126 Days
• Field of Search: 364/200 MS File, 364/900 MS File
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A method for processing a database for analyzing a query to the database from a user to determine an internal processing procedure therefor and executing it comprises the steps of preparing single or a plurality of internal processing procedures based on analysis result of the query including a variable inputted in a preprocess step and statistical information of a database system, selecting an optimum one of the internal processing procedures based on a value substituted for the variable of the query in a query execution stage and the statistical information of the database system, and executing the query in accordance with the selected internal processing procedure.

Claims

1. A method for processing a database comprising the steps of:

analyzing a query including a variable in a central processing unit;

generating, in the central processing unit, at least one internal processing procedure for executing the query based on a result of the step of analyzing a query and statistical information of a system including the database, the statistical information including

(a) a characteristic of the database, and

(b) a characteristic of the system including a central processing unit for issuing the query, and at least one of

(i) frequency information of a column constituting a relation in the database, and

(ii) information relating to a range value derived by dividing column value frequency relating to a column included in a retrieval condition expressed in the query, the column value frequency and the number of column values appearing in that range;

selecting, in the central processing unit, one processing procedure from said at least one internal processing procedure based on a value substituted for the variable included in the query and the statistical information of the system; and

executing, in the central processing unit, the query in accordance with the selected internal processing procedure.

2. A method for processing a database according to claim 1 wherein the statistical information of the system includes frequency information of a column value relating to a column included in a retrieval condition expressed in the query.

3. A method for processing a database comprising the steps of:

preparing, in a central processing unit, at least one internal processing procedure which is validated, in execution of an input query, by a constant value substituted for a variable included in a retrieval condition expression of the input query, in addition to statistical information of a system including a database, the statistical information including

(a) a characteristic of the database, and

(b) a characteristic of the system including a central processing unit for issuing the query, and at least one of

(i) frequency information of a column constituting a relation in the database, and

(ii) information relating to a range value derived by dividing column value frequency relating to a column included in a retrieval condition expressed in the query, the column value frequency and the number of column values appearing in that range;

maintaining, in a memory of the central processing unit, column value frequency information relating to a column appearing in the retrieval condition expression of the query;

selecting, in the central processing unit, one internal processing procedure from the at least one internal processing procedure based on the column value frequency information in accordance with the constant value substituted for the variable in execution of the input query; and

executing, in the central processing unit, the selected internal processing procedure developed into an execution module.

4. A method for processing a database comprising the steps of:

preparing, in a central processing unit, at least one internal processing procedure for a query including a variable;

selecting, in the central processing unit, one internal processing procedure from the at least one internal processing procedure based on a constant substituted for the variable in an execution of the query and statistical information of a database system including the database, the statistical information including

(a) a characteristic of the database, and

(b) a characteristic of the system including a central processing unit for issuing the query, and at least one of

(i) frequency information of a column constituting a relation in the database, and

(ii) information relating to a range value derived by dividing column value frequency relating to a column included in a retrieval condition expressed in the query, the column value frequency and the number of column values appearing in that range; and

executing, in the central processing unit, the query in accordance with the selected internal processing procedure.

5. An apparatus for processing a database comprising:

means for analyzing;

in a central processing unit, a query including a variable;

means for generating, in the central processing unit, at least one internal processing procedure based on the analysis result by the analyzing means and statistical information of a database system including the database, the statistical information including

(a) a characteristic of the database, and

(b) a characteristic of the system including a central processing unit for issuing the query, and at least one of

(i) frequency information of a column constituting a relation in the database, and

(ii) information relating to a range value derived by dividing column value frequency relating to a column included in a retrieval condition expressed in the query, the column value frequency and the number of column values appearing in that range;

means for selecting, in the central processing unit, one internal processing procedure from at least one internal processing procedure based on a value substituted for the variable included in the query and the statistical information of the database system; and

means for executing the query in accordance with the internal processing procedure selected by the selecting means.

6. An apparatus for processing a database according to claim 5, wherein the statistical information of the database system includes column value relating to a column included in a retrieval condition expressed in the query expression.

7. A method for processing a database comprising the steps of:

analyzing, in a central processing unit, a query including a variable;

generating, in the central processing unit, at least one internal processing procedure for executing the query based on the analysis result and statistical information of the database system including the database, the statistical information including

(a) a characteristic of the database, and

(b) a characteristic of the system including a central processing unit for issuing the query, and at least one of

(i) frequency information of a column constituting a relation in the database, and

(ii) information relating to a range value derived by dividing column value frequency relating to a column included in a retrieval condition expressed in the query, the column value frequency and the number of column values appearing in that range;

selecting, in the central processing unit, one internal processing procedure from said at least one internal processing procedure based on a designation by a user; and

executing, in the central processing unit, the query in accordance with the selected internal processing procedure.