Leapfrog tree-join
View Patent ↗A method for joining records from database tables is proposed. Join attributes are ordered into a sequence S 0 , and a scope C is initialized to empty. Tables including a join attribute A that is first in S 0 are identified. A new sequence S′ omits A from S 0 . Iteration through each identified table proceeds in join attribute A order. The iteration utilizes a tree index structure for each identified table where the index key includes all join attributes in the identified table in S 0 order. For each matched value v of the join attribute located during the iterating, a new scope C v is created by appending “A=v” to the scope C. If S′ is empty, a tuple corresponding to the scope C v is output. Otherwise, S 0 is set to S′ and for each scope C v , C is set to C v and the steps beginning with the identifying step are recursively invoked.
1. A computer implemented method to join records from plurality of database tables, the method comprising:
receiving, by at least one processor, a set of at least three database tables stored in at least one first memory, and a set of join conditions on a set S of join attributes;
initializing, by the at least one processor and in at least one second memory, a scope C to empty;
selecting, by the at least one processor, join attribute A from an ordered sequence S.sub.0 of the join attributes, wherein the join attribute A is the first in the ordered sequence S.sub.0;
identifying, by the at least one processor, tables in the set of tables that include the join attribute A;
iterating, by the at least one processor, through each of the identified tables in join attribute A order, the iterating based on a tree index structure held in at least one third memory for each of the identified tables where an index key includes all join attributes in the identified table in S.sub.0 order, and wherein the iterating includes seeking subsequent rows in each of the identified tables using the tree index structure, the at least one third memory having faster access times than the at least one first memory;
for each matched value v of the join attribute located during the iterating,
creating, by the at least one processor and in the at least one second memory, a new scope C.sub.v by appending “A=v” to the scope C; and
eliminating, by the at least one processor, the join attribute A from the ordered sequence S.sub.0;
in response to S.sub.0 being empty, outputting, by the at least one processor, a tuple corresponding to the scope C.sub.v for each v; and
in response to S.sub.0 containing a next join attribute, for each scope C.sub.v, setting, by the at least one processor, C to C.sub.v selecting the next join attribute as the join attribute A for recursively invoking the identifying, creating, iterating, appending, outputting and recursively invoking.
2. The computer implemented method of claim 1 , wherein the ordered sequence S.sub.0 is a non-prefix-coherent ordered sequence.
3. The computer implemented method of claim 1 , wherein rows in the database tables that meet a filtering condition are skipped by the iterating.
4. The computer implemented method of claim 1 , further comprising building the tree index structure.
5. The computer implemented method of claim 1 , wherein the tree index structure is a B-tree structure.
6. The computer implemented method of claim 1 , wherein the tree index structure is a tree structure.
7. The computer implemented method of claim 1 , wherein a non-join attribute value from a database table is output along with the tuple.
8. The computer implemented method of claim 1 , wherein a query optimizer is utilized to determine the ordered sequence S.sub.0.
9. An apparatus for joining records from a plurality of database tables, the apparatus comprising:
at least one memory having computer readable computer instructions; and
a processor for executing the computer readable instructions to perform a method comprising:
receiving a set of at least three database tables stored in at least one table memory of the at least one memory, and a set of join conditions on a set S of join attributes;
initializing, in the at least one scope memory of the at least one memory, a scope C to empty;
joining records according to each join attribute A in an ordered sequence S.sub.0, which is an ordered sequence of the join attributes S, by:
identifying tables in the set of tables that include the join attribute A; dropping the join attribute A from S.sub.0;
iterating through each of the identified tables in join attribute A order, the iterating based on a tree index structure held in at least one index memory for each of the identified tables where the index key includes all join attributes in the identified table in S.sub.0 order, and wherein the iterating includes seeking subsequent rows in each of the identified tables using the tree index structure, the at least one index memory having faster access times than the at least one table memory;
for each matched value v of the join attribute located during the iterating, creating, in the at least one scope memory, a new scope C.sub.v by appending “A=v” to the scope C; and
for each scope C.sub.v, setting C to C.sub.v and recursively invoking the identifying, creating, iterating, appending, outputting and recursively invoking; and in response to the records being joined according to each attribute in the ordered sequence S.sub.0, outputting a tuple corresponding to the scope C.sub.v for each v.
10. The apparatus of claim 9 , wherein the ordered sequence S.sub.0 is a non-prefix-coherent ordered sequence, and the join attribute A is the first join attribute in S.sub.0.
11. The apparatus of claim 9 , wherein rows in the database tables that meet a filtering condition are skipped by the iterating.
12. The apparatus of claim 9 , wherein the method further comprises building a tree index structure.
13. The apparatus of claim 9 , wherein the tree index structure is a B-tree structure.
14. The apparatus of claim 9 , wherein the tree index structure is a tree structure.
15. The apparatus of claim 9 , wherein a non-join attribute value from a database table is output along with the tuple.
16. The apparatus of claim 9 , wherein a query optimizer is utilized to determine the ordered sequence.
17. A non-transitory computer readable storage medium for system for joining records from plurality of database tables, the computer program product comprising:
a computer readable storage medium having computer-readable program code embodied thereon, which when executed by a computer processor, causes the computer processor to implement a method, the method comprising:
receiving a set of at least three database tables stored in at least one first memory, and a set of join conditions on a set S of join attributes;
initializing, in at least one second memory, a scope C to empty;
joining records according to each join attribute in ordered sequence S.sub.0, which is an ordered sequence of the join attributes S, by:
identifying tables in the set of tables that include a join attribute A that is first in the ordered sequence S.sub.0;
dropping the join attribute A from S.sub.0;
iterating through each of the identified tables in join attribute A order, the iterating based on a tree index structure held in at least one third memory for each of the identified tables where the index key includes all join attributes in the identified table in S.sub.0 order, and wherein the iterating includes seeking subsequent rows in each of the identified tables using the tree index structure, the at least one third memory having faster access times than the at least one first memory;
for each matched value v of the join attribute located during the iterating, creating, in the at least one second memory, a new scope C.sub.v by appending “A=v” to the scope C; and
for each scope C.sub.v, setting C to C.sub.v and recursively invoking the identifying, creating, iterating, appending, outputting and recursively invoking; and in response to the records being joined according to each attribute in the ordered sequence S.sub.0, outputting a tuple corresponding to the scope C.sub.v for each v.
18. The computer program product of claim 17 , wherein the ordered sequence S.sub.0 is a non-prefix-coherent ordered sequence.
19. The computer program product of claim 17 , wherein rows in the database tables that meet a filtering condition are skipped by the iterating.
20. The computer program product of claim 17 , wherein the tree index structure is a tree structure.
21. The computer program product of claim 17 , wherein a non-join attribute value from a database table is output along with the tuple.
22. The computer program product of claim 17 , wherein a query optimizer is utilized to determine the ordered sequence.