Efficient processing of relational joins of multidimensional data

• US 20050033741A1
• Filed: 09/16/2004
• Published: 02/10/2005
• Est. Priority Date: 05/07/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Welton, Caleb, Hopeman, Albert A. IV, Dombroski, Matthew, Soylemez, Ekrem
• Granted Patent: US 8,612,421 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Predicate abduction involves identifying, from a database statement, predicates on attributes of dimensional data, and applying those predicates to corresponding measure data so that only measure values are fetched and returned for dimension values that satisfy the predicate. In contrast to processing joins of relational data, the subset of the measure data is determined without needing to join a fact table with a dimension table and, therefore, without processing every fact record to determine which measure values are associated with records that satisfy the condition. Buffer reuse involves storing in buffers the “domains” of queries being executed on multidimensional data. In response to detecting that execution of a database statement requires multiple iterations of extracting the same multidimensional data, the data is extracted during the first iteration and stored in a buffer so that, for each subsequent iteration, the data in the buffer is accessed rather than again extracting the data.

Claims

1. A method comprising the computer-implemented steps of:

receiving a database statement that involves multidimensional data objects;

determining that a plurality of operations need to be performed to execute the database statement, the operations including

a first operation to retrieve measure data from the multidimensional data objects, wherein the measure data is dimensioned by a particular dimension, and

a second operation that specifies a condition on a dimension attribute of the particular dimension;

during performance of the first operation, returning a subset of the measure data from the multidimensional data objects by only returning measure data associated with dimension values, for the attribute, that satisfy the condition on the attribute.

2. The method of claim 1, wherein the step of determining that a plurality of operations need to be performed comprises:

identifying an equi-join operation on (a) the measure data that is dimensioned by the particular dimension and (b) dimensional data associated with the particular dimension; and

identifying the condition on the dimension attribute of the particular dimension.

3. The method of claim 1, further comprising the computer-implemented steps of:

determining that the measure data is dimensioned by more than one particular dimension;

determining that the database statement includes a condition on a respective dimension attribute of more than one of the particular dimensions; and

wherein the step of returning comprises returning a subset of the measure data from the multidimensional data objects by only returning measure data associated with dimension values, for each of the respective dimension attributes, that satisfy the condition on the respective dimension attribute.

4. The method of claim 1, further comprising the computer-implemented steps of:

determining that the database statement includes conditions on more than one dimension attribute of the particular dimension; and

wherein the step of returning comprises returning a subset of the measure data from the multidimensional data objects by only returning measure data associated with dimension values, for each of the respective more than one dimension attributes, that satisfy the condition on the respective more than one dimension attributes.

5. The method of claim 1,

wherein the first operation to retrieve measure data is an operation to retrieve first measure data that is associated with a first measure and second measure data that is associated with a second measure that is different from the first measure;

wherein the second operation specifies a condition on a dimension attribute in association with the first measure; and

wherein the step of returning a subset of the measure data includes returning only first and second measure data that is associated with dimension values, for the attribute, that satisfy the condition on the attribute of the particular dimension.

6. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

7. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

8. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

9. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

10. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

11. A method comprising the computer-implemented steps of:

receiving a database statement that involves multidimensional data objects;

determining that the statement specifies performance of a particular operation on particular multidimensional data from the multidimensional data objects more than once;

in response to a first specification of the particular operation, performing the particular operation;

storing the results of the particular operation in a buffer; and

in response to a second specification of the particular operation, retrieving the results of the particular operation from the buffer without again performing the particular operation.

12. The method of claim 11,

wherein the statement specifies (a) at least one operation on a first set of data from the multidimensional data objects and (b) at least one other operation on a second set of data, with a different dimensional domain than the first set of data, from the same set of multidimensional data objects;

wherein the first set of data and the second set of data are different;

the method further comprising the computer-implemented steps of;

retrieving the first set of data from the multidimensional data objects, for executing the at least one operation;

storing the first set of data in a first buffer;

while concurrently executing the at least one operation and the at least one other operation, retrieving the second set of data from the multidimensional data objects, for executing the at least one other operation, and

storing the second set of data in a second buffer that is different from the first buffer; and

completing execution of the at least one operation based on the first set of data in the first buffer.

13. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 11.

14. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 12.

15. A method comprising:

receiving a query that requires extraction of multidimensional data from multidimensional data objects;

in response to detecting that execution of the query requires multiple iterations of extracting the same multidimensional data from the multidimensional objects, performing the steps of;

extracting the multidimensional data during a first iteration of the multiple iterations;

storing the multidimensional data in a buffer separate from the multidimensional objects; and

for each subsequent iteration of the multiple iterations, accessing the multidimensional data in the buffer rather than extracting the multidimensional data from the multidimensional objects.

16. The method of claim 15, wherein:

state information affects which multidimensional data is extracted from the multidimensional objects;

state information changes between the first iteration and one of the subsequent iterations; and

during the subsequent iterations, accessing the multidimensional data from the buffer rather than extracting the multidimensional data from the multidimensional data objects, such that the change in the state information does not cause the multidimensional data that is accessed during the subsequent iterations to differ from the multidimensional data used during the first iteration.

17. The method of claim 15, wherein:

the query is received in a session;

the state information is part of a session state of the session; and

the session state of the session changes between the first iteration and one of the subsequent iterations.

18. The method of claim 17, wherein the session state changes in response to a concurrently executing query within the session.

19. The method of claim 17, wherein:

the multiple iterations are associated with a first subquery of the query; and

the session state changes in response to concurrent execution of a second subquery of the query.

20. The method of claim 17,

wherein the session state specifies a domain associated with said multidimensional data objects;

and

wherein the domain constrains one or more dimensions of the multidimensional data objects.

21. The method of claim 15, wherein:

the query includes a table function that requires extraction of multidimensional data from the multidimensional data objects; and

the step of detecting that execution of the query requires multiple iterations of extracting the same multidimensional data from the multidimensional data objects includes detecting that execution of the query requires multiple executions of the table function.

22. The method of claim 15, wherein

the step of receiving a query is performed by a relational database server;

and

the step of extracting the multidimensional data during a first iteration is performed by a multidimensional database server in response to commands from the relational database server.

23. The method of claim 22, wherein the steps of storing the multidimensional data in a buffer and accessing the multidimensional data in the buffer are performed by the relational database server.

24. The method of claim 22, wherein the steps of storing the multidimensional data in a buffer and accessing the multidimensional data in the buffer are performed by the multidimensional database server.

25. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 15.

26. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 16.

27. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 17.

28. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 18.

29. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 19.

30. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 20.

31. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 21.