6 Replies Latest reply: Dec 23, 2013 4:20 AM by 933417 RSS

    Repalcing UNIOn with full outer Join

    933417

      I have a query wihch looks like ,

       

      select colA,colB ,colC, coldD from A,B,C,D
      where (some join conditions)
      union
      select colA,colB ,colC, NULL from A,B,C
      where (some join conditions)

       

      This query is posing us serious performance issues and we want to tune the query.

      SO to remove the UNION operator we want to use FULL JOIN.

       

      Can you please let us know how to frame the full outer join in the above query,so that the result set intact.

       

       

      Thanks Much

        • 1. Re: Repalcing UNIOn with full outer Join
          Brian Bontrager

          Not given the information provided.  Sample tables and current output to compare to would be helpful.  Not knowing the current join conditions makes it hard (read:impossible) to formulate the proper full outer join conditions.  An example of the full outer join you have tried, and some idea of what is not working the way you expect when you tried it would be ideal.

          • 2. Re: Repalcing UNIOn with full outer Join
            Hoek

            A simple example might give some ideas:

             

            SQL> select * from t1;

             

                  COL1       COL2

            ---------- ----------

                     1         10

                     2         20

                     3         30

                     4         40

                     5         50

                     6         60

                     7         70

                     8         80

                     9         90

                    10        100

             

            10 rows selected.

             

            SQL> select * from t2;

             

                  COL1       COL2

            ---------- ----------

                     1       1000

                     2       2000

                     3       3000

                     4       4000

                     5       5000

                     6       6000

                     7       7000

                     8       8000

                     9       9000

                    10      10000

             

            10 rows selected.

             

            SQL> select t1.col2

              2  from   t1

              3  ,      t2

              4  where  t1.col1 = t2.col1

              5  union

              6  select t2.col2

              7  from   t1

              8  ,      t2

              9  where  t1.col1 = t2.col1;

             

                  COL2

            ----------

                    10

                    20

                    30

                    40

                    50

                    60

                    70

                    80

                    90

                   100

                  1000

                  2000

                  3000

                  4000

                  5000

                  6000

                  7000

                  8000

                  9000

                 10000

             

            20 rows selected.

             

            SQL> --

            SQL> select nvl(t1.col2, t2.col2)

              2  from   t1

              3         full outer join

              4         t2

              5  on     t1.col2 = t2.col2

              6  order by 1;

             

            NVL(T1.COL2,T2.COL2)

            --------------------

                              10

                              20

                              30

                              40

                              50

                              60

                              70

                              80

                              90

                             100

                            1000

                            2000

                            3000

                            4000

                            5000

                            6000

                            7000

                            8000

                            9000

                           10000

             

            20 rows selected.

            • 3. Re: Repalcing UNIOn with full outer Join
              Karthick_Arp

              933417 wrote:

               

              This query is posing us serious performance issues and we want to tune the query.

              SO to remove the UNION operator we want to use FULL JOIN.

               

              How you came to a conclusion that FULL JOIN will be the solution for your issue. I would suggest you read When your query takes too long ... It would give you a good idea on how to narrow down your performance issue and identify the root cause.

              • 4. Re: Repalcing UNIOn with full outer Join
                933417

                  Here goes my first query that uses UNION :

                SELECT  from_it.seq_routing,

                       from_it.milestone_routing_dt,

                       from_it.milestone_type_desc,

                       to_it.seq_routing,

                       to_it.milestone_routing_dt,

                       to_it.milestone_type_desc,

                       to_it.delay_flag,

                
                
                

                       to_it.acceptable_delay_flag,

                       TAB_A.calendar_minutes, TAB_A.business_minutes,

                       TAB_A.due_date, TAB_A.late_minutes,

                       TAB_A.delay_concat_comments,TAB_C.TAB_C_key,

                       TAB_C.pcd,TAB_C.ssn,

                       mmp.MEASURE_NAME,

                       api.METRICS_PARAMETER_KEY,

                api.METRICS_PARAMETER_PRODUCT_KEY,

                crt.JUSTIFICATION_CD,

                crt.JUSTIFICATION_DESC,

                crt.ROUTING_USER_SITE_DESC

                  FROM TAB_A, TAB_B from_it, TAB_B to_it,

                  TAB_C, TAB_E,TAB_D mmp,TAB_F api, TAB_G crt

                WHERE from_it.TAB_B_key =

                                                  TAB_A.from_TAB_B_key

                   AND to_it.TAB_B_key =

                                                    TAB_A.to_TAB_B_key

                   ANDTAB_C.TAB_C_key = from_it.TAB_C_key

                   andTAB_C.TAB_C_key = to_it.TAB_C_key

                   and TAB_E.pck =crt.pck

                   and to_it.SEQ_ROUTING  =crt.SEQ_ROUTING

                   and TAB_E.pcd =TAB_C.pcd

                   and TAB_E.ssn =TAB_C.ssn

                   andTAB_C.END_DT = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                   and TAB_A.METRICS_MEASURE_KEY =mmp.METRICS_MEASURE_KEY

                   andapi.END_DT = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                   andTAB_C.TAB_C_key =api.TAB_C_KEY

                   andapi.PARAMETER_TYPE = 'IN'

                and TAB_A.end_dt = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                and CRT.end_dt  = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                UNION

                SELECT  from_it.seq_routing,

                       from_it.milestone_routing_dt,

                       from_it.milestone_type_desc,

                       to_it.seq_routing,

                       to_it.milestone_routing_dt,

                       to_it.milestone_type_desc,

                       to_it.delay_flag,

                       to_it.acceptable_delay_flag,

                       TAB_A.calendar_minutes, TAB_A.business_minutes,

                       TAB_A.due_date, TAB_A.late_minutes,

                       TAB_A.delay_concat_comments,TAB_C.TAB_C_key,

                       TAB_C.pcd,TAB_C.ssn,

                       mmp.MEASURE_NAME,

                       api.METRICS_PARAMETER_KEY,

                api.METRICS_PARAMETER_PRODUCT_KEY,

                NULL, NULL, NULL

                  FROM TAB_A, TAB_B from_it, TAB_B to_it,

                  TAB_C, TAB_E,TAB_D mmp,TAB_F api

                WHERE from_it.TAB_B_key =

                                                  TAB_A.from_TAB_B_key

                   AND to_it.TAB_B_key =

                                                    TAB_A.to_TAB_B_key

                   ANDTAB_C.TAB_C_key = from_it.TAB_C_key

                   andTAB_C.TAB_C_key = to_it.TAB_C_key

                   and TAB_E.pcd =TAB_C.pcd

                   and TAB_E.ssn =TAB_C.ssn

                   andTAB_C.END_DT = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                   and TAB_A.METRICS_MEASURE_KEY =mmp.METRICS_MEASURE_KEY

                   andapi.END_DT = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                   andTAB_C.TAB_C_key =api.TAB_C_KEY

                   andapi.PARAMETER_TYPE = 'IN'

                   and to_it.SEQ_ROUTING is null

                and TAB_A.end_dt = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                 

                And the one with OUTER JOIN used :

                 

                SELECT  from_it.seq_routing,

                       from_it.milestone_routing_dt,

                       from_it.milestone_type_desc,

                       to_it.seq_routing,

                       to_it.milestone_routing_dt,

                       to_it.milestone_type_desc,

                       to_it.delay_flag,

                       to_it.acceptable_delay_flag,

                       TAB_A.calendar_minutes, TAB_A.business_minutes,

                       TAB_A.due_date, TAB_A.late_minutes,

                       TAB_A.delay_concat_comments,TAB_C.TAB_C_key,

                       TAB_C.pcd,TAB_C.ssn,

                       mmp.MEASURE_NAME,

                       api.METRICS_PARAMETER_KEY,

                api.METRICS_PARAMETER_PRODUCT_KEY,

                crt.JUSTIFICATION_CD,

                crt.JUSTIFICATION_DESC,

                crt.ROUTING_USER_SITE_DESC

                  FROM TAB_A  JOIN

                  TAB_B from_it

                  on  from_it.TAB_B_key =

                                                  TAB_A.from_TAB_B_key

                                  and TAB_A.end_dt = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                         JOIN

                  TAB_B to_it

                  ON to_it.TAB_B_key =

                                                    TAB_A.to_TAB_B_key

                                    JOINTAB_C

                ONTAB_C.TAB_C_key = from_it.TAB_C_key

                ANDTAB_C.TAB_C_key = to_it.TAB_C_key    

                andTAB_C.END_DT = TO_DATE('12/31/9999', 'MM/DD/YYYY')                

                  JOINTAB_D mmp

                  ON TAB_A.METRICS_MEASURE_KEY =mmp.METRICS_MEASURE_KEY

                  JOIN TAB_E

                  ON  TAB_E.pcd =TAB_C.pcd

                   and TAB_E.ssn =TAB_C.ssn

                  -- and TAB_E.ssn = 'PFEARG'

                JOINTAB_F api

                ON   TAB_C.TAB_C_key =api.TAB_C_KEY

                   andapi.PARAMETER_TYPE = 'IN'

                   andapi.END_DT = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                  LEFT outer join  TAB_G crt

                  on  TAB_E.pck =crt.pck

                  and  to_it.SEQ_ROUTING  =crt.SEQ_ROUTING

                  and  CRT.end_dt  = TO_DATE('12/31/9999', 'MM/DD/YYYY')

                 

                But the queries are differing when it comes to result set.

                 

                To add,

                here is the first query explain plan

                (

                Cost: 93,907  Bytes: 7,245  Cardinality: 8    

                                       

                ----------------------------------------------------------------------------------------------------------------------------------------------------

                | Id  | Operation                                | Name                       | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

                ----------------------------------------------------------------------------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT                         |                            |     7 | 28056 | 46951 |       |       |        |      |            |

                |   1 |  PX COORDINATOR                          |                            |       |       |       |       |       |        |      |            |

                |   2 |   PX SEND QC (RANDOM)                    | :TQ10009                   |     7 | 28056 | 46951 |       |       |  Q1,09 | P->S | QC (RAND)  |

                |   3 |    NESTED LOOPS OUTER                    |                            |     7 | 28056 | 46951 |       |       |  Q1,09 | PCWP |            |

                |   4 |     VIEW                                 |                            |     7 | 26432 | 46951 |       |       |  Q1,09 | PCWP |            |

                |   5 |      HASH JOIN                           |                            |     7 |  6216 | 46951 |       |       |  Q1,09 | PCWP |            |

                |   6 |       PX RECEIVE                         |                            |       |       |       |       |       |  Q1,09 | PCWP |            |

                |   7 |        PX SEND HASH                      | :TQ10008                   |       |       |       |       |       |  Q1,08 | P->P | HASH       |

                |   8 |         BUFFER SORT                      |                            |     7 | 28056 |       |       |       |  Q1,08 | PCWP |            |

                |   9 |          NESTED LOOPS                    |                            |       |       |       |       |       |  Q1,08 | PCWP |            |

                |  10 |           NESTED LOOPS                   |                            |     1 |   700 |  3806 |       |       |  Q1,08 | PCWP |            |

                |  11 |            HASH JOIN                     |                            |     1 |   205 |  3806 |       |       |  Q1,08 | PCWP |            |

                |  12 |             JOIN FILTER CREATE           | :BF0000                    |   140K|    21M|  2932 |       |       |  Q1,08 | PCWP |            |

                |  13 |              PX RECEIVE                  |                            |   140K|    21M|  2932 |       |       |  Q1,08 | PCWP |            |

                |  14 |               PX SEND HASH               | :TQ10006                   |   140K|    21M|  2932 |       |       |  Q1,06 | P->P | HASH       |

                |  15 |                HASH JOIN BUFFERED        |                            |   140K|    21M|  2932 |       |       |  Q1,06 | PCWP |            |

                |  16 |                 JOIN FILTER CREATE       | :BF0001                    |   164K|    10M|  1575 |       |       |  Q1,06 | PCWP |            |

                |  17 |                  PX RECEIVE              |                            |   164K|    10M|  1575 |       |       |  Q1,06 | PCWP |            |

                |  18 |                   PX SEND HASH           | :TQ10004                   |   164K|    10M|  1575 |       |       |  Q1,04 | P->P | HASH       |

                |  19 |                    PX BLOCK ITERATOR     |                            |   164K|    10M|  1575 |    30 |    30 |  Q1,04 | PCWC |            |

                |  20 |                     TABLE ACCESS FULL    | TAB_A           |   164K|    10M|  1575 |    30 |    30 |  Q1,04 | PCWP |            |

                |  21 |                 PX RECEIVE               |                            |   974K|    84M|  1357 |       |       |  Q1,06 | PCWP |            |

                |  22 |                  PX SEND HASH            | :TQ10005                   |   974K|    84M|  1357 |       |       |  Q1,05 | P->P | HASH       |

                |  23 |                   JOIN FILTER USE        | :BF0001                    |   974K|    84M|  1357 |       |       |  Q1,05 | PCWP |            |

                |  24 |                    HASH JOIN BUFFERED    |                            |   974K|    84M|  1357 |       |       |  Q1,05 | PCWP |            |

                |  25 |                     HASH JOIN            |                            |   270K|    12M|   483 |       |       |  Q1,05 | PCWP |            |

                |  26 |                      PX RECEIVE          |                            |   275K|  6198K|     6 |       |       |  Q1,05 | PCWP |            |

                |  27 |                       PX SEND HASH       | :TQ10001                   |   275K|  6198K|     6 |       |       |  Q1,01 | P->P | HASH       |

                |  28 |                        PX BLOCK ITERATOR |                            |   275K|  6198K|     6 |       |       |  Q1,01 | PCWC |            |

                |  29 |                         TABLE ACCESS FULL| TAB_F |   275K|  6198K|     6 |       |       |  Q1,01 | PCWP |            |

                |  30 |                      PX RECEIVE          |                            |   269K|  7106K|   477 |       |       |  Q1,05 | PCWP |            |

                |  31 |                       PX SEND HASH       | :TQ10002                   |   269K|  7106K|   477 |       |       |  Q1,02 | P->P | HASH       |

                |  32 |                        PX BLOCK ITERATOR |                            |   269K|  7106K|   477 |    30 |    30 |  Q1,02 | PCWC |            |

                |  33 |                         TABLE ACCESS FULL| TAB_C                 |   269K|  7106K|   477 |    30 |    30 |  Q1,02 | PCWP |            |

                |  34 |                     PX RECEIVE           |                            |  1146K|    44M|   873 |       |       |  Q1,05 | PCWP |            |

                |  35 |                      PX SEND HASH        | :TQ10003                   |  1146K|    44M|   873 |       |       |  Q1,03 | P->P | HASH       |

                |  36 |                       PX BLOCK ITERATOR  |                            |  1146K|    44M|   873 |     1 |    30 |  Q1,03 | PCWC |            |

                |  37 |                        TABLE ACCESS FULL | TAB_B|  1146K|    44M|   873 |     1 |    30 |  Q1,03 | PCWP |            |

                |  38 |             PX RECEIVE                   |                            |  1146K|    49M|   873 |       |       |  Q1,08 | PCWP |            |

                |  39 |              PX SEND HASH                | :TQ10007                   |  1146K|    49M|   873 |       |       |  Q1,07 | P->P | HASH       |

                |  40 |               JOIN FILTER USE            | :BF0000                    |  1146K|    49M|   873 |       |       |  Q1,07 | PCWP |            |

                |  41 |                PX BLOCK ITERATOR         |                            |  1146K|    49M|   873 |     1 |    30 |  Q1,07 | PCWC |            |

                |  42 |                 TABLE ACCESS FULL        |TAB_B|  1146K|    49M|   873 |     1 |    30 |  Q1,07 | PCWP |            |

                |  43 |            INDEX UNIQUE SCAN             | PK_TAB_D |     1 |       |     0 |       |       |  Q1,08 | PCWP |            |

                |  44 |           TABLE ACCESS BY INDEX ROWID    | TAB_D |     1 |   495 |     1 |       |       |  Q1,08 | PCWP |            |

                |  45 |       BUFFER SORT                        |                            |       |       |       |       |       |  Q1,09 | PCWC |            |

                |  46 |        PX RECEIVE                        |                            |    24M|  4363M| 43144 |       |       |  Q1,09 | PCWP |            |

                |  47 |         PX SEND HASH                     | :TQ10000                   |    24M|  4363M| 43144 |       |       |        | S->P | HASH       |

                |  48 |          TABLE ACCESS FULL               | TAB_E       |    24M|  4363M| 43144 |       |       |        |      |            |

                |  49 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TAB_G               |     1 |   232 |     0 | ROWID | ROWID |  Q1,09 | PCWP |            |

                |  50 |      INDEX UNIQUE SCAN                   | PK_TAB_G            |     1 |       |     0 |       |       |  Q1,09 | PCWP |            |

                ----------------------------------------------------------------------------------------------------------------------------------------------------

                 

                For second query , which used OUTER JOIN ,

                explain plan is ,

                Plan

                Cost: 46,951  Bytes: 28,056  Cardinality: 7                         

                 

                ---------------------------------------------------------------------------------------------------------------------------------------------------

                | Id  | Operation                                | Name                       | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

                ----------------------------------------------------------------------------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT                         |                            |     7 | 28056 | 46951 |       |       |        |      |            |

                |   1 |  PX COORDINATOR                          |                            |       |       |       |       |       |        |      |            |

                |   2 |   PX SEND QC (RANDOM)                    | :TQ10009                   |     7 | 28056 | 46951 |       |       |  Q1,09 | P->S | QC (RAND)  |

                |   3 |    NESTED LOOPS OUTER                    |                            |     7 | 28056 | 46951 |       |       |  Q1,09 | PCWP |            |

                |   4 |     VIEW                                 |                            |     7 | 26432 | 46951 |       |       |  Q1,09 | PCWP |            |

                |   5 |      HASH JOIN                           |                            |     7 |  6216 | 46951 |       |       |  Q1,09 | PCWP |            |

                |   6 |       PX RECEIVE                         |                            |       |       |       |       |       |  Q1,09 | PCWP |            |

                |   7 |        PX SEND HASH                      | :TQ10008                   |       |       |       |       |       |  Q1,08 | P->P | HASH       |

                |   8 |         BUFFER SORT                      |                            |     7 | 28056 |       |       |       |  Q1,08 | PCWP |            |

                |   9 |          NESTED LOOPS                    |                            |       |       |       |       |       |  Q1,08 | PCWP |            |

                |  10 |           NESTED LOOPS                   |                            |     1 |   700 |  3806 |       |       |  Q1,08 | PCWP |            |

                |  11 |            HASH JOIN                     |                            |     1 |   205 |  3806 |       |       |  Q1,08 | PCWP |            |

                |  12 |             JOIN FILTER CREATE           | :BF0000                    |   140K|    21M|  2932 |       |       |  Q1,08 | PCWP |            |

                |  13 |              PX RECEIVE                  |                            |   140K|    21M|  2932 |       |       |  Q1,08 | PCWP |            |

                |  14 |               PX SEND HASH               | :TQ10006                   |   140K|    21M|  2932 |       |       |  Q1,06 | P->P | HASH       |

                |  15 |                HASH JOIN BUFFERED        |                            |   140K|    21M|  2932 |       |       |  Q1,06 | PCWP |            |

                |  16 |                 JOIN FILTER CREATE       | :BF0001                    |   164K|    10M|  1575 |       |       |  Q1,06 | PCWP |            |

                |  17 |                  PX RECEIVE              |                            |   164K|    10M|  1575 |       |       |  Q1,06 | PCWP |            |

                |  18 |                   PX SEND HASH           | :TQ10004                   |   164K|    10M|  1575 |       |       |  Q1,04 | P->P | HASH       |

                |  19 |                    PX BLOCK ITERATOR     |                            |   164K|    10M|  1575 |    30 |    30 |  Q1,04 | PCWC |            |

                |  20 |                     TABLE ACCESS FULL    | TAB_A |   164K|    10M|  1575 |    30 |    30 |  Q1,04 | PCWP |            |

                |  21 |                 PX RECEIVE               |                            |   974K|    84M|  1357 |       |       |  Q1,06 | PCWP |            |

                |  22 |                  PX SEND HASH            | :TQ10005                   |   974K|    84M|  1357 |       |       |  Q1,05 | P->P | HASH       |

                |  23 |                   JOIN FILTER USE        | :BF0001                    |   974K|    84M|  1357 |       |       |  Q1,05 | PCWP |            |

                |  24 |                    HASH JOIN BUFFERED    |                            |   974K|    84M|  1357 |       |       |  Q1,05 | PCWP |            |

                |  25 |                     HASH JOIN            |                            |   270K|    12M|   483 |       |       |  Q1,05 | PCWP |            |

                |  26 |                      PX RECEIVE          |                            |   275K|  6198K|     6 |       |       |  Q1,05 | PCWP |            |

                |  27 |                       PX SEND HASH       | :TQ10001                   |   275K|  6198K|     6 |       |       |  Q1,01 | P->P | HASH       |

                |  28 |                        PX BLOCK ITERATOR |                            |   275K|  6198K|     6 |       |       |  Q1,01 | PCWC |            |

                |  29 |                         TABLE ACCESS FULL| TAB_F |   275K|  6198K|     6 |       |       |  Q1,01 | PCWP |            |

                |  30 |                      PX RECEIVE          |                            |   269K|  7106K|   477 |       |       |  Q1,05 | PCWP |            |

                |  31 |                       PX SEND HASH       | :TQ10002                   |   269K|  7106K|   477 |       |       |  Q1,02 | P->P | HASH       |

                |  32 |                        PX BLOCK ITERATOR |                            |   269K|  7106K|   477 |    30 |    30 |  Q1,02 | PCWC |            |

                |  33 |                         TABLE ACCESS FULL| TAB_C |   269K|  7106K|   477 |    30 |    30 |  Q1,02 | PCWP |            |

                |  34 |                     PX RECEIVE           |                            |  1146K|    44M|   873 |       |       |  Q1,05 | PCWP |            |

                |  35 |                      PX SEND HASH        | :TQ10003                   |  1146K|    44M|   873 |       |       |  Q1,03 | P->P | HASH       |

                |  36 |                       PX BLOCK ITERATOR  |                            |  1146K|    44M|   873 |     1 |    30 |  Q1,03 | PCWC |            |

                |  37 |                        TABLE ACCESS FULL | TAB_B |  1146K|    44M|   873 |     1 |    30 |  Q1,03 | PCWP |            |

                |  38 |             PX RECEIVE                   |                            |  1146K|    49M|   873 |       |       |  Q1,08 | PCWP |            |

                |  39 |              PX SEND HASH                | :TQ10007                   |  1146K|    49M|   873 |       |       |  Q1,07 | P->P | HASH       |

                |  40 |               JOIN FILTER USE            | :BF0000                    |  1146K|    49M|   873 |       |       |  Q1,07 | PCWP |            |

                |  41 |                PX BLOCK ITERATOR         |                            |  1146K|    49M|   873 |     1 |    30 |  Q1,07 | PCWC |            |

                |  42 |                 TABLE ACCESS FULL        |TAB_B |  1146K|    49M|   873 |     1 |    30 |  Q1,07 | PCWP |            |

                |  43 |            INDEX UNIQUE SCAN             | PK_TAB_D |     1 |       |     0 |       |       |  Q1,08 | PCWP |            |

                |  44 |           TABLE ACCESS BY INDEX ROWID    | TAB_D|     1 |   495 |     1 |       |       |  Q1,08 | PCWP |            |

                |  45 |       BUFFER SORT                        |                            |       |       |       |       |       |  Q1,09 | PCWC |            |

                |  46 |        PX RECEIVE                        |                            |    24M|  4363M| 43144 |       |       |  Q1,09 | PCWP |            |

                |  47 |         PX SEND HASH                     | :TQ10000                   |    24M|  4363M| 43144 |       |       |        | S->P | HASH       |

                |  48 |          TABLE ACCESS FULL               | TAB_E |    24M|  4363M| 43144 |       |       |        |      |            |

                |  49 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TAB_G |     1 |   232 |     0 | ROWID | ROWID |  Q1,09 | PCWP |            |

                |  50 |      INDEX UNIQUE SCAN                   | PK_TAB_G|     1 |       |     0 |       |       |  Q1,09 | PCWP |            |

                ----------------------------------------------------------------------------------------------------------------------------------------------------

                • 5. Re: Repalcing UNIOn with full outer Join
                  Hoek

                  I don't see an outer join in your second query?

                  • 6. Re: Repalcing UNIOn with full outer Join
                    933417

                    I used a left outer join in the second query