Pushing joins across a union

Abstract

Selecting a join plan for a query containing a join and a union block includes determining whether to propose a join plan with the join pushed across the union block. A selection is made between a join plan in which the join is not pushed across the union block and any proposed join plan in which the join is pushed across the union block.

1 . A method for selecting a join plan for a query containing a join and a union block, the method comprising:
 determining that the join is an inner join;   in response, determining that the union block is a UNION ALL; and   in response, proposing a join plan with the join pushed across the union block;   selecting between:
 the join plan with the join pushed across the union block; and 
 a join plan without the join being pushed across the union block. 
   
   
   
       2 . A method for selecting a join plan for a query containing a join and a union block, the method comprising:
 determining whether to propose a join plan with the join pushed across the union block; and   selecting between a join plan in which the join is not pushed across the union block and any proposed join plan in which the join is pushed across the union block.   
   
   
       3 . The method of  claim 2  wherein determining whether to propose a join plan with the join pushed across the union block comprises considering whether the join is an inner join or an outer join. 
   
   
       4 . The method of  claim 2  wherein determining whether to propose a join plan with the join pushed across the union block comprises considering whether the union block is a UNION ALL or a UNION (distinct). 
   
   
       5 . The method of  claim 2  wherein the join is an inner join, the union block comprises a UNION (distinct) having branches, and determining whether to propose a join plan with the join pushed across the union block comprises considering whether the join increases the cardinality of the branches of the union block. 
   
   
       6 . The method of  claim 2  wherein the join is an outer join, the union block comprises branches, and determining whether to propose a join plan with the join pushed across the union block comprises considering whether the join increases the cardinality of the branches of the union block. 
   
   
       7 . A method for selecting a join plan for a query containing a plurality of joins and a union block, the method comprising:
 selecting from among the plurality of joins a subset to consider for pushing across the union block;   identifying from among the selected joins N joins for which pushing the join across the union block is semantically correct;   proposing a join plan for all of the plurality of joins without any of the joins being pushed across the union block;   proposing 2**N separate join plans for the plurality of joins, each of the 2**N separate join plans comprising a unique combination of pushed joins and unpushed joins;   selecting a join plan from among the proposed join plans.   
   
   
       8 . The method of  claim 7  wherein selecting from among the plurality of joins a subset to consider for pushing across the union block comprises:
 determining that the number of joins in the plurality of joins is too great to propose a join plan for each of the possible alternatives of pushing one of the plurality of joins across the union block or not; and   in response, applying a set of heuristics to select fewer than all of the plurality of joins to consider for pushing across the union block.   
   
   
       9 . The method of  claim 7  wherein the subset is all of the plurality of joins. 
   
   
       10 . The method of  claim 7  wherein the subset is a proper subset of the plurality of joins. 
   
   
       11 . The method of  claim 7  wherein identifying the selected joins that should be pushed across the union block comprises:
 determining that one of the selected joins comprises an inner join;   in response, determining that the union block comprises a UNION ALL; and   in response, identifying the one of the selected joins as an identified join.   
   
   
       12 . The method of  claim 7  wherein the union comprises a plurality of branches, each of the plurality of branches has a cardinality, and wherein identifying the selected joins that should be pushed across the union block comprises:
 determining that one of the selected joins comprises an inner join;   in response, determining that the union block comprises a UNION (distinct);   in response, determining that pushing the one of the selected joins will not increase the cardinality of any of the plurality of branches of the union block; and   in response, identifying the one of the selected joins as an identified join.   
   
   
       13 . The method of  claim 7  wherein the union comprises a plurality of branches, each of the plurality of branches has a cardinality, and wherein identifying the selected joins that should be pushed across the union block comprises:
 determining that one of the selected joins comprises an inner join;   in response, determining that the union block comprises a UNION (distinct);   in response, determining that pushing the one of the selected joins will increase the cardinality of any of the plurality of branches of the union block; and   in response, rejecting the one of the selected joins as an identified join.   
   
   
       14 . The method of  claim 7  wherein identifying the selected joins that should be pushed across the union block comprises:
 determining that one of the selected joins comprises an outer join having an outer table and an inner table;   in response, determining that the union block is the outer table;   in response, identifying the one of the selected joins as an identified join.   
   
   
       15 . The method of  claim 7  wherein identifying the selected joins that should be pushed across the union block comprises:
 determining that one of the selected joins comprises an outer join having an outer table and an inner table;   in response, determining that the union block is the inner table; and   in response, rejecting the one of the selected joins as an identified join.