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
] 
Blog
Search
Help
Members
Register
Login
Home
