Method for executing star queries

• US 5,848,408 A
• Filed: 02/28/1997
• Issued: 12/08/1998
• Est. Priority Date: 02/28/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Ozbutun, Cetin, Waddington, William H., Jakobsson, Hakan
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): WALLACE, MICHAEL J
• Application Number:
  US08/808,621
• Time in Patent Office: 648 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method and apparatus for processing star queries is provided. According to the method, a star query is transformed by adding to the star query subqueries that are not in the query. The subqueries are generated based on join predicates and constraints on dimension tables that are contained in the original query. The subqueries are executed, and the values returned by the subqueries are used to access one or more bitmap indexes built on columns of the fact table. The bitmaps retrieved for the values returned by each subquery are merged to create one subquery bitmap per subquery. An AND operation is performed on the subquery bitmaps, and the resulting bitmap is used to determine which data to retrieve from the fact table.

Claims

1. A method for processing a query that requires retrieval of data from a fact table and specifies constraints on one or more dimension tables associated with said fact table, the method comprising the steps of:

transforming the query to produce a transformed query that includes at least one subquery that is not in the query;

determining a result set for the at least one subquery, said result set including a set of key values;

accessing a bitmap index built on a column of the fact table to retrieve a set of bitmaps associated with said set of key values;

merging said set of bitmaps to generate a subquery bitmap; and

determining which data to retrieve from said fact table in response to said query based on said subquery bitmap.

2. The method of claim 1 wherein:

said at least one subquery includes a plurality of subqueries;

the step of determining a result set for the at least one subquery is performed for each subquery of said plurality of subqueries;

a plurality of subquery bitmaps are generated by performing the step of merging said set of bitmaps to generate a subquery bitmap for each subquery of said plurality of subqueries;

the method further includes the step of performing a logical AND operation to combine said plurality of subquery bitmaps into a result bitmap; and

the step of determining which data to retrieve from said fact table in response to said query based on said subquery bitmap includes the step of determining which data to retrieve from said fact table in response to said query based on said result bitmap.

3. The method of claim 1 wherein the step of transforming the query includes the steps of adding to a WHERE clause of the query additional subqueries.

4. The method of claim 3 wherein the step of transforming the query further includes constructing the additional subqueries from a set of join predicates and a set of dimension table constraints contained in the query.

5. The method of claim 1 wherein:

the query includes a constraint on a dimension k;

the step of transforming the query includes the step of generating an IN subquery in the form;

fact.keyk IN (select dimk.key from dimk where <

dimk constraints>

)

where dimk is a dimension table, dimk.key is a column of dimk, and fact.keyk is a column of the fact table that a join predicate correlates with dimk.key.

6. The method of claim 5 wherein:

the query includes constraints on a plurality of dimensions;

the step of generating an IN subquery includes generating an IN subquery for each dimension of said plurality of dimensions.

7. The method of claim 1 further comprising the steps of:

identifying, in the query, one or more join predicates that associate a particular column in the fact table and a particular column in a dimension table;

determining whether (a) the particular column in the dimension table is unique and (b) the query contains no references to columns in that particular dimension table other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery;

if (a) the particular column in the dimension table is unique and (b) the query contains no references to columns in that particular dimension table other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery, then processing the query without executing a join operation between data from the fact table and data from the particular dimension table; and

if either the particular column in the dimension table is not unique or the query contains a reference to columns in that particular dimension table other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery, then executing a join operation between data from said fact table and data from the particular dimension table.

8. A method for processing a query that requires retrieval of data from a first data repository and specifies constraints on one or more second data repositories that the query associates with said first data repository, the method comprising the steps of:

transforming the query to produce a transformed query that includes at least one subquery that is not in the query, wherein the at least one subquery selects records in the one or more second data repositories based on the constraints specified in the query;

executing said at least one subquery to determine a first set of record identifiers that identify records in said one or more second data repositories that satisfy said at least one subquery;

using said first set of record identifiers as key values to access an index associated with the first data repository to generate a second set of record identifiers that identify records in said first data repository; and

determining which data to retrieve from said first data repository in response to said query based on said second set of record identifiers.

9. The method of claim 8 wherein the step of using said first set of record identifiers as key values to access an index associated with the first data repository to generate a second set of record identifiers comprises the steps of:

retrieving a set of bitmaps from said index associated with the first data repository; and

merging said set of bitmaps to generate a subquery bitmap.

10. The method of claim 9 wherein:

said at least one subquery includes a plurality of subqueries;

the step executing said at least one subquery is performed for each subquery of said plurality of subqueries;

a plurality of subquery bitmaps are generated by performing the step of merging said set of bitmaps to generate a subquery bitmap for each subquery of said plurality of subqueries;

the method further includes the step of performing a logical AND operation to combine said plurality of subquery bitmaps into a result bitmap; and

the step of determining which data to retrieve from said first data repository in response to said query based on said second set of record identifiers includes the step of determining which data to retrieve from said first data repository in response to said query based on said result bitmap.

11. The method of claim 8 wherein the step of transforming the query includes the steps of adding additional subqueries to a WHERE clause of the query.

12. The method of claim 11 wherein the step of transforming the query further includes constructing the additional subqueries from a set of join predicates and a set of second data repository constraints contained in the query.

13. The method of claim 8 wherein:

the first data repository is a fact table;

the second data repositories are dimension tables of the fact table;

the query includes a constraint on a dimension k;

the step of transforming the query includes the step of generating an IN subquery in the form;

fact.keyk IN (select dimk.key from dimk where <

dimk constraints>

)

where dimk is a second data repository, dimk.key is a column of dimk, and fact.keyk is a column of the first data repository that a join predicate correlates with dimk.key.

14. The method of claim 13 wherein:

the query includes constraints on a plurality of dimensions;

the step of generating an IN subquery includes generating an IN subquery for each dimension of said plurality of dimensions.

15. The method of claim 8 further comprising the steps of:

identifying, in the query, one or more join predicates that associate a particular portion of the first data repository and a particular portion of a particular second data repository;

determining whether (a) values in the particular portion of the second data repository are unique and (b) the query contains no references to portions of the particular second data repository other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery;

if (a) values in the particular portion of the second data repository are unique and (b) the query contains no references to portions of the particular second data repository other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery, then processing the query without executing a join operation between data from the first data repository and data from the particular second data repository; and

if either values in the particular portion of the second data repository are not unique or the query contains references to portions of the particular second data repository other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery, then executing a join operation between data from said first data repository and data from the particular second data repository.

16. A computer readable medium having stored thereon sequences of instructions for processing a query that requires retrieval of data from a first data repository and specifies constraints on one or more second data repositories that the query associates with said first data repository, the sequences of instructions comprising sequences of instructions for performing the steps of:

transforming the query to produce a transformed query that includes at least one subquery that is not in the query, wherein the at least one subquery selects records in the one or more second data repositories based on the constraints specified in the query;

executing said at least one subquery to determine a first set of record identifiers that identify records in said one or more second data repositories satisfy said at least one subquery;

using said first set of record identifiers as key values to access an index associated with the first data repository to generate a second set of record identifiers that identify records in said first data repository; and

determining which data to retrieve from said first data repository in response to said query based on said second set of record identifiers.

17. The computer readable medium of claim 16 wherein the step of using said first set of record identifiers as key values to access an index associated with the first data repository to generate a second set of record identifiers comprises the steps of:

retrieving a set of bitmaps from said index associated with the first data repository; and

merging said set of bitmaps to generate a subquery bitmap.

18. The computer readable medium of claim 17 wherein:

said at least one subquery includes a plurality of subqueries;

the step executing said at least one subquery is performed for each subquery of said plurality of subqueries;

a plurality of subquery bitmaps are generated by performing the step of merging said set of bitmaps to generate a subquery bitmap for each subquery of said plurality of subqueries;

the sequences of instructions further include sequences of instructions for performing the step of performing a logical AND operation to combine said plurality of subquery bitmaps into a result bitmap; and

the step of determining which data to retrieve from said first data repository in response to said query based on said second set of record identifiers includes the step of determining which data to retrieve from said first data repository in response to said query based on said result bitmap.

19. The computer readable medium of claim 16 wherein:

the first data repository is a fact table;

the second data repositories are dimension tables of the fact table;

the query includes a constraint on a dimension k;

the step of transforming the query includes the step of generating an IN subquery in the form;

fact.keyk IN (select dimk.key from dimk where <

dimk constraints>

)

where dimk is a second data repository, dimk.key is a column of dimk, and fact.keyk is a column of the first data repository that a join predicate correlates with dimk.key.

20. The computer readable medium of claim 16 further comprising sequences of instructions for performing the steps of:

identifying, in the query, one or more join predicates that associate a particular portion of the first data repository and a particular portion of a particular second data repository;

determining whether (a) values in the particular portion of the second data repository are unique and (b) the query contains no references to portions of the particular second data repository other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery;

if (a) values in the particular portion of the second data repository are unique and (b) the query contains no references to portions of the particular second data repository other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery, then processing the query without executing a join operation between data from the first data repository and data from the particular second data repository; and

if either values in the particular portion of the second data repository are not unique or the query contains references to portions of the particular second data repository other than the one or more join predicates and any constraints that are used in a WHERE-clause of the subquery, then executing a join operation between data from said first data repository and data from the particular second data repository.