Index merge ordering

• US 9,734,177 B2
• Filed: 10/02/2014
• Issued: 08/15/2017
• Est. Priority Date: 06/12/2014
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: McKinley, Thomas O., Cain, Michael W., Bestgen, Robert J.
• Primary Examiner(s): Mofiz, Apu
• Assistant Examiner(s): BIBBEE, JARED M
• Application Number:
  US14/505,275
• Publication Number:
  US 20150363470A1
• Time in Patent Office: 1,048 Days
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/24575 using context
• CPC Class Codes: G06F 16/24578 using ranking
• CPC Class Codes: G06F 16/9535 Search customisation based on user profiles and personalisation

Abstract

Method to order query results, by processing a received query at least in part using a composite key index, wherein the composite key index comprises at least two key columns, wherein the at least two key columns comprise at least one pre-ordering column and an ordering column, wherein the query specifies ordering criteria to order a result set based on values of the ordering column, wherein the processing comprises receiving, from the composite key index, an ordering column value for each unique pre-ordering column value in the composite key index, identifying a first ordering column value as being ordered first according to the ordering criteria, and returning the first ordering column value as being responsive to the query.

Claims

1. A method to order query results, comprising:

processing a received query at least in part using a composite key index, wherein the composite key index comprises at least two key columns, wherein the at least two key columns comprise at least one pre-ordering column and an ordering column, wherein the query specifies ordering criteria to order a result set based on values of the ordering column, by;

receiving, from the composite key index, an ordering column value for each unique pre-ordering column value in the composite key index;

identifying a first ordering column value as being ordered first according to the ordering criteria;

returning the first ordering column value as being responsive to the query;

replacing, in a list of the ordering column values received from the composite key index, the first ordering column value with a second ordering column value received from the composite key index, the second ordering column value sharing the unique pre-ordering column value of the first ordering column value;

identifying a next ordering column value as being ordered first among the ordering column values in the list according to the ordering criteria; and

returning the next ordering column value as being responsive to the query.

2. The method of claim 1, further comprising:

prior to processing the query;

determining a cardinality of the unique pre-ordering column values; and

determining that a ratio of the cardinality to a count of rows in a database table upon which the composite key index is built is lower than a predefined threshold.

3. The method of claim 1, wherein a first set of ordering column values are returned, wherein a count of the ordering column values in the first set comprises a predefined number of ordering column values, wherein the first set of ordering column values is ordered according to the ordering criteria.

4. The method of claim 3, wherein a second set of ordering column values is returned responsive to user input specifying to return additional ordering column values.

5. The method of claim 1, wherein ordering column values are received for each unique pre-ordering column value matching a selection criteria specified in the query.

6. The method of claim 1, wherein the query is processed according to a query execution plan specifying to process the query at least in part using the composite key index.

7. A method, comprising:

processing a received query at least in part using a composite key index, wherein the composite key index comprises at least two key columns, wherein the at least two key columns comprise at least one pre-ordering column and an ordering column, wherein the query specifies ordering criteria to order a result set based on values of the ordering column, by;

receiving, from the composite key index, an ordering column value for each unique pre-ordering column value in the composite key index;

identifying, in a list of the ordering column values received from the composite key index, a first ordering column value as being ordered first according to the ordering criteria;

returning the first ordering column value as being responsive to the query;

replacing the first ordering column value in the list with a second ordering column value received from the composite key index, the second ordering column value sharing the unique pre-ordering column value of the first ordering column value;

identifying a next ordering column value as being ordered first among the ordering column values in the list according to the ordering criteria; and

returning the next ordering column value as being responsive to the query.

8. The method of claim 7, further comprising:

prior to processing the query;

determining a cardinality of the unique pre-ordering column values; and

determining that a ratio of the cardinality to a count of rows in a database table upon which the composite key index is built is lower than a predefined threshold.

9. The method of claim 7, wherein a first set of ordering column values are returned, wherein a count of the ordering column values in the first set comprises a predefined number of ordering column values, wherein the first set of ordering column values is ordered according to the ordering criteria.

10. The method of claim 9, wherein a second set of ordering column values is returned responsive to input specifying to return additional ordering column values.

11. The method of claim 7, wherein ordering column values are received for each unique pre-ordering column value matching a selection criteria specified in the query.

12. The method of claim 7, wherein the query is processed according to a query execution plan specifying to process the query at least in part using the composite key index.