Query system

1 . A computer-implemented method comprising:

receiving, using one or more processors, a set operation query including:

a join type operator,

a union type operator,

an identification of a first queried table, a second queried table, and a third queried table, and

wherein the first query table and the second queried table are unioned tables;

adding, using the one or more processors, a plurality of set operator nodes to a query tree, the plurality of set operator nodes including a first set operator node associated with the join type operator and a second set operator node associated with the union type operator;

inserting, using the one or more processors, a first retrieval node into the query tree associated with the first queried table, a second retrieval node associated with the second queried table, and a third retrieval node associated with the third queried table,

wherein the first retrieval node corresponds to an index scan of the first queried table using one or more real indices associated with the first queried table,

wherein the second retrieval node is associated with the second queried table and one of: a same order of index scan as the first retrieval node, a different order of index scan than that of the first retrieval node, and a table scan, and

wherein the second set operator node, the first retrieval node, and the second retrieval node comprise a union subtree; and

selecting, using the one or more processors, the query tree based on a cost, wherein a portion of the cost associated with the union subtree of the query tree is based on a sum of one or more real indices associated with the union subtree.

2 . The computer-implemented method of claim 1 including:

determining a largest order real index among the unioned tables associated with the second set operator node associated with the union type operator; and

responsive to determining the first retrieval node associated with the first queried table is associated with a higher order index than the second retrieval node, inserting a restriction node between the second retrieval node and the second set operator node associated with the union type operator.

3 . The computer-implemented method of claim 2 , wherein the restriction node has an order equal to the difference between the largest order real index among the unioned tables associated with the second set operator node and the order of the real index associated with the first queried table.

4 . The computer-implemented method of claim 2 , wherein the second retrieval node is a table scan retrieval node when the second queried table lacks a real index and is an index scan node when the second queried table includes a real index.

5 . The computer-implemented method of claim 1 , wherein the union type operator is one or more of a union and union all.

6 . The computer-implemented method of claim 1 , wherein the join type operator is associated with a root node in the query tree.

7 . The computer-implemented method of claim 1 , wherein the second set operator node associated with the union type operator, when executed, uses a dynamically generated virtual index, wherein the dynamically generated virtual index does not persist after the set operation query is processed.

8 . The computer-implemented method of claim 1 , wherein the join type operator includes one or more of an inner join, left outer join, right outer join, full outer join, and cross join.

9 . The computer-implemented method of claim 1 including:

calculating the cost associated with the query tree, wherein the second set operator node associated with the union type operator uses a virtual index.

10 . The computer-implemented method of claim 1 including:

determining, responsive to comparing the cost associated with the query tree to a cost associate with another query tree, that the cost associated with the query tree has a lower cost; and

executing the query based on the query tree.

11 . A system comprising:

one or more processors; and

a memory storing instructions that, when executed by the one or more processors, cause the system to:

receive a set operation query including:

a join type operator,

a union type operator,

an identification of a first queried table, a second queried table, and a third queried table, and

wherein the first query table and the second queried table are unioned tables;

add a plurality of set operator nodes to a query tree, the plurality of set operator nodes including a first set operator node associated with the join type operator and a second set operator node associated with the union type operator;

insert a first retrieval node into the query tree associated with the first queried table, a second retrieval node associated with the second queried table, and a third retrieval node associated with the third queried table,

wherein first retrieval node corresponds to an index scan of the first queried table using one or more real indices associated with the first queried table,

wherein the second retrieval node is associated with the second queried table and one of: a same order of index scan as the first retrieval node, a different order of index scan than that of the first retrieval node, and a table scan, and

wherein the second set operator node, the first retrieval node, and the second retrieval node comprise a union subtree; and

select the query tree based on a cost, wherein a portion of the cost associated with the union subtree of the query tree is based on a sum of one or more real indices associated with the union subtree.

12 . The system of claim 11 , wherein the instructions, when executed, cause the system to:

determine a largest order real index among the unioned tables associated with second set operator node associated with the union type operator; and

responsive to determining the first retrieval node associated with the first queried table is associated with a higher order index than the second retrieval node, insert a restriction node between the second retrieval node and the second set operator node associated with the union type operator.

13 . The system of claim 12 , wherein the restriction node has an order equal to the difference between the largest order real index among the unioned tables associated with the second set operator node and the order of the real index associated with the first queried table.

14 . The system of claim 12 , wherein the second retrieval node is a table scan retrieval node when the second queried table lacks a real index and is an index scan node when the second queried table includes a real index.

15 . The system of claim 11 , wherein the union type operator is one or more of a union and union all.

16 . The system of claim 11 , wherein the join type operator is associated with a root node in the query tree.

17 . The system of claim 11 , wherein the second set operator node associated with the union type operator, when executed, uses a dynamically generated virtual index, wherein the dynamically generated virtual index does not persist after the set operation query is processed.

18 . The system of claim 11 , wherein the join type operator includes one or more of an inner join, left outer join, right outer join, full outer join, and cross join.

19 . The system of claim 11 , wherein the instructions, when executed, cause the system to:

calculate the cost associated with the query tree, wherein the second set operator node associated with the union type operator uses a virtual index.

20 . The system of claim 11 , wherein the instructions, when executed, cause the system to:

determining, responsive to comparing the cost associated with the query tree to a cost associate with another query tree, that the cost associated with the query tree has a lower cost; and

executing the query based on the query tree.