Method and system for implementing an enhanced database
A database is created from a set of data by identifying the patterns in the data set, storing the patterns in a memory device, and generating a representation of the database structure using pointers to the stored patterns. The use of pointers to store the patterns can greatly reduce the amount of memory or other space required to store the data set by replacing duplicate patterns with additional pointers, which are normally considerably smaller in size. In addition, the use of pointers may allow for more rapid searching, sorting, and other operations on the database. The representation of the database may use pointers in a tree structure to identify nodes and reduce searching and other operations. A tree structure representation of a table may use a pointer to a pattern for the base leaf node (e.g., nodes that do not branch) while using pointer pairs to represent branching nodes.
This application claims the benefit of U.S. Provisional Application No. 60/615,793, filed on Oct. 4, 2004, and U.S. Provisional Application No. 60/671,172, filed on Apr. 12, 2005, each of which is expressly incorporated herein in its entirety by reference thereto.
This application relates to U.S. Provisional Application No. 60/615,793 with title “Method and System for Implementing an Enhanced Database” filed on Oct. 4, 2004 and PCT Application WO 02/063498 with title “Method of Querying a Structure of Compressed Data”, which are expressly incorporated in their entirety by reference herein.
COPYRIGHT NOTICEA portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or patent disclosure as it appears in the Patent and Trademark Office, patent file or records, but otherwise reserves all copyright rights whatsoever.
FIELD OF THE INVENTIONThe present invention relates to a method and system for implementing an enhanced database and enhanced database operations.
BACKGROUND INFORMATIONConventional database management systems incur greater operating overhead using more resources as they grow larger. Businesses rely on fast database performance in the execution of normal commercial operations and, in particular, would benefit from gains made in database efficiency and performance. The present invention is a significantly enhanced implementation of a database as well as enhanced database operations that result in greater efficiency, particularly with larger databases.
BRIEF DESCRIPTION OF THE DRAWINGS
According to one embodiment of the present invention, a database is created from a set of data (i.e., a “data set”) by identifying the patterns in the data set, storing the patterns in memory (or on a storage device), and generating a representation of the database structure using pointers to the stored patterns. The use of pointers to store the patterns can greatly reduce the amount of memory or other space required to store the data set by replacing duplicate patterns with additional pointers, which are normally considerably smaller in size. In addition, the use of pointers may allow for more rapid searching, sorting, and other operations on the database because they can be performed using the patterns to represent many records (tuples) in a single operation. The present invention incorporates such a database as described in PCT Application WO 02/063498, METHOD OF QUERYING A STRUCTURE OF COMPRESSED DATA, the entirety of which is expressly incorporated by reference herein.
The terms tuple, row, and record are used interchangeably throughout this specification. The example embodiment illustrated below refers to a tuple identifying the relational nature of the resulting database according to this embodiment of the present invention. The present invention may also be implemented using a hierarchical or other database model in alternative embodiments of the present invention. The following examples and embodiments often describe the present invention using a data set for a table in a database. However, the embodiments described can also be used across several tables rather than for a single table only and the descriptions should not be considered as limiting the present invention to operating only on a single table of a database.
In addition to the leaf nodes (the fields) in a table, a tree structure representation of the table also includes branch nodes (a combination of leaf nodes and/or other branch nodes) as previously discussed according to one embodiment of the present invention. In a binary tree structure according to this embodiment, each branch node contains patterns based on two subordinate nodes.
Identifying Patterns in a Data Set:
A pattern may refer to one value in a field of a table as well as to a combination of values from multiple fields of the table. For example, a pattern for a leaf node is a value for a field represented by the leaf node. If the leaf node represents the field vehicle manufacturer, a pattern for the leaf node may be, for example, “Mfg A”. A pattern may also represent the value for a branch node in a tree. A branch node is a node with subordinate or child nodes (two subordinate or child nodes for each branch in a binary tree). A pattern for a branch node represents the values for at least two leaf nodes in a binary tree. For example, a branch node may have two subordinate or child leaf nodes representing the fields vehicle manufacturer and year of manufacture. A pattern for this branch node may include, for example, {Mfg A, 1996}. The pattern in this example represents one pairing of values from the subordinate/child nodes. In the case where a branch node has at least one subordinate branch node, the pattern for the parent branch node will contain a grouping of at least 3 field values for the at least 3 leaf nodes at some point subordinate to the parent branch node. In summary, a pattern may be one value in the set of values for a field in a table (a pattern at a leaf node of a tree) or a pattern may be a grouping of values from the set of values for at least two fields in a table (a pattern at a branch node of a tree).
According to one embodiment of the present invention, the identification of data patterns in a data set for a table may be an important element in storing the patterns in memory or on a storage device and using pointers in the table to reference the data patterns. The use of pointers in the tuples (rows or records) of the table may allow greater efficiency through faster database operation and decreased storage required to implement a database according to the present invention. A pointer to a stored pattern replaces each data pattern that is encountered during the building of the example table in a database according to this embodiment. The use of a pointer to reference a pattern may occur for any type of pattern encountered. For example, a pointer may be used for any of the patterns at one of the four different types of nodes that may be encountered in a binary tree representation of a tuple of data in a database table as discussed below. According to one embodiment of the present invention, all the data patterns in a table are stored in pattern sets in memory or on a storage device and the representation of each tuple is implemented by using pointers to the stored patterns.
The first node 202 represents one particular type of node: a branching node with a subordinate leaf node and another subordinate branching node—i.e., Branching node with Leaf and Other nodes (BLO). This branching node 202 contains a pointer to a BLO pattern. The BLO pattern itself being a pair of pointers with the first pointer to the subordinate leaf node 204 pattern (i.e., a field value) and the second pointer to the subordinate branch node 205 pattern, in this case another BLO branching node. Any pattern for a branching node and for the root node will contain a pair of pointers to other patterns because of the binary tree structure used according to this embodiment of the present invention. This second BLO branching node 205 branches to another leaf node 209 and to a different type of branching node 208 that branches to two subordinate leaf nodes—i.e., Branching node with Leaf Pair (BLP). This BLP branching node 208 contains a BLP pattern and branches to two leaf nodes 214, 215. A BLP pattern is a pair of pointers with the first pointing to a first leaf node 214 pattern and the second pointing to a second leaf node 215 pattern. Unlike the root node and the branching nodes, leaf nodes contain pointers to field values instead of to other pointers—the leaf nodes correspond to the fields in the table—according to this embodiment. On the other side of the binary tree 200 from the root node 201, the second node 203 is yet another type of branching node with two subordinate branching nodes—i.e., Branching node with Other node and Other node (BOO). This BOO branching node 203 contains a BOO branching pattern and branches to a BLP branching node 206 and a BLO branching node 207. A BOO branching pattern is a pair of pointers with the first pointing to first branching node 206 pattern and the second pointing to a second branching node 207 pattern. The first branching node 206 is a BLP node branching to two subordinate leaf nodes 210, 211. The second branching node 207 is a BLO node branching to a subordinate leaf node 212 and a subordinate BLP branching node 213. This BLP branching node 213 itself branches to two subordinate leaf nodes 216, 217. Though the nodes in the tree structure, particularly the branching nodes, have been distinguished, all nodes other than leaf nodes in the tree structure will contain pointers to patterns consisting of two pointers, one each for a pattern from the immediate two subordinate nodes, according to this embodiment of the present invention. Leaf nodes represent the fields of the table and will contain pointers to actual field values that are stored as part of the pattern set for the field/leaf node according to this embodiment.
One process according to the example embodiment of the present invention is to replace multiple occurrences of a data pattern in a table with multiple pointers to a single instance of the data pattern. According to this process, the data patterns for a field or node are stored in a pattern set in memory or on a storage device and pointers are used in the nodes of the table to reference the stored patterns in the pattern set. This reduces the amount of memory and/or disk space required to implement a table or database resulting in improved efficiency. In other words, a repeated pattern in a table is replaced by multiple pointers to a single instance of the pattern stored in memory or on a storage device. For this reason, a determination whether a data pattern is new needs to be made each time a data pattern is identified. For example, when a pattern is encountered for a table, a search operation may be conducted to determine if the pattern has already been encountered—i.e., an instance of the pattern has already been recorded in the pattern set for the field or node of the table. The time required to conduct this search operation is proportional to number of already encountered patterns—in particular, it is proportional to log(n) where n represents the number of different patterns already encountered for the table of the database. As a result of this proportionally increasing execution cost for the search operation, a reduction in the number of search operations conducted may greatly improve the efficiency in the building of the example database.
One method to reduce the number of search operations conducted is related to the type of data pattern encountered. According to one embodiment of the present invention, the type of data pattern is first determined when a data pattern is encountered (e.g., leaf, BLP, BLO, and BOO). For a leaf pattern (i.e., a field value), no reduction in the number of search operations may be feasible. Therefore, the encountered leaf pattern may need to be compared with the set of already encountered patterns to determine if the leaf pattern is new. If the pattern is new, it is added to the set of encountered patterns and a pointer may be used to replace the pattern in the representation of the tuple of the table. A frequency value of 1 may also be assigned to the pattern according to one embodiment of the present invention. If the pattern already exists, the frequency counter may be updated (i.e., incremented) and a pointer may be used to replace the pattern in the representation of the tuple for the table. The remaining patterns (e.g., BLP, BLO, and BOO-branch node patterns) represent a pair of patterns that can occur in a binary tree representation of a tuple of a table. For these patterns based on a pair of subordinate patterns, the pattern may be new regardless of whether its subordinate patterns are new or already exist because the combination of the subordinate patterns may be new. However, it is a certainty that if either of the subordinate patterns is new, the pattern itself will be new. For this reason, if a new first subordinate pattern is encountered, the second subordinate pattern does not need to be compared in order to determine if the pattern itself is new—it is a certainty that the pattern is new. This situation allows for an efficiency enhancement to be made in the pattern identification process by reducing the number of search operations for patterns based on subordinate pattern pairs (e.g., BLP, BLO, and BOO patterns—branch node patterns) according to one embodiment of the present invention. This efficiency results in only searching half the subordinate patterns where the first subordinate pattern encountered is new.
Tree Design:
According to one embodiment of the present invention, a table of data can be represented using a tree-structure and in particular a binary tree-structure. According to this embodiment, a single tree contains the value for the data in a tuple or row of a table and a forest or conglomeration of trees (with a similar structure) represent the data in the entire table. In other words, the forest or set of trees represents the table while a single tree represents a tuple or row of the table. A tree-structure does not contain the field values and patterns found in the tree but only outlines the placement and organization of the nodes and where the field values are located (i.e., which leaf nodes represent which fields). In a binary tree-structure, all nodes are either leaf nodes (terminal nodes) with no subordinate or child nodes or they are branch or root nodes with exactly two subordinate/child nodes. In other words, in a binary tree structure, all nodes either do not branch (i.e., have no subordinate nodes) or branch to exactly two subordinate nodes. Nodes that do not branch are termed leaf nodes and may be thought of as leaves in the binary tree. Nodes that branch to two subordinate nodes may be termed branch nodes and may be thought of as the branching points in the binary tree. The first branching node in a binary tree may be termed the root node and may be thought of as the trunk or root of the binary tree. As previously discussed, the fields of a table are represented by leaf nodes in the binary tree. The patterns at the leaf nodes are the values of the field. Branch nodes represent a pairing or grouping of the values from all the subordinate leaf nodes.
The ordering and position of a leaf node representing a field of the table greatly affects the efficiency of the tree design and table organization of the example database according to one embodiment of the present invention. For example, the performance of a search operation is determined by the number of unique patterns at each node (i.e., position) in the tree-structure. A node or position may be represented using an (x,y) coordinate system where x is the number of steps from the left to right across the level (numbered from 0) and y is the height above the lowest level of the tree (also numbered from 0). In other words, the x-value may be viewed as the horizontal axis value and the y-value may be viewed as the vertical axis value for the node in the tree. Using this (x,y) coordinate system (i.e., Cartesian coordinates), a formula for determining the minimum and a formula for determining the maximum number of unique patterns for a node may be implemented according to one embodiment of the present invention. The equation for the minimum number of unique patterns, in one embodiment of the present invention, is:
npMIN(x,y)=max(NP(a,b)), where (a,b)εchildren(x,y)
npMIN(x,y) is a function determining the Number of Patterns Minimum (npMIN) at a particular branch node in a tree-leaf nodes will have the set of unique values (patterns) for the field and no calculations need to be made for subordinate/child nodes as must be done for branch nodes. The branch node is identified using the (x,y) position identification as discussed earlier. The function is solved by determining the Number of Patterns (NP) for each of the immediate subordinate/child nodes of the branch node, the subordinate node identified using the (a,b) convention for the position to differentiate it from the (x,y) convention used for the branch node. The “max” function in the equation indicates using the larger of the two values (for a binary tree) for the number of patterns for the two subordinate nodes of the branch node. According to this equation, the minimum number of unique patterns for a branch node is equal to the larger of the two values for the number of unique patterns for the subordinate or child nodes of the branch node. For example, if a branch node has two subordinate leaf nodes, one subordinate leaf node for manufacturers with 10 unique patterns {Mfg 1, Mfg 2, Mfg 3, Mfg 4, Mfg 5, Mfg 6, Mfg 7, Mfg 8, Mfg 9, Mfg 10} and another subordinate leaf node for automobile colors with 5 unique patterns {Red, Green, Blue, Black, Grey}, the minimum number of unique patterns for the branch node is 10—the larger of the two values for the unique number of patterns for the two subordinate leaf nodes. Described another way, the minimum number of unique patterns at a branch node must include at least one instance of each pattern/value in the set of values for the subordinate/child nodes. Therefore, a subordinate leaf node with 10 unique values in its set of values requires a minimum of at least 10 different patterns occurring in the parent branch node according to this embodiment of the present invention. Because the patterns/values from the subordinate nodes can fully correlate (e.g., Mfg 1 is always Red), the minimum unique patterns at a branch node equal the greater of the two values for the unique number of patterns for each subordinate/child node of the branch node.
In addition to the equation for the minimum number of patterns at a branch node, an equation for the maximum number of patterns at a branch node may also be implemented. One example of an equation for the maximum number of patterns is, according to one embodiment of the present invention, as follows:
npMAX(x,y)=min(ΠNP(a,b),R), where (a,b)εchildren(x,y)
npMAX(x,y) is the function to determine the Number of Patterns Maximum (npMAX) at a particular branch node in a tree. The branch node is identified using the (x,y) position identification as discussed earlier. As with the minimum function (npMIN) discussed above, the solution to the npMAX function requires a determination of the Number of Patterns (NP) for each of the subordinate or child nodes of the branch node. Unlike the minimum function (npMIN), a product of the number of unique patterns for each of the subordinate or child nodes is taken and compared to the number of tuples or rows in the table with the lesser of the two values (i.e., the product and the number of tuples) used as the final value. Using the same example used above for the minimum function (npMIN), if a branch node has two subordinate leaf nodes for the fields automobile manufacturer and color, the product of the two values for the unique number of patterns for each subordinate node equals 50 (i.e., 10 manufacturers*5 colors). This value, 50, is compared to the total number of tuples for the table—e.g., 40 tuples—and the lesser value (in this example 40) is used as the maximum number of unique patterns that can occur for a branch pattern according to this embodiment of the present invention.
Because a binary tree structure is used to represent the tuples of the table, the minimum and maximum number of unique pattern functions (npMIN and npMAX) can be further refined by replacing the (a,b) position convention for the subordinate or child nodes with the same (x,y) convention used for the branch node resulting in the following redefined functions:
npMIN(x,y)=max(NP(2x,y−1), NP(2x+1,y−1))
npMAX(x,y)=min(NP(2x,y−1)*NP(2x+1,y−1), R)
The minimum pattern function (npMIN) is equal to the maximum or greater value of the number of unique patterns from either of the two subordinate nodes. The maximum pattern function (npMAX) is equal to the minimum or lesser value of the product of the number of unique patterns for the two subordinate nodes or the total number of tuples or rows for the table (R).
When the actual number of unique patterns at a branch node is closer to the minimum value (npMIN) rather than the maximum value (npMAX), query and other operations on the data may be more efficient thereby improving the performance of the example database according to one embodiment of the present invention. One method to structure the tree in order to reduce the number of unique patterns at a branch node is to group closely correlated fields according to one embodiment of the present invention. Correlation refers to the direct relationship between the values for two different fields in a table of the database (or, for example, the patterns for two different leaf nodes in the tree). Using the previous example, the patterns/values in an automobile manufacturer field may have a correlation to the patterns in the automobile color field. If the pattern “Mfg 1” in the manufacturers field is always “Red”, a pattern in the color field, and “Mfg 2” is always “Green” and so on, a strong or complete correlation between the patterns in the fields exists as each automobile manufacturer would be correlated to one automobile color. At the other extreme, the patterns or values for both fields would be completely uncorrelated if each automobile manufacturer produced automobiles in every one of the automobile colors. The direct correlation between the fields may range from no correlation (i.e., completely uncorrelated) as discussed above to a strong or complete correlation also discussed above with any degree of correlation in between. Using the above example, if the automobile manufacturer field is completely correlated to the automobile color field and their representative leaf nodes are both subordinate to the same branch node, there would only be 10 branch patterns for the 10 manufacturers with each manufacturer correlating to a single color value. If the automobile manufacturer field is completely uncorrelated to the automobile color field and their representative leaf nodes are both subordinate to the same branch node, there would be 50 branch patterns because each of the 10 manufacturers may produce cars in all 5 of the automobile colors. In a previously discussed example, the 40 tuples in the table indicate that there is little correlation between these fields as the value of 40 more closely resembles the completely uncorrelated value of 50 rather than the completely correlated value of 10.
In another embodiment of the present invention, a sub-binary tree (a subset of the binary tree beginning at a branch node instead of the root node) that has n subordinate leaf nodes (leaves) with the patterns at each leaf node having similar frequencies or alternatively the leaf nodes having a similar number of patterns could be replaced with a single n-ary node with the same number of leaves that also retains a count of the frequencies of the sub-leaves. For example using
As previously stated, a stronger correlation in the patterns of two subordinate nodes result in a parent branch node having closer to the minimum number of patterns rather than to the maximum number of patterns. By reducing the actual patterns at the branch node, the number of patterns is reduced in all further parent branch nodes. In other words, the stronger the correlation between two sets of patterns for subordinate nodes, the closer their combination size will be to the minimum possible value and because the parent node pattern size will be smaller, its parents can be smaller in pattern size and so on. The reduction in the number of patterns in the parent nodes results in a decreased amount of memory and storage usage as well as expedited execution of a query process on the data according to this embodiment of the present invention. The way the tree is designed, therefore, becomes very important in optimizing the database.
In selecting a tree design or layout, correlation may be used to achieve more optimal outcomes. In one embodiment with a tree with p leaf sets (leaves), there is a possible p! orderings of the fields with 2p possible layouts or designs for the tree. As the number of fields increase or the number of tuples or rows (i.e., the set of values for the field) increase, it becomes increasingly impractical to determine the correlation between the fields. Therefore, finding another solution to achieve similar results where correlation values are not available can provide a near optimal solution without the considerable overhead determining correlation involves. Ideally, this other solution will require additional time or resources in a linearly progressing manner to the quantity of data and not in a greater than linearly progressing manner or else this solution will become too cumbersome for very large data sets. One such solution is the use of a cardinality value in place of correlation according to one embodiment of the present invention.
The cardinality of a field is the number of unique values (patterns) that the field contains in the entire data set for the field—in other words all the unique values that are used for the field. For example, in a data set of size R (the number of tuples or rows for the table), a unique key field would have a cardinality of R (all the values for the field are unique). The maximum number of values for a field (or patterns for a leaf node) is R because you can not have more values than tuples or rows in the table. Therefore, in this example, the minimum number of values for each parent branch node above the unique key must be R, which will always be greater than or equal to the number of patterns for any other leaf node. Because the number of patterns for the parent branch node is a minimum of R, if the leaf node for the field appears 10 levels below the root node there will be at least 10R patterns necessary to represent the table (10 levels * R as the minimum per level) in this example. However, if the leaf node for the field appears immediately below the root node there will only be a minimum of 2R patterns necessary to represent the table (2 levels * R). Therefore, placing the leaf node representing fields with high cardinality closer to the root node reduces the number of patterns necessary to represent the table and results in lower memory and storage usage as well as more efficient query execution in a manner similar to using field correlation. Regardless of the placement of the leaf nodes for the fields, there will always be a minimum of R patterns in the root node to represent all the tuples in a table. The maximum number of patterns in any field is R, the number of tuples (a field can only have one pattern in any given tuple, so even if a key is unique there can only be R of them in the entire database). The minimum number of patterns is always 1 because the field must always have a value (even if the value is NULL) in every tuple. According to one embodiment of the database, there will be R root nodes for a table because it allows the original order of the tuples in the table to be recorded. Although it is theoretically possible to have two completely identical tuples in a table, the occurrence is rare in practice and using a complicated mechanism to remove the duplicated pattern in the root node while retaining the original ordering of the tuples in the table may be less efficient. In an alternative embodiment of the present invention, it is possible to remove duplication of patterns at the root node.
The above example illustrates how using cardinality can achieve similar results as correlation according to one embodiment of the present invention. The principle difference between the two is in the amount of time (resources) required to determine cardinality as opposed to correlation. Using a simple function to calculate the time taken to determine the cardinality can be represented as O(n*p) while the time taken to determine correlation would take O(n*p2) where n represents the number of tuples in the table and p represents the number of fields in each tuple of the table.
The third step 503 in the process 500 involves the grouping of fields (or clustering of nodes) and generating sub-trees based on these groupings. In order to accomplish this task, a function to determine the sub-tree structure may be required. For example, the function subtree(list,h) is defined below and takes as its parameters a list of the fields for the table—list—and a minimum height for any node in the sub-tree—h. The list of fields can be in any order (though the grouping process 503 provides a sorted list which is used as part of the process) and the minimum height is the height in levels (further described below) of the branch node or sub-tree. The subtree function may return a list of equal length as the list provided; the returned list specifying the positions for each field and node. In one embodiment of the present invention, the subtree function may be specified as follows:
Applying the subtree function to a list of fields {a,b,c,d,e} of length 5 (i.e., containing 5 fields) and with a minimum height of 0 (h=0) results in a width=2int(log
Using the coordinates to place the fields in the array results in: field “a” 605 being placed in the fourth row 604 and first column 611 corresponding to coordinates (0,0); field “b” 606 being placed in the fourth row 604 and second column 612 corresponding to coordinates (1,0); field “c” 607 being placed in the third row 603 and second column 612 corresponding to coordinates (1,1); field “d” 608 being placed in the third row 603 and third column 613 corresponding to coordinates (2,1); and field “e” 609 being placed in the third row 603 and fourth column 614 corresponding to coordinates (3,1).
The process shown in
In order to improve the optimal nature of the tree design, the tree design process can be further refined to take into account the potential for large deviations in the cardinality between the fields.
Null Value Representation:
As part of the process of generating the example database, patterns in the data of a database table are identified and stored according to one embodiment of the present invention. In order to accomplish this pattern identification task, for any given field in a table, a set of all values that are present in the field is generated. Each value is included only once in this set of values for the field. For example, if an integer field of a table contains the values 1, 3, 5, 3, 7, 1, 5, 6 in various tuples of the table, a set of values {1, 3, 5, 7, 6} is generated for the field. The values 1, 3, and 5 are only included once in this set of values even though they each occur twice in the original data set of the database table.
In addition to storing values for the field, the data in a table of the database may also be examined and represented in a binary tree structure. According to one embodiment of the present invention, each tuple in the table is represented by a binary tree. The binary trees may then be manipulated in a manner where the leaves and branches of the binary tree are reorganized in order to facilitate a comparison of the binary trees for the table. Patterns may be found as follows: in the field value shown at a leaf of the binary tree—a leaf pattern; at a branch in the binary tree with a left and right leaf patterns—a pair of leaf patterns; at a branch in the binary tree with a subordinate leaf pattern and another pattern (branch pattern); and at a branch in the binary tree with two subordinate non-leaf patterns (branch pattern). Leaf patterns may be found at each leaf in the binary tree (i.e., for each field). Branch patterns may be identified at each branching point indicating at least one of a left branch and a right branch value, which may also include its own subordinate patterns. Each pattern is stored in memory and is generally referred to by reference using a pointer. In other words, the pointer contains an address where the pattern is stored. Multiple occurrences of a single pattern result in the pattern being stored once with additional pointers to the same stored pattern. In this manner, a pattern that occurs 15 times in the table is only stored once with at most 15 pointers to the pattern value. As a result of this process, the data in the table is reduced to a set of stored patterns and the trees for each tuple of the table are reduced with pointers replacing leaf and branch patterns whenever possible except at the root level (a root level structure needs to be maintained to maintain an accurate ordering of the tuples). Depending on the degree of pattern repetition, this process can significantly reduce the amount of space (in memory or on disk) required to represent the data.
For most types of data patterns for the fields of a tuple, the pattern is encoded as a pointer to a location where the actual pattern is stored. For example, a simple pattern “blue” for a field “color” may be stored as a pointer in the “color” field (or associated leaf in a tree) for the tuple, the pointer containing an memory address where the actual pattern “blue” is stored. Alternatively, it is also possible to directly encode a leaf pattern (i.e., a field value) rather than an address in the pointer. For example, if there are 5 values in a set of values for a field {0, 1, 3, 5, and 8}, the field value could be encoded into the pointer by storing the binary value of the field value instead of an address in the pointer. In this example, the pointer may contain the binary sequence “0000” for value (or pattern) 0, “0001” for value (or pattern) 1, “0011” for value (or pattern) 3, “0101” for value (or pattern) 5, and “1000” for value (or pattern) 8. The database system can, using some meta-data indicating the contents for a field, determine whether the pointer contains a field value or a memory address based on the pointer contents and can handle the different types of pointers separately. For example, in the case of an address, the database will map the field value to the memory address of the pattern. In the case of a field value, the database will map the field value to the contents of the pointer. In other words, the pointer is either interpreted as pointing to a memory address or it is interpreted as a value. Incorporating a mix in these two types of pointers may reduce the overall size of the database.
In a 32-bit computer system where the pointer contains an address referencing an integer data pattern (e.g., a leaf pattern or field value), a pointer requires 32-bits of information for the pointer (i.e., for the address) and an additional 32-bits to represent the pattern (e.g., the integer) referenced by the pointer. Using the above example where the field values 0, 1, 3, 5, and 8 are the leaf patterns being referenced, a total of 5 pointers (5*32-bits) addressing the 5 patterns (5*32-bits) requires a total of 320-bits to implement (i.e., 5 pointers*32-bits+5 values*32-bits=320-bits). However, encoding the pattern (in this example the field value) into the pointer itself adds an additional requirement for a 32-bit value to identify the data type of the field (e.g., pointer contains value not address) while combining the pointer and pattern (i.e., value) into the pointer resulting in only 192-bits to implement (i.e., 5 pointers*32-bits+32-bits for the field type=192-bits). Incorporating the pattern into the pointer itself reduces the memory or disk space required to represent the field from 320-bits to 192-bits in this simple and limited example. The potential space savings may be significantly greater where a greater number of patterns are referenced.
One problem that may arise in storing a pattern in a pointer rather than in using only addresses is the situation in which the pointer represents a NULL value. A NULL value is used by some database systems to indicate that no value has been entered in a particular field of a tuple. How the NULL value is represented or referenced by the pointer is generally handled according to two methods. First, the entire set of values that can be included in a 32-bit pointer include integers falling into the range of −231 to 2331 −1. The NULL value may be represented by using a particular value to represent NULL (e.g., −231). The second method uses an additional bit (i.e., 33-bits) to represent 232 integers and may allow for an extra NULL value or pattern. In the first case, one potential value is lost to the pointer, and in the second case an additional bit needs to be used resulting in an additional n bits of storage, where n represents the total number of fields.
According to one embodiment of the present invention, using the first value of the set of values for the pointer to represent NULL allows for NULL to be represented without sacrificing a value in a pointer or adding additional bits to the pointer. As previously discussed, pointers reference, through their addresses or pattern values, a set of values for the field. Including a NULL value as the first value in the set of values for the field allows for NULL to be represented with only 32-bits*f additional space required for this NULL value where f represents the total number of fields. This situation applies to both cases where the pointer contains an address and when the pointer contains a pattern or value directly. In the case of a pointer containing an address, the first value in the set of values referenced by the pointers for the field may be the NULL pattern or value. In the case of a pointer containing a value, the first value in the set of values contained by the pointer for the field may be the NULL pattern or value.
One example of a modified query engine in order to provide a select operation for including a NULL value and for allowing the use of patterns or values incorporated directly into the pointer is, according to one embodiment of the present invention, presented below.
In this example code, the select function for a field “f” of the database, a value of the field “v”, and a reference to a stored pattern “r” is executed according to 3 cases. The appropriate case is determined by examining the data settings for the field “d” which may include the above described example use of an extra 32-bits to identify the type of data contained for the field (e.g., whether the pointer contains values or patterns instead of addresses). In the first case, we are testing if the field contains a NULL value (i.e., v=NULL). In this case, the pointer is set to the first address in the set of addresses or, in other words, the first value in the set of values for the field. In the second case, the pointer contains the value for the field and not a reference to a value. In the third case, the pointer contains a reference to a value in the set of values for the field. In this case, we test whether the data pointed to by the reference (i.e., address) is equal to the requested value.
Evaluating an Expression
Many database operations may be implemented using an expression rather than a particular value. For example, a sort operation, a search or query operation, and a count operation may be executed using an expression or function in addition to or instead of using a field or field value. Expressions are often used in database operations but are typically implemented in conventional database systems by computing the expression for each tuple of the table for the operation (e.g., a query operation). For example, if a query operation is executed looking for all tuples where age is greater that 21 years old, the age field of every tuple is examined to determine if the tuple matches the query requirements for an age greater than 21 years old. According to one embodiment of the present invention, the structure of a database and its component tables as described above allow expressions to be evaluated in a more efficient manner.
The tree structure of a tuple according to the present invention may be used to implement an expression (i.e., a function). For example, if an expression uses three fields (leaf nodes) as arguments, the lowest level (i.e., closest to the leaf nodes and farthest away from the root node) common node for the three fields/leaf nodes in the tree structure may first be determined.
A function may be used to determine the lowest-level common node for the set of fields used as arguments in the expression according to one embodiment of the present invention.
In one embodiment of the present invention, the steps for computing the value of the expression and looking up the value using the path from the root node can be combined so that the expression value is calculated on demand.
One benefit of this embodiment is that the expression is evaluated only at most M times where M is the number of unique patterns for the first node. The expression needs to be evaluated only once for each pattern (i.e., unique combination of fields) and, if a subset of the root set is being used, less than M evaluations need to be made. The performance characteristics for evaluating the expression from the root node of the tuple instead of implementing the embodiment above has a time required to evaluate any field used as an argument in the expression related to O(length(path(root,field))), where root is the root node for the tuple. Using the embodiment described above, the time required to evaluate any field used as an argument in the expression is related to O(length(path(X, field))), where X is the first node (lowest level node) where the paths of the fields used as arguments in the expression join (i.e., share a common node). If there are f fields used as arguments in the expression and n root set elements (i.e., tuples), the performance for evaluating the expression from the root node instead of implementing the embodiment described above is O(n*j*length(path(root,field))). The performance for evaluating the expression using the techniques of the embodiment described above is O(M*f*length(path(X,field))+n*length(path(root,X))), where the first half of the calculation is the determination of the evaluation value from the first node X and the second half is the determination of the path from the root node, root, to the first node X. If the evaluation value has already been determined and is stored with the pattern at the first node X as described above, the performance time is related to O(length(path(root, X))) which is the time taken to follow the path from the root node, root, to first node, X(i.e., the lowest level common node for the field arguments in the expression).
Derived Fields
A data set may be further augmented by adding additional fields that are calculated from other field values in the data set according to one embodiment of the present invention. In other words, a derived field is a new column or field in the data set for a table that did not appear in the original data set but was instead computed from the original data set. For example, a field “data of birth” may appear in the original data set for a table and may be used to calculate a derived field “age”. Using a derived field may be particularly advantageous where the value in the derived field is often used for querying or sorting the data in the table. For example, if an age value is often used to query the table data or to sort on the table data, using a derived age field may improve the efficiency of the database system. A derived field value may be calculated at the time the data for a tuple is added or updated in a the table. For example if a new tuple for a person is added to a table, the derived age field value for the tuple is calculated using the data of birth field. If the data of birth field is NULL (i.e., there is no data in the field), the age field may also take a NULL value. Computing the derived field during the loading or updating of the data for a tuple according to one embodiment of the present invention may improve the performance of the derived field but results in an additional storage requirement for the derived field value. According to one embodiment of the present invention, only the unique values for the derived field along with the number of instances for the derived field pattern are stored in memory or on a storage device. The representation of the table may contain pointers to these values as it does for other field values. The overhead for implementing a derived field according to this embodiment is the additional storage required to store the unique patterns and number of instances as well as the pointers for each tuple. Though relatively efficient compared to conventional methods for using derived fields in a database management system, adding additional derived fields to a table or changing the definition of an existing derived field may still require a pass through the entire data set to generate the derived field values which, depending upon the circumstances, may result in an undesirable impact on the efficient operation of the database management system.
In another embodiment of the present invention, an expression (i.e., a function) is used to implement a derived field rather than calculating and storing a computed value. An expression is stored for the derived field and the expression is evaluated when the derived field is used rather than computing the value of the derived field earlier as in the previously discussed embodiment. The use of an expression reduces the amount of space in memory or on a storage device required to implement the derived field. In addition, the use of an expression allows derived fields to be added, modified, and removed in an easier manner generally using less resources than otherwise required. Also, the use of an expression for a derived field allows the derived field values to be dynamic in that they reflect the latest calculation rather than a previously made calculation. For example, a derived age field implemented as an expression results in a calculation made using the current date when the derived is used rather than an age determined the last time the tuple was added or modified or when the derived fields was added.
Implementing a derived field using an expression may only require the modification of the function that retrieves the value of the field according to one embodiment of the present invention. According to this embodiment of the present invention, the definition of a path may be expanded to include not only the previously described path to a node value but also a path may contain the details of an expression. For example if an expression is used to add a derived value to a table, a path may lead to the expression that can be evaluated when the derived value for a tuple is needed according to this embodiment. The expression for the added derived value may be stored along with the pattern data for the other fields of the table so that a function retrieving the value (e.g., a pathToField( ) function) may retrieve the expression. The valueOf( ) function retrieves the value (i.e., pattern) for a field f of a table t as previously described. According to this embodiment of the present invention, the valueOf( ) function is modified below to allow a determination if a path points to an expression or a value and, if it points to an expression, to evaluate the expression and return the results.
As stated above, the path to a field f of a table t is first determined then evaluated according to the valueOf( ) function used in one embodiment of the present invention. If the path is to an expression, the expression is evaluated and the expression value is returned. If the path is not to an expression but to a field, the value of the field is retrieved. A path is retrieved as previously described but an additional test (the if statement) is added to determine if the path is for an expression. If the path is for an expression, the value for the derived field is computed using the expression otherwise the value of the field is retrieved for the path as previously described.
Root Indirection Set
A root indirection set 1050 may exist for each table (root node set) in a database and additional root indirection sets may be used for specific operations as needed according to one embodiment of the present invention. For example, a primary or original root indirection set may exist for each table (root node set) to allow faster delete, insert, and update operations as discussed in the following sections. Additional root indirection sets may also be used for other operations such as sorting, partitioning, and joining as discussed later in this specification. According to this embodiment, the same root indirection set does not need to be used for all database operations (though they are based on the same data). In a sort operation as part of a database query, a copy of the root indirection set may be made and the order of the pointers in the root indirection set copy may be altered to produce the sorted tuple results without changing the underlying tuple order (and the original root indirection set). A query or search operation may also use a root indirection set that is a subset of all the tuples in the data set for the table. For example using
Delete Operation
Using a primary root indirection set for a table, a tuple in the data set for a table may be in part deleted by removing the reference (i.e., the pointer) in the root indirection set addressing the tuple of the table. If a root indirection set is used to access the tuple data for the table, the absence of a pointer will eliminate accessing that particular tuple of the table. In one embodiment of the present invention, the pointer is replaced by an illegal value (i.e., a value that can't be used as a pointer) such as, for example, a negative value. A negative value may be used instead of eliminating the pointer altogether because it preserves the order of the tuples especially where the pointers to the patterns for the tuple may still exist at the nodes of the table. For example, a root indirection set containing 10 pointers to tuples in the data set for a table may be initially represented as follows:
{0, 1, 2, 3, 4, 5, 6, 7, 8, 9} (RIS1)
In the root indirection set above (RIS1), each pointer value represents the address in the root node for each tuple of the table—e.g., the address of the pair of pointers corresponding to the tuple in the root node for a binary tree representation of the table. If the fourth tuple of the table is deleted, according to this embodiment the fourth pointer (i.e., pointer value 3) is replaced with an illegal (e.g., negative) value resulting in the root indirection set containing the following new example values:
{0, 1, 2, −1, 4, 5, 6, 7, 8, 9} (RIS2)
Database operation algorithms using the root indirection set need to be properly structured in this embodiment to ignore illegal (e.g., negative) values when they occur. Therefore, when a database operation algorithm using the root indirection set encounters a negative value, that negative value is ignored as if it did not exist. For example, an algorithm processing the tuples of a table according to the modified root indirection set RIS2 above would ignore the −1 value.
In another alternative embodiment of the present invention, a deleted tuple may be reflected in the root indirection set by using a negative value of the actual pointer value (address). For example if a pointer has a value 3 (the 3 referring to a memory or storage address) addressing a tuple in the data set of a table, deleting the tuple may result in resetting the pointer value to −3 to reflect the deletion of the tuple. Using the original root indirection set RIS1 above as an example, deleting the fourth tuple of a table results in the root indirection set pointer value for the tuple (e.g., 3) being set to the negative of its value (i.e., −3). The resulting root indirection set is shown below.
{0, 1, 2, −3, 4, 5, 6, 7, 8, 9} (RIS3)
In this embodiment as in the previous embodiment, the value of the pointer for the deleted tuple is negative and it is still ignored by the database operation algorithms as discussed above. However, this alternative embodiment has the added benefit that the deletion of the tuple can be reversed or undone by taking the absolute value of the pointer. For example, the root indirection set RIS3 above reflects the deletion of a tuple (i.e., the fourth tuple) in a table. The deletion of this tuple can be reversed by taking the absolute value of the pointer value (e.g., the absolute value of −3) to restore the deleted tuple in the root indirection set. The resulting root indirection set is the original root indirection set RIS1. Though this example may simplify the contents of the pointer for explanation, the use of a negative or other inverse pointer value may be used to delete a pointer in a root indirection set in a manner to facilitating an option to undo the deletion.
The above embodiments have only dealt with the deletion of a tuple in the root indirection set. In addition, the deletion of a tuple needs to be reflected in the data set for the table. Traditionally, deleting a tuple of data resulted in the actual erasing or removing of the information from the table. According to one embodiment of the present invention, the tree structure representation of a table allows the actual data patterns to be stored separately from the table structure. According to this embodiment, a frequency indicating the number of times that a pattern (i.e., a value) occurs in the table is stored along with the pattern. As a result, deleting a tuple of a table necessitates updating the frequency associated with a pattern found in the tuple of the table. For example if a table contains information about an individual including the city in which they live, deleting a tuple requires decreasing the frequency for the associated city (e.g., London for the deleted tuple) by 1 to reflect the deletion of one instance of the pattern (e.g., London) in the table.
Updating the pattern frequencies for the data set of the table to reflect the deletion of a tuple can be achieved by traversing the tree representation of the deleted tuple decrementing the frequency for each occurrence of a pattern in the deleted tuple. This can be accomplished using any method of tree traversal that visits all the nodes in the tree representation of the tuple once (visiting a node more than once during the tree traversal may have an unintended consequence of decrementing the frequency by more than the one value in the deleted tuple). Tree traversals are conventionally well known and refer to the sequential processing of each node in a tree structure, which is inherently a non-sequential data structure. Traversal methods are characterized by the sequence in which the nodes of the tree are processed. Three common types of traversal for binary trees are pre-order, post-order and in-order traversal. A pre-order traversal method visits each node before any child nodes are visited. A post-order traversal method visits each node after all its child nodes have been visited. An in-order traversal method visits each node after visiting all its left-branch child nodes but before visiting any of its right-branch child nodes. According to one embodiment of the present invention, an in-order traversal method is used though the present invention may work with many other traversal methods.
The following algorithms for tree traversal assume that the structure of a node in a binary tree has a pattern (i.e., a value) and may have a left and right child node value. The following is an example of an in-order traversal function:
According to this above example in-order traversal function, each left child node is processed before the parent node and each right child node is processed after the parent node is processed. The following is an example of a pre-order traversal function:
The above pre-order traversal function visits and processes each node before any of its child nodes are processed. An example of a post-order traversal is as follows:
In the above post-order traversal function, all child nodes are processed before any parent nodes. As stated above, an in-order traversal of the tree structure for the table is used though other traversal functions may be used in other embodiments of the present invention. The following in-order function is used to decrement the frequency of the patterns for the deleted tuple in the data set of the table.
The decrement_frequency( ) function operates recursively and begins by finding the lowest level left branching pattern (p.left) at a child node to process (as long as it is not NULL in value) and continues an in-order traversal of the tree. The processing done at each node for the pattern in the deleted tuple is simply to decrement the pattern frequency (p.frequency=p.frequency−1) to reflect the reduced frequency of the pattern due to the deletion of the tuple.
When a pattern has a frequency of zero in the data set of a table, the pattern is not being used by any of the tuples in the table and, therefore, is not reachable through any of the pointer pairs in the root node. Additionally, a pattern having a frequency of zero indicates that all the patterns above it (patterns at parent and ancestor nodes) also have a frequency of zero. For this reason, a pattern with a frequency of zero can be removed from the pattern set for the node of the table. According to one embodiment of the present invention, a pattern with a frequency of zero is not removed because the adverse impact of the removal outweighs the minimal additional space (memory or storage) saved by no longer storing the pattern. In this embodiment when the data set of the table is first built (or it is optimized according to one embodiment of the present invention), a continuous area of memory or storage is allocated to the pattern set. As additional patterns are added to the pattern set for the node in the tree structure representation of the table, these additional patterns are stored in an overrun pattern set in memory or storage outside the originally allocated continuous pattern space. If a pattern is deleted from the continuous pattern space, the gap created in the continuous pattern space may need to be closed by moving other patterns within the continuous pattern space. This can be very computationally and resource expensive. Additionally, accessing and searching for patterns in the overrun pattern set is less efficient than accessing and searching for patterns in the continuous pattern space. Therefore, deleting a pattern from the continuous pattern space reduces the number of patterns that can be more efficiently accessed and searched. This presents no problems if the pattern is deleted but should the pattern be added back into the table at a future time, the overall performance of the database may be adversely impacted as the pattern will now be in the overrun pattern set rather than in the continuous pattern space. Also, a pattern once in the data set of a table is more likely to again recur at some future time. An insert or update operation adding a new pattern to the pattern set requires additional resources and time that can be avoided by leaving the pattern in the pattern set. The overhead for the pattern is generally very low and consists of the space (memory or storage) required for the pattern and an integer for its frequency. For these reasons, if a frequency of a pattern becomes zero as a result of a tuple being deleted from the data set of the table, the pattern is not deleted from the pattern set according to this embodiment of the present invention. In an alternative embodiment, the pattern may be deleted but the problems discussed above may occur.
Insert Operation
An insert operation allows a new tuple to be inserted into the tree representation for the table according to one embodiment of the present invention. The inserted tuple may be viewed as another set of patterns combined to form the tuple. If all the patterns in the inserted tuple already exist in the data set for the table, no additional patterns need to be added to the pattern set for a node. However if a new pattern is contained in the tuple, that new pattern needs to be added to the pattern set so that pointers in the tree structure representation of the tuple can refer to the pattern. In one embodiment of the present invention discussed above, the data set for the table is initially stored in one continuous block of memory. Additional patterns added later to this data set of patterns for the table may exceed the block of memory initially allocated to this continuous block of memory. Therefore, the additional patterns may be stored in an “overrun pattern set” or “overrun set”. The dynamic nature of the data in a database makes it inefficient to try to determine and allocate sufficient continuous memory space for the patterns at each node (i.e., root, branch and leaf nodes) in the data set for the table. Therefore using an overrun set to handle the additional patterns inserted after the database is built (or alternatively optimized in another embodiment of the present invention) can solve this problem with little additional cost. The result for each node may be a pattern set consisting of two parts: an initial pattern set in a continuous block of memory generated when the database was built or later optimized; and a second overrun pattern set containing all the additionally inserted patterns after the database was last built or optimized. Using an overrun pattern set simplifies the insertion process while still retaining the benefits of the present invention as outlined herein but it may require more memory to store each pattern and may be slightly less efficient due to the use of non-continuous space to store the additional patterns. One possible implementation of an overrun set is a balanced binary tree of patterns.
One example of the use of an initial and an overrun pattern set for a leaf node (field) in a table using a balanced binary tree structure is illustrated in the following embodiment. If a field f contains integers, the initial pattern set (indicated in square brackets [ ]) may contain the following patterns (values):
[7, 11, 14, 18, 23, 26, 37]
As stated above, these patterns (integers) are stored in consecutive memory addresses. If the data can be updated (as is generally the case), an overrun pattern set is created to store any new patterns. Initially, the overrun pattern set (indicated in braces { }) for the node is empty with field f containing the following patterns:
[7, 11, 14, 18, 23, 26, 37] { }
A new tuple is added to the table. If the new tuple contains a value for f of “9”, a determination is made that “9” is not in the original pattern set nor in the overrun pattern set so it must be added to the overrun pattern set. This results in field f now containing the following patterns:
[7, 11, 14, 18, 23, 26, 37] {9}
If another new tuple is added containing a valued for f of “5”, a determination is again made as to whether the value “5” already exists in the original pattern set or in the overrun pattern set. In this case, “5” exists in neither set. This results in field f containing the following patterns:
[7, 11, 14, 18, 23, 26, 37] {5, 9}
The value “5” is placed before the “9” according to the balanced binary tree structure of the overrun pattern set. Eventually, field f may contain several additional (overrun) patterns as shown in the example below.
[7, 11, 14, 18, 23, 26, 37] {5, 9, 12, 24, 36}
A balanced binary tree pattern set may have an average search time of O(ln(|T|)) representing order of log of size T where T is the balanced binary tree. Other structures such as a hash table or other extensible data structure may be used for the pattern sets in other embodiments of the present invention.
When a new tuple is inserted into the table, any new field values (patterns at the leaf nodes) must be added to the pattern set of the appropriate leaf nodes. For any existing field values (patterns at the leaf nodes) for new tuple, the frequency of the existing field value may be incremented to reflect the new occurrence (incidence) of the pattern in the table. For example, a new tuple inserted into the table may contain information for an individual living in “London” 1082 requiring the frequency for the pattern “London” 1082 in the city pattern set 123 to be incremented from 2 1083 to 3 to reflect the additional occurrence of the value in the tuples of the table. This is a simple operation for any existing patterns that are in the new tuple. However, a new tuple inserted into the table may contain information for a city not already in the pattern set (e.g., “Birmingham” 1084) requiring the new city pattern (e.g., “Birmingham” 1084) to be added to the city pattern set 123 with a frequency of 1 1085 for this one new occurrence of the value. As discussed above, a continuous block of memory may be allocated to the pattern set 123 when the table is built or when an optimization function is run reorganizing the data. Using a continuous block of memory may enhance the efficiency of the database. When an inserted tuple is added after the continuous block of memory has been allocated, the new patterns from the inserted tuple are added to the overrun pattern set for the node according to one embodiment of the present invention. Using the above example, the new city pattern “Birmingham” 1084 is then added to the overrun pattern set for the city leaf node 123. The use of a continuous block of memory or storage when a pattern is built or optimized is according to one embodiment of the present invention. Other than discussed herein, the use of a continuous block of space (memory or storage) for the pattern set has no other impact on these database operations, which can still function properly where other storage configurations for the pattern sets are used (e.g., all continuous or all non-continuous memory or storage space for the pattern sets). For example in an alternative embodiment of the present invention, pattern sets may be stored in a non-continuous manner making the entire pattern set function like the overrun pattern set. In this alternative embodiment, the performance of database operations on the pattern set may be less efficient than otherwise possible but the database operations function in the same manner as described herein.
When inserting a tuple into a table according to this embodiment, patterns need to be either added or updated (i.e., the frequency incremented) for all the nodes in the tree structure representation of the table for the inserted tuple.
A variation to the above insert process in one embodiment of the present invention may improve performance by omitting the determination of whether a branch pattern is new and assume that the branch pattern will always be new. Either the search for a matching pattern in the initial pattern set for the branch node may be omitted or both the search for a matching pattern in the initial pattern set and the overrun pattern may be omitted in alternate embodiments of this variation.
Implementing this embodiment incorporating a variation to the insert process may require additional changes that may result in reduced efficiencies and/or otherwise require alterations to other sections described herein. For example, using a balanced binary tree structure for the overrun set may no longer be possible if a pattern can be included multiple times in the overrun set—doing so runs counter to the definition of a balanced binary tree. In another example, the existing algorithms/functions may need to be modified to handle the occurrence of multiple identical patterns in the data set (pattern set) for a field (a leaf node).
The above embodiment variations become particularly advantageous with pattern sets having a high initial cardinality (i.e., where repeat patterns are uncommon or are less common). Conversely, the disadvantages become substantially greater in pattern sets expressing little initial cardinality. For this reason, an alternative embodiment of the present invention may incorporate a method for measuring the cardinality of a branch node with this cardinality value determining how an insert operation is executed on a pattern for a branch node. For example, an initial cardinality value may be used with the first embodiment variation above because it only skips the first initial pattern set. In this case, the variation skips the search of the initial pattern set for a node where the cardinality of the pattern set for the node exceeds a particular threshold. This cardinality may be determined when the initial pattern set is built and/or when it is updated during an optimization process. By determining the cardinality value during the initial build of the data set for the table or when an optimization process is run on the data set (updating the initial data set) the cardinality can readily be available and used to determine whether this embodiment variation is used to process a pattern for a branch node.
In addition to adding the new patterns in the inserted tuple to the patterns sets for each node in the tree structure representation of the table and incrementing the frequency for existing patterns in the inserted tuple, an insert operation also adds an entry to a root indirection set for the table according to one embodiment of the present invention. If a root indirection set is being used for the table, the inserted tuple is added using a pointer to the newly inserted root node for the tuple. Because the root indirection set maintains the order of the tuples in the table, the pointer to the newly inserted tuple is added to the end of the root indirection set according this embodiment. For example, the following root indirection set contains a set of pointers to root patterns for the tuples of a table:
{0, 1, 2, 3, 4, 5} (RIS4)
Each pointer contains the address of the root node pattern of a tuple. Inserting a new tuple results in an additional pointer in the root indirection. According to the embodiment discussed above, the new pointer is appended at the end of the root indirection set resulting in a new root indirection set:
{0, 1, 2, 3, 4, 5, 6} (RIS5)
The newly inserted tuple is identified by pointer 6 in the root indirection set above (RIS 5). In other embodiments of the present invention, the newly inserted tuple or pointer to the tuple may be placed elsewhere in the table and/or elsewhere in the root indirection set.
Update Operation
An update operation is another database operation that may be performed on the data set of a table using a root indirection set according to one embodiment of the present invention. During an update operation the contents of the data for a tuple of a table are modified. In addition to a possible modification in the root node, the actual data stored in the leaf nodes (the pattern sets) for a field in the binary tree representation of the table and intervening branch nodes may also need to be modified according to this embodiment. The possible implications of an update operation on the data set for the table (the tree representation) are further illustrated using the root indirection sets and figures discussed below.
An update operation may be implemented according to one embodiment of the present invention as a combination of a delete operation and an insert operation.
{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}
The deletion of a tuple (e.g., the fourth tuple identified by pointer 3) results in a negative value as previously discussed. For example in an embodiment where a simple negative value is used to indicate a deleted tuple, the original root indirection is modified to reflect the deletion as follows:
{0, 1, 2, −1, 4, 5, 6, 7, 8, 9}
The second step 1052 in the update operation according to this embodiment is the insertion of the updated record as discussed above (an insert operation). As previously discussed, an inserted tuple may be appended to the root indirection set. According to this embodiment, the root indirection set reflecting the deletion above is then appended to reflect the inserted and updated tuple as follows:
{0, 1, 2, −1, 4, 5, 6, 7, 8, 9, 10}
The third step 1053 is restoring the order of the tuples to reflect the update. To restore the order of the tuples, the newly inserted tuple needs to be moved to (swapped with) the position of the deleted tuple in the root indirection set. This may be accomplished by simply swapping the pointers to the deleted tuple with the pointer to the inserted tuple resulting in the following root indirection set:
{0, 1, 2, 10, 4, 5, 6, 7, 8, 9, −1}
The result is the inserted and updated tuple is in the same order as the original deleted tuple. The above embodiment accomplishes an update operation by deleting and inserting a tuple. The order given above is only one possible example and the order between inserting the updated tuple and the deletion operation on the original tuple may be switched in another embodiment of the present invention.
Sorting
According to one embodiment of the present invention, the process for building the example database identifies patterns in the data sets and replaces the patterns in the tables of the example database with pointers to the pattern stored elsewhere in memory or on a storage device. In this manner, the size of a table is greatly reduced resulting in something similar to a three-dimensional array of pointers, which can be rapidly sorted. For example, the computational complexity of conventional sorting algorithms determines their execution time. Some conventional sorting algorithms have an average execution time to sort a conventional data set of size n that is proportional to O(n log(n)). These conventional sorting algorithms may include traditional binary tree sorting, heap sorting (i.e., heap sorts), and merge sorting to name a few. Other conventional sorting algorithms may be less efficient with an average execution time O(n2). An example of this type of sorting algorithm is traditional bubble sorting. Conventional techniques exist to improve the performance of sorting algorithms by using key comparisons with average sort execution times proportional to O(n log(k)), where k is the size of the key space. According to one embodiment of the present invention, a majority of sorting operations can be reduced to an average execution time proportional to O(n) with only a few circumstances requiring average execution times proportional to O(n log(n)). This improvement in sorting execution times are partially a result of the representation of the data set using pointers as described in the following embodiments rather than the traditional database representation storing actual patterns. In addition, this improvement is also due to maintaining the data in the database in a sorted ordered by pattern that, as discussed later, can greatly reduce sort operation execution times according to one embodiment of the present invention. One embodiment of the sorting operation is discussed in greater detail in the following section.
As discussed above in the pattern recognition section, the identification of patterns in the data set results in a pointer being stored for each pattern according to one embodiment of the present invention—the pointer pointing to the pattern and the number of instances of the pattern in the table. According to this embodiment, each pattern in a tuple or row of a table is replaced by a pointer pointing to the pattern and the number of instances of the pattern in the table. The pointers point to a condensed listing of the unique patterns and instances (i.e., the pattern frequency) with duplicate patterns normally found in the table removed.
After determining the initial offset values for the patterns 1204, the sorting process continues by examining each tuple of the existing table in order to generate the sorted table. The first step in this sub-process for each tuple 1205 is a determination whether the end of the table has been reached. If not, the value of the pattern for the next tuple is identified 1206. Using the offset for the pattern determined earlier 1204, the tuple is written to the new sorted table in the allocated memory or storage at the offset value for the pattern 1207. The offset value for the pattern is then incremented 1208 and the determination of whether the end of the table has been reached 1205 is again made. On reaching the end of the table 1205, the sorting process may terminate 1209 at which time the table will be sorted in the allocated memory or storage.
The sorting process embodiment can be further illustrated showing an example of the use of offset values to the memory or storage areas for the sorted table.
The sub-process 1205-1208 that executes for each tuple of the table as part of the overall sorting process performs the calculations for executing the sorting process as outlined in
The above offset process performs a single pass through the pattern set (e.g., as shown in
The above offset process is used during the sorting process according to one embodiment of the present invention. The sorting process rearranges the tuple data in the memory or storage allocation for the sorted table to sequentially place patterns in sort order according to the sorting pattern value and by instance. In another embodiment of the present invention, an alternative process to determine the offset may be used.
The offset process shown above involves a single pass through the set of patterns for the table to compute the initial offset values and then a single pass through the set of tuples in the original or root table to sort the tuples. The time to compute the sort is proportional to O(m+n) where m is the number of patterns (the first pass or loop in the offset process) and n is the number of tuples in the table (the second pass or loop in the offset process). The upper bound of the time to compute the sort is proportional to O(n) because m<=n where n dominates this computation.
As previously discussed, the patterns are structured as a tree in the representation of the table, therefore the time required for the sort operation may be determined not by n (the number of tuples in the table) but by f the number of fields in each tuple. Where a table contains only a single field, performance remains proportional to n as previously discussed because f is one. However, if a table contains multiple fields, the performance of the sort operation may be considered proportional to log(f). The above example performance calculations for one embodiment of the present invention reflect the utility and efficiency of the embodiment as compared to conventionally known sorting techniques. The sorting operation in this embodiment may include other efficiency in consideration of the use of pointers rather than actual table data and through the use of the offset values during the sorting process.
In an alternative embodiment of the present invention, the offset values may be stored along with the patterns and number of instances in memory or storage instead of calculating them during the sort operation. An example of this embodiment is reflected in
According to one embodiment as previously discussed, the sorting process generates a new sorted table in memory or storage containing n pairs of pointers (one pair for each tuple storing the root node pattern) and therefore requires 2nP bits to store, where P is the number of bits per pointer (typically 32 or 64) representing the typically addressing size of the computer system. The pairs of pointers in the sorted table are identical to the pairs of pointers in the original, root table but are only in a different order. The sorting operation does not alter the pair of pointers only their order in the table. Therefore, instead of generating a sorted table containing n rows of the pointer pairs, a single pointer to a tuple in the root table could be stored in sorted order instead according to another embodiment of the present invention. Instead of the sort operation generating a new sorted table, a root indirection set (i.e., a sorted set of pointers to the tuples in the root or original table) could be generated instead. The root indirect set uses just nP bits because only one pointer is stored for each tuple in the table. Therefore, using a root indirection set according to this embodiment may use half the memory to perform the sort operation for a table with a single field.
Combining a selection or query operation on one particular field along with a sort operation on the same or other field presents additional challenges above those discussed for the previous embodiments of the sort operation. A selection or query operation is the identification of a subset of the data in the table. For example, if a table contains information about automobiles and one particular field is the age in years of the car, a selection or query specifying SELECT WHERE age<5 may result in only those tuples of the table for which the age field value is less than 5. Selection or query operations are common when using databases. In conjunction with a selection or query operation, a specification is often made regarding the order in which to present the selection or query results. Specifying the order of the results is a sort operation combined with the selection or query operation. Under these circumstances, not all of the tuples of the table are of interest and, therefore, the offset values are no longer relevant as the results will generally not include every tuple of the table. One solution (embodiment) to handle this situation may include recalculating the number of instances of the patterns for the subset of data returned by the selection or query operation and the new associated offset values. However, the recalculation of the number of instances of the pattern for the subset is time consuming and results in values that are only valid for that selection or query operation. In another solution according to one embodiment of the present invention, the sorting process is conducted as previously described with the addition of two new steps: a selection or query matching operation and the removal of gaps in the resulting sorted table or root indirection set.
The previous examples have described one embodiment of the present invention where sorting of a table is performed using a single pattern or field from the table. However, it is also possible to sort the data using multiple patterns or fields. According to one embodiment of the present invention, a sorting process using multiple fields can be implemented by using the previously described sorting process on the patterns in a reverse sort order for the patterns. For example, if a table containing the fields vehicle manufacturer and age of vehicle is to be sorted first by manufacturer then by age within manufacturer, the above described sorting process can be used to sort the data first by age and then the resulting set of data sorted by age can then be sorted by manufacturer. The result is a final data set first sorted by manufacturer then by age within manufacturer. The term data set is used instead of table because this process for sorting by multiple fields can also be used in conjunction with a selection or query operation resulting in only a subset of the table data being used.
Building the root or original table in a manner where the tuples are already sorted by pattern or field may improve the sorting operation by allowing two of the steps—1203 determining the order of the patterns and 1204 determining the offset value for each pattern—to be eliminated where the natural order (the original or root order) is used for the sort. This can improve the performance of the sort operation by reducing or eliminating several calculations that are part of the process outlined above. The resulting benefit is that performance more closely achieves O(log(n)) or O(log(m)) rather than O(n+mlog(m)) that otherwise occurs, where n represents the number of tuples and m represents the number of patterns. Building the root or original table in a manner where the tuples are sorted according to a unique key for the table is particularly performance enhancing according to one embodiment of the present invention. Sorting on an unsorted unique key requires time proportional to O(n log(n)) to complete, which is the average sort time of many conventional sorting algorithms. By sorting the table by unique key when the table is generated or amended, future sorting operations can still function efficiently because sorting on the unique key can be avoided.
Sort by Expression
According to one embodiment of the present invention, using derived fields or an expression, both of which were previously discussed, can further expand the sorting process. A derived field may be calculated and added to the data set for a table and is treated as any other field according to one embodiment of the present invention. An expression is calculated at the time an operation (e.g., a sort operation) is executed and is not stored with the data set for the table according to one embodiment of the present invention. A sort by expression operation uses an expression as part of the sort process.
Sorting conducted according to the previously discussed embodiments of the present invention used single or multiple patterns in a table to produce the sorted ordering of data. Combining some of these previously discussed techniques allows for the implementation of a sort operation using an expression in addition to or in place of using a pattern value according to one embodiment of the present invention. Unlike the values for a pattern that are part of the data set for a table, an expression is a computation using the values of the patterns in the table and it is not stored for future use in one embodiment of the present invention-unlike derived fields.
In this embodiment, an expression is a computation made during the sorting process using at least one of the fields from the data set for the table. For example, an expression may involve a mathematical calculation such as 5*int(age/5). According to this example, a derived field value age, as previously discussed, is divided by 5, the integer value of this quotient is taken and multiplied by 5 yielding the expression value to be used in conducting the sort operation. Another example of an expression involving a mathematical calculation is the expression premium-claim which uses a difference obtained by subtracting the value of an insurance claim (i.e., the claim field value) from the value of an insurance premium (i.e., the premium field value). These are only a few examples of possible expressions that may be used according to this embodiment. The use of an expression for a sort operation may require a new or modified sorting process according to this embodiment of the present invention.
The sorting process as previously described according to one embodiment of the present invention used sort “bins” determined according to the unique patterns of the sort field. Memory or storage space was allocated for the sorted data, the memory was partitioned using offset values to created blocks of space or sort bins for each unique value in the sort field or key and a single pass through the tuples of the table was made with each tuple being assigned to the appropriate bin or block of space. The sort process for multiple fields worked the same way sorting by the individual sort fields (sort keys) in reverse order according to one embodiment described above. The sorting process using an expression is similar but uses a modification of these previously described techniques accounting for the addition of one or more expressions.
The sort by expression process begins by conducting a previously described sort operation on the table t using a sort field f representing a field used in the expression for the table—in this example, the age field. As previously described, the sort operation, according to one embodiment of the present invention, allocates an area of memory for the sorted table and then determines “bins” or subsets of the allocated memory by computing offset values. These bins are determined using the unique patterns for the sort field. Tuples from the table are then copied to the appropriate bins, which are arranged by sort order, in the tuple order in which they appear in the original table. The sort by expression process calculates the expression value for each of the determined bins (the unique patterns for the sort field f) resulting in a determination of the sort field or key (the expression value) for the sort by expression operation. These sort fields or keys (sort expression values) are then reorganized in a sorted order with the bins being reorganized according to these keys. Because of the initial sort based on the field used in the expression, the sort field/key for the sort by expression operation only needs to be determined once for each bin and the bins as a whole can be rearranged according to the sorted order of these sort fields/keys. In
Further examining the sort by expression process, an execution or performance assessment may be made by examining each of the subordinate steps. The initial sort operation sort(t,f) takes O(n) operations as previously described. Determining or collecting the keys (i.e., the sort expression values) for the bins (i.e., for each unique pattern in the sort by expression field f) takes O(m) operations where m is the number of unique patterns for the sort by expression field f. The keys are then sorted taking O(mlog(m)) operations and resulting in a sorted list of the keys (i.e., sort expression values). As shown in
The sort by expression process evaluates the function for the expression m times during the sort operation where m is the number of unique patterns for the sort by expression field for, in other words, the number of sort bins in the initial sort. Evaluating the expression for m (i.e., for each occurrence of a unique pattern) rather than n (i.e., for each tuple in the data set for the table) may result in a significant improvement in performance where n is greater than m. The more significant the difference between n and m, the greater the performance gained by using the initial sort bins to evaluate the expression rather than on a row-by-row basis requiring n calculations of the expression (i.e., execution of the expression function) as is generally done in conventional database management systems. The above description for implementing a sort operation based on an expression can also be applied to a partition or join operation as well, especially because both are based on the basic sort process as described later in this document.
The embodiment discussed above describes the implementation of a sort by expression operation where the expression is based on a value for a single field in the data set for the table. In another embodiment of the present invention, the sort by expression operation may be conducted using an expression involving multiple fields in a table. If the sort expression uses multiple fields to determine a single key (i.e., sort by expression value), there is little change from the sort by expression process described above. According to one embodiment where multiple fields are used to generate a single key (sort by expression) value, a minor variation of the sort by expression process may be used as follows.
The sort by expression process conducts the initial sort using the multiple fields as previously described. The order of the fields in this initial sort does not matter as any resulting sort bin organization for the combination of the field values/patterns will be reordered later. The initial sort by the multiple fields creates bins for each unique combination of the patterns for the multiple fields with each bin containing any corresponding tuples (or root indirection sets for the tuples) of the table. The keys (sort by expression values) are then determining using the multiple fields. The keys are then sorted and the bins reordered as previously described resulting in a successful sort by expression using multiple fields in the expression.
Using the multiple fields alters the performance characteristics of the sort by expression process. The performance of the sort by expression process using a single field had an execution time related to O(2n+m+mlog(m)) operations. The sort by expression process using multiple fields has an expected performance related to O(2n+M+Mlog(M)) operations, where M is the total number of unique patterns resulting from the various combinations of the multiple fields used in the expression for the sort by expression operation. For example, if a sort by expression operation is conducted for the expression 5*int(age/5)+int(int(income/1000)/2), the unique patterns M for the different combinations of the fields age and income determine the performance of the sort by expression operation.
In addition to the above examples where a sort by expression operation using a single field and a sort by expression operation using multiple fields are described, a sort by expression operation using multiple expressions may also be implemented according to one embodiment of the present invention. The use of multiple expressions is similar to sorting using multiple fields as previously described. According to this embodiment, a sort by expression operation for the last expression-whether using single or multiple fields—is first conducted, then a sort by expression operation for the next to last expression is then conducted until the final sort by expression operation for the first expression is conducted (i.e., the sort by expression operations are conducted in reverse expression order-last first). The sort by expression operations for multiple expressions results in a table sorted first by the first expression, second by the second expression, etc. The sort by expression operations each work as outlined above depending upon if a single field or multiple fields are a used for the sort expression. A sort by expression process for multiple expressions, according to one embodiment of the present invention, is shown below.
The sort by expression process for multiple expressions is similar to the previous sort by expression process except that a loop is executed for each expression with the sorting beginning with the last expression and continuing until the first expression—the final expression for which a sort operation is conducted—is executed.
Partition Operation (Partitioning)
The sorting process can be used to implement partitioning and joining according to one embodiment of the present invention. Partitioning is the dividing of a table and possibly its associated data set. For example, if a table contains information about automobiles, it may be partitioned into subsets of data according to the manufacturer. One example of this type of partition operation is to group and separate all the tuples for “Mfg A”, “Mfg B”, etc. This example divides information by complete tuple according to one embodiment of the present invention. For example, dividing the automobile information by manufacturer results in the complete tuple of automobile data for the manufacturer being included into the partitioned subset (i.e., partition). According to this embodiment, the sorting process previously discussed can be used to group the data in a table according to the partition field or partition key and generate the partitions accordingly. The partition field or partition key is the pattern or field value that is the basis for the sorting and separation of the data in the table—in the above example, the manufacturer field value/pattern. Though the examples for this embodiment use a single partition field, it is possible to partition a table using multiple fields or patterns in other embodiments of the present invention.
According to this embodiment of the present invention if the manufacturer field has 5 associated patterns 1103-1107 as illustrated in
According to one embodiment of the present invention, the partition process is based on the sort process previously discussed and is shown as follows.
The value t is the table to be partitioned and f is the partition field according to which the table is partitioned. The table is first sorted and the two counter variables fieldIndex and count are initialized (set to 0). After the sorting of the table, a for loop is executed going through each tuple of the table one at a time partitioning the tuples by the partition field.
The partition operation is a linear process similar to the sort process as previously described. A first pass through the table is executed to sort the tuples and a second pass through the sorted table is executed to assemble the partitions according to this embodiment. In one embodiment of the present invention, the partitions are virtual tables in that they share leaf and branch nodes with the parent table. For this reason, the only unique information for the partition is its root indirection set referencing the already existing parent table tuples making the partition efficient and quick to compute, manipulate, and store. For example, a partition on a 32 bit computer system may use approximately 4n bytes to store the entire set of partitions for the table according to this embodiment, where n is the number of tuples in the table.
The combination of a selection or query operation with the sorting process as previously described can be used as the basis for a partitioning of the data set according to another embodiment of the present invention. According to this embodiment, all the data in the table is not partitioned as described in the previous embodiment and a partition is only generated for a subset of the data (i.e., tuples) in a table. According to this alternative embodiment, if a table includes information about automobiles a partition or subset may only be generated for tuples related to one manufacturer. In the previous embodiment, the entire table would be partitioned as a result of the partition operation with one partition for each manufacturer. According to this embodiment, a partition only for the desired (i.e., selected) manufacturer will be generated. The partition process for this alternative embodiment is similar to the partition process for the previous embodiment except that a partition is only generated for the desired partition field value/pattern.
The previous partition embodiments grouped and separated the table data by complete tuple. In the first embodiment, a root indirection set was generated for each partition with one partition for every value/pattern in the partition field. In the second embodiment, a root indirection set was generated for the sole partition for the selected partition field value/pattern. In both cases, the root indirection set referenced the complete tuple in the parent table from which the partition was generated. In another embodiment of the present invention, a partition operation may be used to split the tuples of the table with the fields and patterns being partitioned rather than the tuples. This embodiment is the opposite of the join operation discussed below and illustrated later in
Join Operation (Joining)
The sorting process can also serve as the basis for a join operation according to one embodiment of the present invention. A join operation is the combination of two tables into a single resulting joined table. For example, a table or data set of automobile information for cars made by manufacturer “Mfg A” may be combined with a table or data set of automobile information for cars made by manufacturer “Mfg B”. This example of a join operation is the opposite operation to a partitioning operation as described above. According to this example, both tables share a similar structure in that they have the same fields for each tuple of data. The join operation according to this example merely merges the two tables sharing the same structure into a single joined table. This can be done by appending the data from one table to the other table or by creating a new table and adding the information from the two merging tables according to previously discussed embodiments of the present invention. This type of a join operation is different from other types ofjoin operations and will be referred to as a merge operation to distinguish it and to avoid any confusion.
Another type of join operation is the joining of the tuples from two tables that do not share the same structure (i.e., do not have all the same fields for their tuples). In this second type of join operation, tuples from the first or “left” table are associated with data from the second or “right” table according to some criteria. For example, a join operation for a table of insurance claims with a table of insurance policies may use an insurance policy identifier or insurance policy number to identify the insurance policy details to which each claim applies. According to this example, the resulting joined table will contain a tuple for each insurance claim but each tuple will also contain details about the insurance policy to which the insurance claim applies. Under these circumstances, the insurance policy number may be repeated in numerous claims (tuples) which apply to the same insurance policy. This may result in a loss of data normalization under the relational database model but this will not affect the join operation according to this embodiment of the present invention.
According to one embodiment of the present invention, joining the table for insurance policies with the table for insurance claims results in a new root node for each tuple with the root node sitting above the previous root nodes for the tuple data in the individual tables.
The above example for joining the two data sets is facilitated by the use of pointers as previously discussed according to at least one embodiment of the present invention. Because pointers are used, the underlying data (e.g., the insurance policy numbers 1503-1505 and associated number of instances 1513-1515 stored for the insurance policies data set 1510, 1550 and the insurance policy numbers 1523-1524 and associated number of instances 1533-1534 stored for the insurance claims data set 1530, 1560) remain unaffected by the joining of the data sets. Instead, the resulting joined table only needs to incorporate pointers to the data stored in memory or on a storage device according to one embodiment of the present invention.
One method of determining which tuples in the table data sets to be joined is to examine every possible pairing of data and determine whether it belongs in the resulting joined table. Implementing this type of process takes time proportional to n1*n2, where n1 and n2 are the sizes of the data sets (the number of patterns for the join field in the data set) for the insurance policies 1510, 1550 and the insurance claims 1530, 1560. Depending on the size of the data sets to be joined, the number of combinations that need to be tested according to this method can be potentially very large. For the example given in
There are a number of types of join operations used in database systems. One example is a equi-join or natural join where two tuples are joined where they both have a matching join “key”. The type of join operation illustrated in
The values t1 and t2 represent the two tables to be joined and f1 and f2 represent the shared fields (i.e., the join field or join key) according to this embodiment. The join size determination process is predicated on both data sets being in sorted order as the determination s1.patterns[i]<s2.patterns[j] would otherwise not function properly. This join size determination process applies to an equi-join and can be illustrated using the example join shown in
According to one embodiment of the present invention, the join process is similar to the join size determination process. The equi-join join process for two tables t1 and t2 with two shared fields f1 and f2 (i.e., the join field or join key) according to this embodiment is listed below.
The above equi-join join process is similar to the join size determination process previously discussed except that the tables are first sorted and no join count total is maintained. In addition, the add function is the joining of the matching tuples from both tables. As previously discussed, the sorting of the tables takes O(n1) steps to execute the first sort and O(n2) steps to execute the second sort. The while loop for the equi-join join process will take at most O(max(n1+n2, n3)) steps to complete where n1 and n2 are the sizes of the data sets to be joined and n3 is the size of the final joined data set. Because the first branch of the if statement combines the processing of the second and third branches of the if statement, every time a result uses this first branch the overall steps are reduced resulting in O(n1+n2) being the worst case number of steps to be performed for the equi-join join process.
An equi-join is only one example of a database join operation. For other types of join operations, variations of the above equi-join join process may be used to implement the other join operations according to one embodiment of the present invention. For example, another type of join operation may be termed a “left outer join” operation. In the equi-join example provided above and in
The join size determination is the same for both the equi-join operation and the “left outer join” operation except that for the “left outer join” the total is incremented for all patterns in the “left” data set—even for those where no match is found. The changes to the join size determination process (i.e., an additional line in this case) made from the equi-join operation to the “left outer join” operation is shown in bold above. A “left outer join” operation may be particularly useful where the “right” data set requires the existence of the join key or join field before allowing data to be included in the data set and table. For example, to add a claim to the insurance claim table, a requirement may be in place that the insurance policy number must already exist in the insurance policies table with the insurance policies table serving as the lookup table for the policy number in the insurance claims table. In this situation, it would not be possible to have a tuple in the insurance claims table (the “right” data set) that does not match a tuple in the table for the “left” data set though the table for the “left” data set may have tuples that do not match the data in the table for the “right” data set. In these circumstances, the “left outer join” operation is particularly useful in making sure that all tuples are included in the resulting joined table.
According to one embodiment of the present invention, a “left outer join” join process is shown below as a modification of the equi-join join process previously discussed.
The nullTree(t2) is a tuple or tree in the table for the second or “right” data set with NULL values for all the patterns in the tuple/tree. This NULL value tuple for the “right” data set is then joined to the tuple in the table for the “left” data set where the data (i.e., the join key or join field) in the tuple for the left data set does not match the data (i.e., the join key or join field) in the “right” data set. This is reflected in the second use of the add function. The additions to the equi-join join process to implement a “left outer join” join process are shown in bold.
The equi-join join operation and the “left outer join” operation are only two examples of possible join operations that may be implemented according to at least one embodiment of the present invention. Other join operations may be implemented in a similar manner using minor modification to the join size determination process and join process discussed above according to one embodiment of the present invention.
Archiving
The necessity to maintain increasingly greater amounts of data to comply with, for example, current government regulatory requirements, such as Sarbanes-Oxley and Basle 2, may create burdens that conventional archiving techniques are ill equipped to satisfy. According to one embodiment of the present invention, implementing the present invention as discussed above may provide a mechanism for successful meeting the archiving requirements that demand increasingly greater storage and rapid accessibility.
This embodiment of the present invention favorably addresses several archiving issues and criteria. For example, one challenge raised by archiving is the accessibility of the archived data. Having archived data readily accessible online over an information network (e.g., the Internet) may provide the best accessibility to the data. However, conventional archiving systems typically implement means further removing the archived data from online accessibility. Deploying tape storage based backup procedures, for example, are less practical as they further remove the archived data from rapid online access and may also be more failure prone than optical or hard disk online storage formats. This embodiment of the present invention reduces the amount of memory or storage space used for an archive thereby facilitating its storage on more immediately accessible media. This reduction in the amount of memory or storage space to store an archive coupled with the fully searchable (i.e., the user is able to query the archived data) nature of the archive provides significant advantages over conventional archives.
One particular advantage according to this embodiment of the present invention is that a compression in the data occurs without any data loss. The patterns themselves are uniquely stored while addresses to the patterns are used in the tree structure representation of each tuple in a table. In this manner, the amount of memory or storage space necessary for the data set of a table may be substantially reduced over other conventional database management systems. In this manner, an archive may be stored using less memory or storage than otherwise conventionally necessary.
Another particularly advantageous benefit of this embodiment of the present invention is obtained when archiving snapshots of data in a database. A snapshot is copy of the data that exists in the database at one particular point in time. Because a snapshot of data from a database will share a considerable amount of data with other snapshots of the database and with the current data in the database, it is possible to achieve greater archiving efficiency. As described above, the patterns in the data set of a table are uniquely stored and pointers to the patterns are used in the representation of the table. The overlap between the patterns used in one archived snapshot and another archived snapshot and between an archived snapshot and the current data set may be substantial. Therefore, the patterns for all the snapshots may be stored together in a single pattern set for the table (i.e., not broken down into separate pattern sets for each archived snapshot). A root indirection set as previously described may then be used for each archived snapshot to indicate the grouping of patterns for the tuples in the snapshot. In other words, adding an archive of data (e.g., a snapshot of the database) may be implemented by simply adding a root indirection set for the archive. In addition, the delete function described herein would instead of erasing data (according to one embodiment above) would instead remove a tuple from the root indirection set for the current table of the database. Also, the update function described herein would instead of erasing data (according to one embodiment above) would instead add any new patterns to the pattern set for the table and create a new entry to the root indirection set for the current table of the database. In this manner, the patterns used in the root indirection set for the archive are preserved even though they may no longer be reachable (i.e., used) in the current database.
In addition to this embodiment of the present invention, other advantages of this embodiment include:
The following example illustrates the memory or storage savings that may be achieved according to this embodiment of the present invention used for implementing multiple archives. According to this example, a table containing one million tuples (records) having a total size of 200 MB (200 bytes per tuple) is going to be archived according to this embodiment. Assuming that there is 80% compression (i.e., reduction in the amount of space necessary) resulting from the savings in memory or storage space that occurs when patterns are stored uniquely with pointers for each tuple pointing to the unique patterns, the archive of the data uses 40 MB of data to store the patterns. In addition, as previously discussed a root indirection set may then be used by the archive to indicate its component tuples. As previously discussed, a root indirection set may use four bytes per tuple in a 32-bit system resulting in 4 MB of space for the one million tuples in the root indirection set of the archive. Combined with the 40 MB of storage, the archive uses 44 MB of space (40 MB for the storage of the patterns and pointers and 4 MB for the root indirection set). If a second archive (e.g., a second snapshot of the table data) is generated where 10% of the patterns are different from (new over) the first archive, an additional 4 MB of memory or storage space is necessary for these different patterns in addition to the 4 MB for the root indirection set for the second archive resulting in 52 MB of space for both archives (44 MB for the first archive +4 MB extra patterns for the second archive (10% of 40 MB)+4 MB for the root indirection set for the second archive=52 MB). This 52 MB represents considerably less memory or storage space than the 88 MB (44 MB per archive) that would otherwise be required if the two snapshots were stored separately. This savings in memory or storage space is in addition to the savings that occur from storing the patterns uniquely and using pointers to point to the patterns. In a conventional archiving system, the above example may require 400 MB for both archives (200 MB for each archive) whereas this embodiment of the present invention not only reduces the memory or storage for the archives to 88 MB (44 MB each) but further reduces their size by sharing the duplicated patterns resulting in only 52 MB of memory or storage. Each additional snapshot that is added to the this archive further increases the memory or storage space savings according to this embodiment of the present invention.
As previously stated, an archive unlike a current database (the database that is in current use) may be moved to a persistent storage media other than an online memory structure or storage space for which the above embodiments have been described. This may raise an issue regarding the immutability of the data relating to the use of pointers to the memory addresses of patterns. If the database is move to a persistent storage structure, the address of the patterns changes. When the database is reloaded into a memory structure or other readily accessible storage structure, the pointers may no longer refer to accurate pattern addresses. In order to correct for this potential problem when moving an archive to another media, the addresses are transformed into offset values (i.e., relative addresses) indicating an offset position in memory or storage with the pointers referencing the offset values. When the archive is restored (reloaded), the offset values are transformed from a relative address back to an actual address with the pointers referring to the actual address. In this manner, problems with the addressing of patterns may be avoided. This embodiment may also be used when moving the current database as well. According to one embodiment of the present invention, active data may be kept in memory (which is more readily accessible) while less active data may be stored on a persistent storage media such a disk from which it may be loaded into memory when needed. The short delay resulting from the loading of data into memory may be acceptable considering the other performance gains and, in particular, with the less frequent accessing of the data not already in memory.
In another embodiment of the present invention, the use of snapshots as described above may be used to perform time series analysis on the data in the database. For example, each snapshot captures the data in the database at a particular period of time. Using specified snapshots that are stored together allows for changes in the data over time to be examined indicating trends. This may be accomplished by running queries on each snapshot desired for the analysis using those snapshots' root indirection sets. According to this embodiment, time series analysis may be performed without any extra aggregation and indexing of the data as may be required in conventional specialty database allowing time series analysis.
Claims
1. A method for identifying a new pattern for a table of a database, comprising:
- identifying a first subordinate pattern and a second subordinate pattern for a data pattern;
- determining if the first subordinate pattern is new, wherein the first subordinate pattern is compared to a first set of patterns;
- determining, where the first subordinate pattern is not new, if the second subordinate pattern is new, wherein the second subordinate pattern is compared to a second set of patterns;
- determining, where the first subordinate pattern is not new and the second subordinate pattern is not new, if the data pattern is new, wherein the data pattern is compared to a third set of patterns, the third set of patterns composed of at least one pattern pair containing a left pattern from the first set of patterns and a right pattern from the second set of patterns;
- storing the data pattern in the third set of patterns with a frequency value equal to one where at least one of the first subordinate pattern is new, the second subordinate pattern is new, and the data pattern is new; and
- incrementing the frequency value for the data pattern in the third set of patterns where the first subordinate pattern is not new, the second subordinate pattern is not new and the data pattern is not new.
2. A method for identifying a new pattern for a table of a database, comprising:
- identifying a first subordinate pattern and a second subordinate pattern for a data pattern;
- retrieving a first frequency value for the first subordinate pattern, wherein the first frequency value is one if the first subordinate pattern is new;
- retrieving, where the first frequency value is greater than one, a second frequency value for the second subordinate pattern, wherein the second frequency value is one if the second subordinate pattern is new;
- determining, where the first frequency value is greater than one and the second frequency value is greater than one, if the data pattern is new, wherein the data pattern is compared to a set of patterns;
- storing the data pattern in the set of patterns with a pattern frequency value equal to one where at least one of the first frequency value is one, the second frequency value is one, and the data pattern is new; and
- incrementing the pattern frequency value for the data pattern in the set of patterns where the first frequency value is greater than one and the second frequency value is greater than one and the data pattern is not new.
3. A method for designing a table of a database from a tree structure, comprising:
- determining a set of values for a field of the table;
- computing at least one of a cardinality and a correlation for the field, the correlation of the field determined in relation to a second field;
- grouping the field with the second field according to at least one of the computed cardinality and the computed correlation of the field;
- building a sub-tree for the grouping of the field with the second field;
- assembling a final tree from the sub-tree; and
- building the table from the final tree.
4. A method for designing a table of a database from a tree structure, comprising:
- determining at least one field for the table;
- computing the cardinality for the field;
- sorting the at least field into an ascending list based on the computed cardinality of the field;
- generating a tree from the ascending list;
- building the table from the tree.
5. A method for designing a table of a database from a tree structure, comprising:
- determining at least one field for the table;
- computing the cardinality for the field;
- sorting the at least field into an ascending list based on the computed cardinality of the field;
- grouping the field into a sub-list, wherein the grouping is based on a grouping value calculated by taking a logarithmic value of the cardinality for the field;
- building a sub-tree for the sub-list;
- assembling the sub-list into a final tree; and
- building the table from the final tree.
6. A method for representing at least one value from a table of a database, comprising:
- determining a set of values for each field of the table in the database, wherein the set of values does not include any duplicate values;
- adding a NULL value as a first value in the set of values for each field; and
- using a pointer for each field in a representation of a tuple in the table of the database, the pointer at least one of a) referencing the first value in the set of values for the field to indicate the NULL value, b) referencing the value in the set of values for the field, and c) encoding the value from the set of values for the field into the pointer.
7. A method for representing at least one value from a table of a database, comprising:
- determining whether a set of values for a field of the table in the database can be directly encoded by a pointer;
- generating the set of values for the field of the table in the database, wherein the set of values does not include any duplicate values and wherein the set of values for the field can not be directly encoded by the pointer as decided in the determining step;
- adding a NULL value as at least one of a) a first value in the set of values for the field wherein the set of values for the field can not be directly encoded by the pointer and b) a first pointer value in the set of directly encoded pointer values wherein the set of values for the field can be directly encoded by the pointer; and
- using the pointer for each field in a representation of a tuple in the table of the database, the pointer at least one of a) referencing the first value in the set of values for the field to indicate the NULL value, b) referencing the value in the set of values for the field, c) encoding the value from the set of values for the field into the pointer, and d) encoding the first pointer value in the set of directly encoded pointer values to indicate the NULL value.
8. A method for representing at least one data pattern in a table of a database, comprising:
- determining a set of patterns for a tuple of the table of the database, wherein the set of values does not include any duplicate patterns;
- adding a NULL pattern as a first pattern in the set of patterns for the tuple; and
- using a pointer for each pattern in a representation of the tuple in the table of the database, the pointer at least one of a) referencing the first pattern in the set of patterns for the tuple to indicate the NULL pattern, b) referencing the pattern in the set of patterns for the tuple, and c) encoding a pattern identifier for the pattern in the set of patterns into the pointer.
9. A method for evaluating an expression for a table of a database, comprising:
- determining a common path to a common node for a first field and a second field of the table, wherein the expression uses at least the first field and the second field as an argument;
- calculating and storing a value for the expression in a pattern at the common node where the pattern at the common node does not already contain the value for the expression; and
- retrieving the value for the expression from the pattern at the common node.
10. A method for including a derived field in a table of a database, comprising:
- including an expression as a pattern for the derived field in the table;
- evaluate the expression if a path to at least one of a node and a field in the table contains the expression;
- return a value for the path, the value reflecting at least one of the evaluated expression and a stored valued indicated by the path.
11. A method for sorting a table of a database according to a sort pattern and a sort order, comprising:
- allocating space for a sorted table, the space allocated in memory or storage;
- determining an offset value for each sort pattern in the table, the offset value determined as a function of the sort order and a number of instances for each sort pattern; and
- copying each row of the table to the allocated space, the position of the row in the sorted table determined as a function of the sort pattern of the row and the offset value for the sort pattern of the row, wherein the offset value for the sort pattern of the row is incremented after copying the row.
12. A method for sorting a table of a database according to a sort pattern and a sort order, comprising:
- allocating space for a sorted pointer list, the space allocated in memory or storage and the sorted pointer list containing enough space for a pointer to each row of the table;
- determining an offset value for each sort pattern in the table, the offset value determined as a function of the sort order and a number of instances for each sort pattern; and
- creating the pointer to each row of the table in the allocated space, the position of the pointer in the sorted pointer list determined as a function of the sort pattern of the row and the offset value for the sort pattern of the row, wherein the offset value for the sort pattern of the row is incremented after creating the pointer.
13. A method for sorting a subset of a table of a database according to a selection criteria, a sort pattern, and a sort order, comprising:
- allocating space for a sorted table, the space allocated in memory or storage;
- determining an offset value for each sort pattern in the table, the offset value determined as a function of the sort order and a number of instances for each sort pattern;
- comparing each row of table with the selection criteria to determine if the row meets the selection criteria;
- copying each row of the table that meets the selection criteria to the allocated space, the position of the row in the sorted table determined as a function of the sort pattern of the row and the offset value for the sort pattern of the row, wherein the offset value for the sort pattern of the row is incremented after copying the row; and
- consolidating the sorted table, wherein unused allocated space is removed from the sorted table.
14. A method for sorting a subset of a table of a database according to a selection criteria, a sort pattern, and a sort order, comprising:
- allocating space for a sorted pointer list, the space allocated in memory or storage and the sorted pointer list containing enough space for a pointer to each row of the table;
- determining an offset value for each sort pattern in the table, the offset value determined as a function of the sort order and a number of instances for each sort pattern;
- comparing each row of table with the selection criteria to determine if the row meets the selection criteria;
- creating the pointer in the allocated space for each row of the table that meets the selection criteria, the position of the pointer in the sorted pointer list determined as a function of the sort pattern of the row and the offset value for the sort pattern of the row, wherein the offset value for the sort pattern of the row is incremented after creating the pointer; and
- consolidating the sorted pointer list, wherein unused allocated space is removed from the sorted pointer list.
15. A method for sorting a table of a database according to an expression, comprising:
- determining at least one unique pattern for a sort field of the table, wherein the sort field is used as part of the expression;
- sorting the table into at least one bin as a function of the at least one unique pattern, wherein the at least one bin is a block of data having a same value of the sort field of the table;
- determining a key for the at least one bin, wherein the key is a result of the expression using the same value of the sort field for the bin;
- generating a sorted list of keys, wherein the sorted list of keys contains the key and the key corresponds to one bin; and
- reordering the at least one bin as a function of the sorted list of keys.
16. A method for sorting a table of a database according to an expression, comprising:
- determining at least one unique pattern for a first sort field of the table, wherein the first sort field is used as part of the expression;
- determining at least one unique pattern for a second sort field of the table, wherein the second sort field is used as part of the expression;
- sorting the table into at least one bin, the table sorted in a reverse sort order first as a function of the at least one unique pattern for the second sort field and second as a function of the at least one unique pattern for the first sort field, wherein the at least one bin is a block of data having a first same value of the first sort field of the table and a second same value of the second sort field of the table;
- determining a key for the at least one bin, wherein the key is a result of the expression using the first same value of the first sort field for the bin and the second same value of the second sort field for the bin;
- generating a sorted list of keys, wherein the sorted list of keys contains the key and the key corresponds to one bin; and
- reordering the at least one bin as a function of the sorted list of keys.
17. A method for sorting a table of a database according to a first expression and a second expression, comprising:
- determining at least one unique pattern for at least one first sort field of the table, wherein the at least one first sort field is used as part of the first expression;
- determining at least one unique pattern for at least one second sort field of the table, wherein the at least one second sort field is used as part of the second expression;
- sorting the table first as function of the second expression and second as a function of the first expression, the table first sorted as a function of the at least one unique pattern for the at least one second sort field of the second expression and second as function of the at least one unique pattern for the at least one first sort field of the first expression.
18. A method for partitioning a table of a database as a function of at least one partition field, comprising:
- sorting the table according to the at least one partition field; and
- generating a root indirection set for each value of the at least one partition field, the root indirection set containing at least one pointer to a tuple in the table wherein the value of the at least one partition field for the root indirect set matches the value of the at least one partition field for the tuple.
19. A method for joining a first table and a second table of a database as a function of at least one join field, comprising:
- sorting the first table according to the at least one join field;
- sorting the second table according to the at least one join field;
- matching a tuple in the first table with a tuple in the second table, wherein a value for the at least one join field of the tuple in the first table is equal to a value for the at least one join field of the tuple in the second table; and
- generating a joined table as a function of the matching tuple in the first table and the matching tuple in the second table.
20. A method for storing a snapshot of data in a database, comprising:
- creating a root indirection set for the snapshot of data;
- maintaining a deleted pattern in the database, wherein the deleted pattern is not removed from a pattern set for a node in a table of the database when the pattern is deleted in the database; and
- providing access to the snapshot of data using the created root indirection set, the created root indirection set accessing the deleted pattern.
21. A method for performing a time series analysis on data in a database, comprising:
- creating a root indirection set for each snapshot of data to be used in the time series analysis, wherein current data is treated as an already existing snapshot for the time series analysis;
- designating a plurality of snapshots of data to be used in the time series analysis, wherein the root indirection sets of the snapshots of data are identified;
- generating a query for the time series analysis, the query to be executed on all the designated snapshots of data; and
- comparing query results as part of the of the time series analysis.
Type: Application
Filed: Oct 4, 2005
Publication Date: May 18, 2006
Inventors: Andy Ben-Dyke (Worcestershire), Tom Longshaw (Worcestershire), Gary Pratley (Gloucester), Keith Summers (Herefordshire)
Application Number: 11/244,347
International Classification: G06F 7/00 (20060101);
