Performing spreadsheet-like calculations in a database system

• US 6,985,895 B2
• Filed: 06/20/2001
• Issued: 01/10/2006
• Est. Priority Date: 07/13/2000
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Witkowski, Andrew, Roth, Martin, Ozbutun, Cetin, Dorman, Gregory, Zemke, Fred
• Primary Examiner(s): Ali, Mohammad
• Assistant Examiner(s): Woo, Isaac M.
• Application Number:
  US09/886,839
• Publication Number:
  US 20020059203A1
• Time in Patent Office: 1,665 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205
• US Class Current: 707/696
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A method and system for performing spreadsheet-like operations in a database system is described herein. As explained, a database language is extended to introduce new clauses (“spreadsheet-like clauses”), for defining and executing spreadsheet-like operations on data contained within a RDBMS. According to one aspect of the invention, in response to receiving a database query that specifies an operation for manipulating data, data from a relational structure is retrieved and stored in a non-relational structure that can be addressed as a multi-dimensional array. The operation specified in the database query is the performed on then data.

Claims

1. A computer-implemented method for processing database query operations, comprising:

a database server receiving a database query including a first array reference symbolically referencing an array having one or more indexes;

wherein the first array reference addresses, as a cell of the array, a certain row and a certain column of a relational structure;

wherein each index of said one or more indexes corresponds to another column different than said certain column;

wherein a first index of said one or more indexes corresponds to a first column;

wherein the first array reference specifies a first index expression for the first index;

wherein the first index expression of the first array reference corresponds to a first value in said first column;

wherein the database query specifies an operation based on the first array reference; and

in response to receiving said database query, the database server executing the database query by performing steps including performing said operation specified in said database query.

2. The method of claim 1, wherein:

the one or more indexes include a second index;

wherein the first array reference includes a second index expression for the second index.

3. 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.

4. The method of claim 1, wherein the first index expression is a Boolean expression.

5. 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.

6. The method of claim 1, wherein the first index expression identifies a range of values.

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 4.

8. The method of claim 1, wherein the first index expression corresponds to multiple values in the first colunm.

9. The method of claim 8, wherein:

each value of said multiple values is stored in a respective row of said relational structure; and

performing said operation specified in said database query includes performing, for said each value, said operation.

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 9.

11. 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 8.

12. The method of claim 1, wherein the first index expression for the first array reference includes a looping construct that specifies a range of values.

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 12.

14. The method of claim 1, wherein:

the database query includes a second array reference to the array; and

wherein the second array reference includes a first index expression for the first index.

15. The method of claim 14, wherein the first index expression of the second array reference addresses an index value in a way that is relative to the first index expression of the first array reference.

16. 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.

17. 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 14.

18. The method of claim 1, wherein the operation is an update operation to the certain column of said certain row.

19. 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.

20. The method of claim 1, wherein the operation is an insert operation of said certain row.

21. 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.

22. The method of claim 1, wherein:

the database query specifies an operation based on the first array reference by specifying an assignment statement;

the database query includes a second array reference to the array;

the assignment statement includes an assignment expression that defines a value to assign to the cell represented by the first array reference; and

the assignment expression includes the second array reference.

23. The method of claim 22, wherein the assignment expression is on the right side of an assignment operator and the first array reference is on the left side of the assignment operator.

24. 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 23.

25. The method of claim 22, wherein:

the first index expression corresponds to multiple values in the first column;

each value of said multiple values is stored in a respective row of said relational structure; and

performing said operation specified in said database query includes performing, for said each value of said multiple values, an assignment operation to the first column of the respective row of said each value.

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 25.

27. The method of claim 22, wherein the first index expression of the second array reference addresses an index value in a way that is relative to the first index expression of the first array reference.

28. The method of claim 27, wherein the first index expression for the first array reference includes a looping construct that corresponding to a range of values in the first column.

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 28.

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 27.

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 22.

32. The method of claim 1, wherein said database query correlates the first column of the relational structure to said first index.

33. 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 32.

34. The method of claim 1, wherein said database query correlates the certain column of the relational structure to the measure of the array.

35. 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 34.

36. 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.