Database query code generation and optimization based on the cost of alternate access methods

• US 4,829,427 A
• Filed: 05/25/1984
• Issued: 05/09/1989
• Est. Priority Date: 05/25/1984
• Status: Expired due to Term

• Current Assignee: Data General Corp. (Dell Technologies Inc.)
• Original Assignee: Data General Corp. (Dell Technologies Inc.)
• Inventors: Green, Nancy L.
• Primary Examiner(s): NOT, DEFINED
• Assistant Examiner(s): NOT, DEFINED
• Application Number:
  US06/614,416
• Time in Patent Office: 1,810 Days
• Field of Search: 364/200 MS File, 364/900 MS File, 364/300
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

An optimizer-code generator for use in a data base system. The optimizer-code generator employs a component called a scan analyzer for performing implementation-dependent analysis and providing implementation-dependent query code. The optimizer-code generator receives a query in logical tree form. It first optimizes the logical tree. In so doing, it provides information from the logical tree to the scan analyzer, which specifies what methods are available for accessing information required for the query and what each of the available methods costs. The optimizer-code generator uses the cost information in its optimization of the logical tree and specifies the access methods to be used in the logical tree. The code generator then uses the logical tree to generate query code. In so doing, it provides the specifications of the access methods to the scan analyzer, which returns query code for the access method. The code generator then incorporates the query code for the access method in the query code which it generates from the logical tree.

Claims

1. In a digital computer system employing a data base system for accessing stored data, means for transforming logical query data representing a query to be performed by said data base system into query code executable by said data base system comprising:

(1) implementation-dependent analysis means for receiving certain said logical query data representing a said query and responding thereto by providing a cost data specifying the cost of an operation in said data base system specified by certain said logical query data and implementation-dependent portions of said query code required to perform said specified operation; and

(2) implementation-independent transformation means for modifying said logical query data in response to said cost data and producing said executable query code containing said implementation-dependent portions from said analysis means.

2. In the logical query data transformation means of claim 1, and wherein:

said data base system includes a plurality of means for accessing said stored data;

said implementation-dependent analysis means comprises a plurality of scan analyzing means, each one of said scan analyzing means corresponding to certain of said plurality of means for accessing said stored data;

each one of said scan analyzing means provides said cost data for said means for accessing said stored data corresponding thereto; and

each one of said scan analyzing means provides said implementation-dependent portions of said query code specifying said means for accessing said stored data corresponding thereto.

3. In the logical query data transformation means of claim 2, and wherein:

each one of said scan analyzing means further provides preliminary information specifying a certain said means for accessing said stored data to said implementation-independent transformation means together with said cost data;

said implementation-independent transformation means incorporates said preliminary information into said modified logical query data and returns said incorporated preliminary information to said scan analyzing means which provided said preliminary information; and

each said scan analyzing means receiving said preliminary information produces said implementation-dependent portions of said executable query code therefrom.

4. In a digital computer system employing a data base system for accessing stored data by means of a plurality of access methods, improved query code generation means for generating query code executable by said data base system from logical query data representing a query wherein the improvement comprises:

(1) preliminary information in said logical query data specifying certain of said plurality of access methods as are required for said query; and

(2) a scan analyzer for receiving said preliminary information and providing therefor said executable query code for said access methods as are required for said query.

5. In the improved query code generation means of claim 4, and wherein:

said improved query code generation means further comprises a plurality of said scan analyzers, each one of said scan analyzers corresponding to a certain one of said plurality of access methods and receiving said preliminary information for said corresponding access method and providing said executable code for said certain one of said access methods.

6. In the improved query code generation means of claim 5, and wherein:

said data base system further includes pluralities of access execution means, each one of said pluralities corresponding to one of said plurality of access methods;

said preliminary informations specifies one of said plurality of access execution means corresponding to a given one of said plurality of access methods; and

said scan analyzer corresponding to said given one of said plurality of access methods provides said executable code specifying said one access execution means.

7. In a digital computer system employing a data base system for accessing stored data by means of a plurality of access methods, improved means for optimizing logical query data corresponding to queries comprising:

(1) a scan analyzer for determining the cost of any certain of said plurality of access methods and returning cost data specifying said cost; and

(2) means responsive to said cost data for altering said logical query data for a given said query to employ said access methods having the lowest said cost for the given said query.

8. In the improved means for optimizing logical query data of claim 7, and wherein:

said improved means for optimizing logical query data further comprises a plurality of said scan analyzers, each one of said scan analyzers corresponding to one of said plurality of access methods and returning said cost data for said corresponding access method.

9. In the improved means for optimizing logical query data of claim 8, and wherein:

said data base system further includes pluralities of access execution means, each one of said pluralities corresponding to one of said plurality of access methods; and

said scan analyzer corresponding to one certain of said plurality of access methods determines said cost data for said plurality of access execution means corresponding to said one certain of said plurality of access methods.

10. In a data base system implemented in a digital computer system, the method of transforming logical query data representing a query to be performed by said data base system into logical query data representing a more efficient form of said query comprising the steps of:

(1) providing said logical query data to a scan analyzer for determining the cost of employing certain ones of a plurality of access methods;

(2) comparing the relative cost of said certain access methods; and

(3) selecting the most cost-efficient one of said certain access methods and altering said logical query data to employ said selectled one.

11. In a data base system implemented in a digital computer system, the method of transforming logical query data into query code executable by said data base system comprising the steps of:

(1) providing preliminary information concerning one of a plurality of access methods from said logical query data to a scan analyzer corresponding to said access method;

(2) receiving portions of said executable query code specific to said access method specified in said preliminary information from said scan analyzer; and

(3) generating, from said logical query data, said executable query code incorporating said portions of said executable query code specific to said access method.