explain plan for SELECT A.*
FROM (SELECT /*+ use_hash(cd cv)*/ CD.*,
nvl(CV.SUM_CI_BALANCE, 0) as SUM_CI_BALANCE,
nvl(CV.SUM_LN_BALANCE, 0) as SUM_LN_BALANCE
FROM OCRM_F_CI_CUST_DESC CD
left join (
select cust_id,
FR_ID,
sum(CI_BALANCE) as sum_CI_BALANCE,
sum(LN_BALANCE) as sum_LN_BALANCE
from OCRM_F_CI_CUST_VIEW
where 1 = 1
and FR_ID = '15601'
and MGR_ID = '00001'
group by cust_id, FR_ID
union
select cust_id,
FR_ID,
sum(CI_BALANCE) as sum_CI_BALANCE,
sum(LN_BALANCE) as sum_LN_BALANCE
from OCRM_F_CI_CUST_VIEW
where 1 = 1
and FR_ID = '15601'
and MGR_ID IN
(SELECT USER_ID
FROM ADMIN_AUTH_MANAGE_ACCOUNT
WHERE MANAGE_ID = '00001')
group by cust_id, FR_ID) CV
on CD.Cust_Id = CV.cust_id
and CD.Fr_Id = CV.fr_id
WHERE 1 = 1
and CD.FR_ID = '15601') A
where 1 = 1
AND ((EXISTS
(SELECT 1
FROM OCRM_F_CI_BELONG_CUSTMGR MGR
WHERE MGR.CUST_ID = A.CUST_ID
AND MGR.MGR_ID = '00001'
union
(SELECT 1
FROM OCRM_F_CI_BELONG_CUSTMGR MGR
WHERE MGR.CUST_ID = A.CUST_ID
and MGR.MGR_ID IN (SELECT USER_ID
FROM ADMIN_AUTH_MANAGE_ACCOUNT
WHERE MANAGE_ID = '00001')))
))
ORDER BY to_number(SUM_CI_BALANCE) DESC, to_number(SUM_LN_BALANCE) DESC;
select * from table(dbms_xplan.display());
Plan hash value: 4170314995
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 256 | | 38928 (1)| 00:07:48 |
| 1 | SORT ORDER BY | | 1 | 256 | | 38928 (1)| 00:07:48 |
|* 2 | HASH JOIN OUTER | | 1 | 256 | | 38927 (1)| 00:07:48 |
|* 3 | HASH JOIN | | 1 | 201 | | 24033 (1)| 00:04:49 |
| 4 | VIEW | VW_SQ_1 | 1086K| 12M| | 14082 (1)| 00:02:49 |
| 5 | SORT UNIQUE | | 1 | 41M| 49M| 14082 (1)| 00:02:49 |
| 6 | UNION-ALL | | | | | | |
|* 7 | INDEX FAST FULL SCAN | IDX_CI_BELONG_CUSTMGR | 162 | 4374 | | 1473 (1)| 00:00:18 |
|* 8 | HASH JOIN | | 1086K| 41M| | 1496 (1)| 00:00:18 |
|* 9 | INDEX RANGE SCAN | IDX_AUTH_MANAGE_ACCOUNT | 6135 | 79755 | | 21 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN | IDX_CI_BELONG_CUSTMGR | 1043K| 26M| | 1472 (1)| 00:00:18 |
|* 11 | TABLE ACCESS FULL | OCRM_F_CI_CUST_DESC | 541K| 97M| | 4994 (1)| 00:01:00 |
| 12 | VIEW | | 414K| 21M| | 14892 (1)| 00:02:59 |
| 13 | SORT UNIQUE | | 414K| 20M| 31M| 14892 (1)| 00:02:59 |
| 14 | UNION-ALL | | | | | | |
| 15 | HASH GROUP BY | | 90 | 3510 | | 113 (1)| 00:00:02 |
|* 16 | TABLE ACCESS BY INDEX ROWID| OCRM_F_CI_CUST_VIEW | 126 | 4914 | | 112 (0)| 00:00:02 |
|* 17 | INDEX RANGE SCAN | IDX_OCRM_F_CI_CUST_VIEW | 146 | | | 3 (0)| 00:00:01 |
| 18 | HASH GROUP BY | | 414K| 20M| 31M| 14779 (1)| 00:02:58 |
|* 19 | HASH JOIN RIGHT SEMI | | 550K| 27M| | 2874 (1)| 00:00:35 |
|* 20 | INDEX RANGE SCAN | IDX_AUTH_MANAGE_ACCOUNT | 6135 | 79755 | | 21 (0)| 00:00:01 |
|* 21 | TABLE ACCESS FULL | OCRM_F_CI_CUST_VIEW | 804K| 29M| | 2851 (1)| 00:00:35 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CD"."FR_ID"="CV"."FR_ID"(+) AND "CD"."CUST_ID"="CV"."CUST_ID"(+))
3 - access("VW_COL_1"="CD"."CUST_ID")
7 - filter("MGR"."MGR_ID"='00001')
8 - access("MGR"."MGR_ID"="USER_ID")
9 - access("MANAGE_ID"='00001')
11 - filter("CD"."FR_ID"='15601')
16 - filter("FR_ID"='15601')
17 - access("MGR_ID"='00001')
19 - access("MGR_ID"="USER_ID")
20 - access("MANAGE_ID"='00001')
21 - filter("FR_ID"='15601')