Efficient mechanism for managing hierarchical relationships in a relational database system

Abstract

A method and apparatus for managing hierarchical relationships in a relational database system is provided. An “orderkey” data type, which is native within a relational database system, is disclosed. The orderkey type is designed to contain values that represent the position of an entity relative to the positions of other entities within a hierarchy. Such values represent hierarchical relationships between those entities. Values that are of the orderkey type have properties that allow hierarchy-oriented functions to be performed in an especially efficient manner. Database functions, which operate on and/or produce values that are of the orderkey type, are also disclosed. Such functions can be placed within SQL statements that a database server executes, for example. In response to executing SQL statements that contain such functions, the database server performs hierarchy-oriented operations in a highly efficient manner.

1 . A computer-implemented method comprising:
 receiving, as input to a previously defined function within a database, a value, of a particular data type, that indicates a respective position within a hierarchy;   wherein the value indicates a complete hierarchical lineage of the respective position within the hierarchy; and   the function returning a result value based on both:   a predefined hierarchical relationship with respect to the respective position within the hierarchy.   
     
     
         2 . The method of  claim 1 , wherein the predefined hierarchical relationship is selected from the group consisting of: first child, left sibling, right sibling, and parent. 
     
     
         3 . The method of  claim 1 , wherein the result value is of the particular data type. 
     
     
         4 . The method of  claim 1 , wherein the respective position is a first respective position; and wherein the result value indicates a complete hierarchical lineage of a second respective position within the hierarchy, the second respective position having the predefined hierarchical relationship with respect to the first respective position. 
     
     
         5 . The method of  claim 1 , wherein the value is a first value; wherein the respective position is a first respective position, and wherein the method further comprises:
 receiving, as input to the previously defined function within the database, a second value, of the particular data type, that indicates a second respective position within the hierarchy;   wherein the second value indicates a complete hierarchical lineage of the second respective position within the hierarchy; and   wherein the function returning the result value based on the predefined hierarchical relationship with respect to the first respective position in the hierarchy is based on the first respective position having the predefined hierarchical relationship with respect to the second respective position.   
     
     
         6 . The method of  claim 1 , wherein the function has a name and the name of the function indicates the predefined hierarchical relationship. 
     
     
         7 . The method of  claim 1 , wherein the value is represented by a first sequence of bytes; wherein the result value is represented by a second sequence of bytes; wherein the predefined hierarchical relationship is parent; wherein the second sequence of bytes is byte-wise less than the first sequence of bytes; and wherein the second sequence of bytes is a byte-wise prefix of the first sequence of bytes. 
     
     
         8 . One or more non-transitory computer-readable media storing one or more programs for execution by one or more processors, the one or more programs having instructions configured for:
 receiving, as input to a previously defined function within a database, a value, of a particular data type, that indicates a respective position within a hierarchy;   wherein the value indicates a complete hierarchical lineage of the respective position within the hierarchy; and   the function returning a result value based on a predefined hierarchical relationship with respect to the respective position within the hierarchy.   
     
     
         9 . The one or more non-transitory computer-readable media of  claim 8 , wherein the predefined hierarchical relationship is selected from the group consisting of: first child, left sibling, right sibling, and parent. 
     
     
         10 . The one or more non-transitory computer-readable media of  claim 8 , wherein the result value is of the particular data type. 
     
     
         11 . The one or more non-transitory computer-readable media of  claim 8 , wherein the respective position is a first respective position; and wherein the result value indicates a complete hierarchical lineage of a second respective position within the hierarchy, the second respective position having the predefined hierarchical relationship with respect to the first respective position. 
     
     
         12 . The one or more non-transitory computer-readable media of  claim 8 , wherein the value is a first value; wherein the respective position is a first respective position, and wherein the instructions are further configured for:
 receiving, as input to the previously defined function within the database, a second value, of the particular data type, that indicates a second respective position within the hierarchy;   wherein the second value indicates a complete hierarchical lineage of the second respective position within the hierarchy; and   wherein the function returning the result value based on the predefined hierarchical relationship with respect to the first respective position in the hierarchy is based on the first respective position having the predefined hierarchical relationship with respect to the second respective position.   
     
     
         13 . The one or more non-transitory computer-readable media of  claim 8 , wherein the function has a name and the name of the function indicates the predefined hierarchical relationship. 
     
     
         14 . The one or more non-transitory computer-readable media of  claim 8 , wherein the value is represented by a first sequence of bytes; wherein the result value is represented by a second sequence of bytes; wherein the predefined hierarchical relationship is right sibling;
 and wherein the second sequence of bytes is a byte-wise greater than the first sequence of bytes.   
     
     
         15 . A computing system, comprising:
 one or more processors;   storage media;   one or more programs stored in the storage media and configured for execution by the one or more processors, the one or more programs having instructions configured for:   receiving, as input to a previously defined function within a database, a value, of a particular data type, that indicates a respective position within a hierarchy;   wherein the value indicates a complete hierarchical lineage of the respective position within the hierarchy; and   the function returning a result value based on a predefined hierarchical relationship with respect to the respective position within the hierarchy.   
     
     
         16 . The computing system of  claim 15 , wherein the predefined hierarchical relationship is selected from the group consisting of: first child, left sibling, right sibling, and parent. 
     
     
         17 . The computing system of  claim 15 , wherein the result value is of the particular data type. 
     
     
         18 . The computing system of  claim 15 , wherein the respective position is a first respective position; and wherein the result value indicates a complete hierarchical lineage of a second respective position within the hierarchy, the second respective position having the predefined hierarchical relationship with respect to the first respective position. 
     
     
         19 . The computing system of  claim 15 , wherein the value is a first value; wherein the respective position is a first respective position, and wherein the instructions are further configured for:
 receiving, as input to the previously defined function within the database, a second value, of the particular data type, that indicates a second respective position within the hierarchy;   wherein the second value indicates a complete hierarchical lineage of the second respective position within the hierarchy;   wherein the first value is a first sequence of bytes;   wherein the second value is a second sequence of bytes;   wherein the result value is a third sequence of bytes;   wherein the predefined hierarchical relationship is left sibling; and   wherein the third sequence of bytes is byte-wise greater than the first sequence of bytes and byte-wise less than the second sequence of bytes.   
     
     
         20 . The computing system of  claim 15 , wherein the value is represented by a first sequence of bytes; wherein the result value is represented by a second sequence of bytes;
 wherein the predefined hierarchical relationship is first child; wherein the second sequence of bytes is byte-wise greater than the first sequence of bytes; and wherein the first sequence of bytes is a byte-wise prefix of the second sequence of bytes.