Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-02-09
2001-07-10
Kulik, Paul V. (Department: 2177)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06260037
ABSTRACT:
FIELD OF THE INVENTION
The present invention relates to the data processing field, and more particularly, relates to a method and computer program product for implementing skip key processing for database grouping queries involving aggregate operations by use of one or more indices in a computer system.
DESCRIPTION OF THE RELATED ART
When executing a database grouping query using an index, every entry within the file is examined to determine how its contents affect the group result. There are certain conditions for which the first entry encountered within each group will contain the results for the entire group. For those cases, processing every entry in every group does not make sense, and takes more time than is necessary for the query to complete. Take the following SQL select statement as an example:
Select A, min(x)
from somefile
group by A
Assuming also an index over somefile defines both A and x as ascending key fields.
If the query is performed in such a manner to move forward through the file examining the entries, it is known that the very first entry in each group will contain the minimum (MIN) value for x for that group.
The example above describes a simple, non-join query, but the same holds true for nested loop join queries with the aggregate function in the join primary and even join secondaries.
Select F1.A, min(F2.x)
from F1, F2
group by F1.A
In this case, we could have two different indexes, one with an ascending key over F1.A and another with an ascending key over F2.x. Once again, if the query is executed such that we are moving forward through the file, the first entry in F2 that we position to will contain the min value for x within each group.
Performance for such queries would be greatly improved if we did not have to process every entry within each group, since we know the first entry will contain the group result.
SUMMARY OF THE INVENTION
A principal object of the present invention is to provide an improved method and computer program product for implementing skip key processing for database grouping queries involving aggregate operations by using one or more indices in a computer system. Other important objects of the present invention are to provide such method and computer program product for skip key processing for database grouping queries involving aggregate operations by using one or more indices, substantially without negative effects and that overcome some disadvantages of prior art arrangements.
In brief, a method and computer program product for implementing skip key processing for database grouping queries involving aggregate operations using one or more indices in a computer system. First checking for a selected entry in a current group in a primary file is performed. Responsive to finding the selected entry in the current group, the set of records of the current group is ordered to provide an aggregate value in an aggregate field of the current group. The checking for an excluded value, such as NULL in the aggregate field of the current group is performed. Responsive to not finding the excluded value in the aggregate field, a current entry is saved as a result for the group. Then checking for more groups in the primary file is performed.
In accordance with features of the invention, responsive to finding more groups in the primary file, a prefix of the index is obtained to position to a next group. When the selected entry in the next group is not found, then checking for more groups in the primary file is performed.
REFERENCES:
patent: 5598559 (1997-01-01), Chaudhuri
Bestgen Robert Joseph
Egan Randy Lynn
Giordano Thomas Paul
Heimer Scott Joseph
Ramler Carol Ledermann
International Business Machines - Corporation
Kulik Paul V.
Pennington Joan
LandOfFree
Method and computer program product for implementing skip... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method and computer program product for implementing skip..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and computer program product for implementing skip... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2494681