<pre name="code" class="sql">SQL> set linesize 200
SQL> set pagesize 200
SQL> explain plan for SELECT A.*
FROM (SELECT 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' OR
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' OR
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 357394316
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52370 | 13M| | 18484 (1)| 00:03:42 |
| 1 | SORT ORDER BY | | 52370 | 13M| 14M| 18484 (1)| 00:03:42 |
|* 2 | HASH JOIN RIGHT OUTER | | 52370 | 13M| 2528K| 15534 (1)| 00:03:07 |
| 3 | VIEW | | 40370 | 2050K| | 8394 (1)| 00:01:41 |
| 4 | HASH GROUP BY | | 40370 | 1537K| 43M| 8394 (1)| 00:01:41 |
|* 5 | FILTER | | | | | | |
|* 6 | TABLE ACCESS FULL | OCRM_F_CI_CUST_VIEW | 804K| 29M| | 2852 (1)| 00:00:35 |
|* 7 | INDEX RANGE SCAN | IDX_AUTH_MANAGE_ACCOUNT | 1 | 13 | | 1 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT SEMI | | 52370 | 10M| | 6469 (1)| 00:01:18 |
| 9 | VIEW | VW_SQ_1 | 52308 | 1021K| | 1474 (1)| 00:00:18 |
|* 10 | FILTER | | | | | | |
| 11 | INDEX FAST FULL SCAN| IDX_CI_BELONG_CUSTMGR | 1043K| 26M| | 1474 (1)| 00:00:18 |
|* 12 | INDEX RANGE SCAN | IDX_AUTH_MANAGE_ACCOUNT | 1 | 13 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | OCRM_F_CI_CUST_DESC | 541K| 97M| | 4994 (1)| 00:01:00 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CD"."FR_ID"="CV"."FR_ID"(+) AND "CD"."CUST_ID"="CV"."CUST_ID"(+))
5 - filter("MGR_ID"='00001' OR EXISTS (SELECT 0 FROM "ADMIN_AUTH_MANAGE_ACCOUNT"
"ADMIN_AUTH_MANAGE_ACCOUNT" WHERE "USER_ID"=:B1 AND "MANAGE_ID"='00001'))
6 - filter("FR_ID"='15601')
7 - access("MANAGE_ID"='00001' AND "USER_ID"=:B1)
8 - access("ITEM_1"="CD"."CUST_ID")
10 - filter("MGR"."MGR_ID"='00001' OR EXISTS (SELECT 0 FROM "ADMIN_AUTH_MANAGE_ACCOUNT"
"ADMIN_AUTH_MANAGE_ACCOUNT" WHERE "USER_ID"=:B1 AND "MANAGE_ID"='00001'))
12 - access("MANAGE_ID"='00001' AND "USER_ID"=:B1)
13 - filter("CD"."FR_ID"='15601')
34 rows selected.
SELECT A.*
FROM (SELECT 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 in (
select '00001' from dual
union
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' OR
MGR.MGR_ID IN (SELECT USER_ID
FROM ADMIN_AUTH_MANAGE_ACCOUNT
WHERE MANAGE_ID = '00001'))*/
and MGR_ID in (select '00001' from dual
union
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