Techniques for handling function-defined hierarchical dimensions

• US 6,493,708 B1
• Filed: 03/02/2000
• Issued: 12/10/2002
• Est. Priority Date: 03/02/2000
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Witkowski, Andrew, Ziauddin, Mohamed
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): Camlinh, Nguyen
• Application Number:
  US09/517,361
• Time in Patent Office: 1,013 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/200
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

Techniques are described which allow function-defined hierarchies to be registered with a database server. The information provided to the server during the registration process is used by the server to determine how to roll up data that has been aggregated at one level of a function-defined hierarchy to another level of the function-defined hierarchy. Techniques are also provided to perform rollup from one level of a function-defined hierarchy to another level of the function-defined hierarchy on data stored in a materialized view. Further, techniques are provided for rewriting queries that require aggregation at one level of a function-defined hierarchy to cause them to access data from a materialized view that stores data at a different level of the function-defined hierarchy.

Claims

1. A method for handling function-defined hierarchical dimensions in a database system, the method including the steps of:

receiving, by a database server, data that specifies a plurality of levels for a function-defined hierarchical dimension;

receiving, by said database server, data that specifies hierarchical relationships between said plurality of levels;

receiving, by the database server, data that specifies functions associated with each level of said plurality of levels; and

registering said function-defined hierarchical dimension with said database server by storing metadata for said function-defined hierarchical dimension at a location accessible to said database server, wherein said metadata identifies;

said plurality of levels,

said hierarchical relationships between said plurality of levels,

said functions, and

a mapping between the plurality of levels and the functions.

2. The method of claim 1 wherein:

the step of receiving data that specifies functions associated with each level of said plurality of levels includes the step receiving data that specifies a first function that generates granules associated with a first level of said plurality of levels based on granules associated with a second level of said plurality of levels; and

said first level is coarser than said second level.

3. The method of claim 2 wherein:

the step of receiving data that specifies functions associated with each level of said plurality of levels includes the step receiving data that specifies a second function that generates granules associated with said first level of said plurality of levels based on granules associated with a third level of said plurality of granules; and

said third level is different than said second level.

4. The method of claim 2 wherein the step of receiving data that specifies a first function that generates granules associated with a first level based on granules associated with a second level includes receiving data that specifies a first function that generates granules associated with a first level based on non-base granules.

5. A method for processing a query that references a table, requests aggregation of values selected from the table, and specifies that the aggregation be performed to a particular level of a function-defined hierarchical dimension, the method comprising the steps of:

determining that said particular level is one of a plurality of levels of said function-defined hierarchical dimension that are specified in a GROUP BY clause of a materialized view;

inspecting metadata associated with said function-defined hierarchical dimension to determine whether said particular level is the finest level of said plurality of levels; and

if said particular level is the finest level of said plurality of levels, then rewriting said query to access the materialized view without performing any additional aggregation; and

if said particular level is not the finest level of said plurality of levels, then rewriting said query to access the materialized view and to roll up values from said materialized view to said particular level.

6. A method for processing queries that reference a table and request aggregation of values selected from the table, wherein the. queries specify that the aggregation be performed at particular levels of a function-defined hierarchical dimension, the method comprising the steps of:

creating a materialized view that includes a column for granules associated with a first level of said function-defined hierarchical dimension;

in response to queries that reference said table and request aggregation to be performed at a second level of said function-defined hierarchy, rewriting said queries to access said materialized view and to aggregate to said second level by applying a function to values in said column for granules associated with said first level, wherein the first level is finer than said second level.

7. The method of claim 6 wherein the step of creating said materialized view includes creating said materialized view without any column for granules associated with said second level of said function-defined hierarchical dimension.

8. The method of claim 6 further comprising the step of inspecting metadata associated with said function-defined hierarchical dimension to determine that said function may be used to derived granules associated with said second level of said function-defined hierarchical dimension.

9. The method of claim 6 wherein the step of applying a function includes applying a plurality of nested functions, wherein an innermost function of said plurality of nested functions receives granules from said first level as input, and wherein an outermost function of said plurality of nested functions produces granules at said second level as output.

10. A computer-readable medium carrying one or more sequences of instructions for handling function-defined hierarchical dimensions in a database system, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving, by a database server, data that specifies a plurality of levels for a function-defined hierarchical dimension;

receiving, by said database server, data that specifies hierarchical relationships between said plurality of levels;

receiving, by the database server, data that specifies functions associated with each level of said plurality of levels; and

registering said function-defined hierarchical dimension with said database server by storing metadata for said function-defined hierarchical dimension at a location accessible to said database server, wherein said metadata identifies;

said plurality of levels,

said hierarchical relationships between said plurality of levels,

said functions, and

a mapping between the plurality of levels and the functions.

11. The computer-readable medium of claim 10 wherein:

the step of receiving data that specifies functions associated with each level of said plurality of levels includes the step receiving data that specifies a first function that generates granules associated with a first level of said plurality of levels based on granules associated with a second level of said plurality of levels; and

said first level is coarser than said second level.

12. The computer-readable medium of claim 11 wherein:

the step of receiving data that specifies functions associated with each level of said plurality of levels includes the step receiving data that specifies a second function that generates granules associated with said first level of said plurality of levels based on granules associated with a third level of said plurality of granules; and

said third level is different than said second level.

13. The computer-readable medium of claim 11 wherein the step of receiving data that specifies a first function that generates granules associated with a first level based on granules associated with a second level includes receiving data that specifies a first function that generates granules associated with a first level based on non-base granules.

14. A computer-readable medium bearing instructions for processing a query that references a table, requests aggregation of values selected from the table, and specifies that the aggregation be performed to a particular level of a function-defined hierarchical dimension, the computer-readable medium bearing instructions for performing the steps of:

determining that said particular level is one of a plurality of levels of said function-defined hierarchical dimension that are specified in a GROUP BY clause of a materialized view;

inspecting metadata associated with said function-defined hierarchical dimension to determine whether said particular level is the finest level of said plurality of levels; and

if said particular level is the finest level of said plurality of levels, then rewriting said query to access the materialized view without performing any additional aggregation; and

if said particular level is not the finest level of said plurality of levels, then rewriting said query to access the materialized view and to roll up values from said materialized view to said particular level.

15. A computer-readable medium bearing instructions for processing queries that reference a table and request aggregation of values selected from the table, wherein the queries specify that the aggregation be performed at particular levels of a function-defined hierarchical dimension, the computer-readable medium bearing instructions for performing the steps of:

creating a materialized view that includes a column for granules associated with a first level of said function-defined hierarchical dimension;

in response to queries that reference said table and request aggregation to be performed at a second level of said function-defined hierarchy, rewriting said queries to access said materialized view and to aggregate to said second level by applying a function to values in said column for granules associated with said first level, wherein the first level is finer than said second level.

16. The computer-readable medium of claim 15 wherein the step of creating said materialized view includes creating said materialized view without any column for granules associated with said second level of said function-defined hierarchical dimension.

17. The computer-readable medium of claim 15 further bearing instructions to perform the step of inspecting metadata associated with said function-defined hierarchical dimension to determine that said function may be used to derived granules associated with said second level of said function-defined hierarchical dimension.

18. The computer-readable medium of claim 15 wherein the step of applying a function includes applying a plurality of nested functions, wherein an innermost function of said plurality of nested functions receives granules from said first level as input, and wherein an outermost function of said plurality of nested functions produces granules at said second level as output.