Home » SQL & PL/SQL » SQL & PL/SQL » Outer join creates multiple
Outer join creates multiple [message #404438] Thu, 21 May 2009 17:50
arielco
Messages: 1
Registered: May 2009
Location: Caracas
Junior Member
Hello everyone,
These queries:
    select trunc(recharge_date_time,'MI') rtime,count(*) sdp1_n from (
      select recharge_date_time from recharge_history_main_a
      union all
      select recharge_date_time from recharge_history_main_b
    ) where recharge_date_time > sysdate - 144/1440
    group by trunc(recharge_date_time,'MI')

    select trunc(recharge_date_time,'MI') rtime,count(*) sdp2_n from (
      select recharge_date_time from recharge_history_main_a@sdp2_n1
      union all
      select recharge_date_time from recharge_history_main_b@sdp2_n1
    ) where recharge_date_time > sysdate - 144/1440
    group by trunc(recharge_date_time,'MI')

produce these execution plans individually:
------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                         |     2 |    18 |  3557   (1)| 00:00:20 |
|   1 |  HASH GROUP BY       |                         |     2 |    18 |  3557   (1)| 00:00:20 |
|   2 |   VIEW               |                         |     2 |    18 |  3556   (1)| 00:00:20 |
|   3 |    UNION-ALL         |                         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| RECHARGE_HISTORY_MAIN_A |     1 |     9 |  1720   (1)| 00:00:10 |
|*  5 |     TABLE ACCESS FULL| RECHARGE_HISTORY_MAIN_B |     1 |     8 |  1836   (2)| 00:00:11 |
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                         | 41523 |   364K|  3592   (4)| 00:00:20 |        |      |
|   1 |  HASH GROUP BY   |                         | 41523 |   364K|  3592   (4)| 00:00:20 |        |      |
|   2 |   VIEW           |                         | 41523 |   364K|  3586   (4)| 00:00:20 |        |      |
|   3 |    UNION-ALL     |                         |       |       |            |          |        |      |
|   4 |     REMOTE       | RECHARGE_HISTORY_MAIN_A |     1 |     9 |  1740   (1)| 00:00:10 | SDP2_~ | R->S |
|   5 |     REMOTE       | RECHARGE_HISTORY_MAIN_B | 41522 |   364K|  1845   (7)| 00:00:11 | SDP2_~ | R->S |
------------------------------------------------------------------------------------------------------------

(the REMOTEs have their respective WHERE clauses)
Each returns a few (10-20) rows.

But when I OUTER JOIN them:
select * from (
    select trunc(recharge_date_time,'MI') rtime,count(*) sdp1_n from (
      select recharge_date_time from recharge_history_main_a
      union all
      select recharge_date_time from recharge_history_main_b
    ) where recharge_date_time > sysdate - 144/1440
    group by trunc(recharge_date_time,'MI')
  ) sdp1
  full outer join (
    select trunc(recharge_date_time,'MI') rtime,count(*) sdp2_n from (
      select recharge_date_time from recharge_history_main_a@sdp2_n1
      union all
      select recharge_date_time from recharge_history_main_b@sdp2_n1
    ) where recharge_date_time > sysdate - 144/1440
    group by trunc(recharge_date_time,'MI')
  ) sdp2
on sdp1.rtime = sdp2.rtime

The plan is just too big and inefficient:
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                         |  2906 |   124K| 14300   (3)| 00:01:19 |        |      |
|   1 |  VIEW                    |                         |  2906 |   124K| 14300   (3)| 00:01:19 |        |      |
|   2 |   UNION-ALL              |                         |       |       |            |          |        |      |
|*  3 |    HASH JOIN OUTER       |                         |   830 | 31540 |  7151   (3)| 00:00:40 |        |      |
|   4 |     VIEW                 |                         |     2 |    38 |  3557   (1)| 00:00:20 |        |      |
|   5 |      HASH GROUP BY       |                         |     2 |    18 |  3557   (1)| 00:00:20 |        |      |
|   6 |       VIEW               |                         |     2 |    18 |  3556   (1)| 00:00:20 |        |      |
|   7 |        UNION-ALL         |                         |       |       |            |          |        |      |
|*  8 |         TABLE ACCESS FULL| RECHARGE_HISTORY_MAIN_A |     1 |     9 |  1720   (1)| 00:00:10 |        |      |
|*  9 |         TABLE ACCESS FULL| RECHARGE_HISTORY_MAIN_B |     1 |     8 |  1836   (2)| 00:00:11 |        |      |
|  10 |     VIEW                 |                         | 41523 |   770K|  3592   (4)| 00:00:20 |        |      |
|  11 |      HASH GROUP BY       |                         | 41523 |   364K|  3592   (4)| 00:00:20 |        |      |
|  12 |       VIEW               |                         | 41523 |   364K|  3586   (4)| 00:00:20 |        |      |
|  13 |        UNION-ALL         |                         |       |       |            |          |        |      |
|  14 |         REMOTE           | RECHARGE_HISTORY_MAIN_A |     1 |     9 |  1740   (1)| 00:00:10 | SDP2_~ | R->S |
|  15 |         REMOTE           | RECHARGE_HISTORY_MAIN_B | 41522 |   364K|  1845   (7)| 00:00:11 | SDP2_~ | R->S |
|  16 |    HASH GROUP BY         |                         |  2076 | 18684 |  7149   (3)| 00:00:40 |        |      |
|* 17 |     FILTER               |                         |       |       |            |          |        |      |
|  18 |      VIEW                |                         | 41523 |   364K|  3586   (4)| 00:00:20 |        |      |
|  19 |       UNION-ALL          |                         |       |       |            |          |        |      |
|  20 |        REMOTE            | RECHARGE_HISTORY_MAIN_A |     1 |     9 |  1740   (1)| 00:00:10 | SDP2_~ | R->S |
|  21 |        REMOTE            | RECHARGE_HISTORY_MAIN_B | 41522 |   364K|  1845   (7)| 00:00:11 | SDP2_~ | R->S |
|  22 |      HASH GROUP BY       |                         |     2 |    18 |  3557   (1)| 00:00:20 |        |      |
|  23 |       VIEW               |                         |     2 |    18 |  3556   (1)| 00:00:20 |        |      |
|  24 |        UNION-ALL         |                         |       |       |            |          |        |      |
|* 25 |         TABLE ACCESS FULL| RECHARGE_HISTORY_MAIN_A |     1 |     9 |  1720   (1)| 00:00:10 |        |      |
|* 26 |         TABLE ACCESS FULL| RECHARGE_HISTORY_MAIN_B |     1 |     8 |  1836   (2)| 00:00:11 |        |      |
--------------------------------------------------------------------------------------------------------------------
 

When the HASH JOIN OUTER could take the two individual plans.
I'm no expert, so any guidance, requests for additional info are welcome.
Thanks and regards,
Ariel
Previous Topic: Totalizing a column (merged)
Next Topic: Any SQL function to reduce the size
Goto Forum:
  


Current Time: Sun Dec 11 08:33:46 CST 2016

Total time taken to generate the page: 0.07879 seconds