Operators for accessing hierarchical data in a relational system

• US 7,028,037 B1
• Filed: 09/27/2002
• Issued: 04/11/2006
• Est. Priority Date: 09/28/2001
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Sedlar, Eric, Chandrasekar, Sivasankaran, Murthy, Ravi, Agarwal, Nipun, Ge, Fei
• Primary Examiner(s): Gaffin, Jeffrey
• Assistant Examiner(s): Veillard, Jacques
• Application Number:
  US10/260,138
• Time in Patent Office: 1,292 Days
• Field of Search: 707 1- 4, 707100-103 R, 707/104.1, 707/200, 709/201, 709/217, 709/218, 709/226, 709/229
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2448 for particular applications; for extensibility, e.g. user defined types
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• 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/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Techniques and systems are provided for efficiently managing hierarchical relational data stored in object-relational database system by extending the syntax of the database language supported by the database system (e.g. SQL) to incorporate several new “hierarchical” operators. These operators are evaluated based on the hierarchical relationship defined for hierarchical relational data. The operators maybe incorporated in SQL queries along with relational operators.

Claims

1. A method of accessing information managed by a relational database system, the method comprising the steps of:

receiving a database language statement that specifies selection criteria for selecting rows managed by the database system;

wherein the rows are associated with nodes in a hierarchy;

wherein the statement includes an operator that specifies that results of said statement are based, at least in part, on respective positions, within the hierarchy, of the nodes associated with the rows;

during execution of said statement, evaluating said operator using information managed by the relational database system about said hierarchy; and

wherein said database language defines said operator as one that evaluates to an output value based on respective positions of nodes within a given hierarchy.

2. The method of claim 1 wherein the step of evaluating the operator includes determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy.

3. The method of claim 2 wherein the step of determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy includes the step of determining whether the row is associated with a node that is under the particular node in the hierarchy.

4. The method of claim 3 wherein the step of determining whether the row is associated with a node that is under the particular node in the hierarchy includes the steps of:

if conditions in said statement are relatively more restrictive, then traversing up the hierarchy from said node; and

if conditions in said statement are relatively less restrictive, then traversing down the hierarchy from the particular node.

5. The method of claim 2 wherein the step of determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy includes the step of determining whether the row is associated with the particular node in the hierarchy.

6. The method of claim 2 wherein the step of determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy includes the step of determining whether the row is associated with a node that is within a specified number of levels under the particular node in the hierarchy.

7. The method of claim 1 wherein the step of evaluating said operator includes determining a path, within the hierarchy, to a particular node.

8. The method of claim 1 wherein the step of evaluating said operator includes determining a depth, within the hierarchy, of a node associated with a particular row.

9. The method of claim 1 wherein the step of evaluating said operator using information managed by the relational database system about said hierarchy includes evaluating said operator using a relational table that includes rows that hold data indicating parent-child relationships between nodes of said hierarchy.

10. The method of claim 9 wherein the statement includes a plurality of hierarchical operators, the method further comprising using information obtained in a particular scan of the relational table to evaluate each of said plurality of hierarchical operators.

11. The method of claim 10 wherein the plurality of hierarchical operators include at least two operators from a set that includes UNDER_PATH, PATH, DEPTH, and EQUALS_PATH.

12. A computer-readable medium carrying instructions for accessing information managed by a relational database system, the instructions comprising instructions for performing the steps of:

receiving a database language statement that specifies selection criteria for selecting rows managed by the database system;

wherein the rows are associated with nodes in a hierarchy;

wherein the statement includes an operator that specifies that results of said statement are based, at least in part, on respective positions, within the hierarchy, of the nodes associated with the rows;

during execution of said statement, evaluating said operator using information managed by the relational database system about said hierarchy; and

wherein said database language defines said operator as one that evaluates to an output value based on respective positions of nodes within a given hierarchy.

13. The computer-readable medium of claim 12 wherein the step of evaluating the operator includes determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy.

14. The computer-readable medium of claim 13 wherein the step of determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy includes the step of determining whether the row is associated with a node that is under the particular node in the hierarchy.

15. The computer-readable medium of claim 13 wherein the step of determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy includes the step of determining whether the row is associated with the particular node in the hierarchy.

16. The computer-readable medium of claim 13 wherein the step of determining whether a row is associated with a node, within the hierarchy, that has a particular position relative to a particular node in the hierarchy includes the step of determining whether the row is associated with a node that is within a specified number of levels under the particular node in the hierarchy.

17. The method of claim 12 wherein the step of evaluating said operator includes determining a path, within the hierarchy, to a particular node.

18. The computer-readable medium of claim 12 wherein the step of evaluating said operator includes determining a depth, within the hierarchy, of a node associated with a particular row.

19. The computer-readable medium of claim 12 wherein the step of evaluating said operator using information managed by the relational database system about said hierarchy includes evaluating said operator using a relational table that includes rows that hold data indicating parent-child relationships between nodes of said hierarchy.

20. The computer-readable medium of claim 19 wherein the statement includes a plurality of hierarchical operators, the computer-readable medium further comprising instructions for using information obtained in a particular scan of the relational table to evaluate each of said plurality of hierarchical operators.

21. The computer-readable medium of claim 20 wherein the plurality of hierarchical operators include at least two operators from a set that includes UNDER_PATH, PATH, DEPTH, and EQUALS_PATH.