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 = '01092' OR
MGR_ID IN
(SELECT USER_ID
FROM ADMIN_AUTH_MANAGE_ACCOUNT
WHERE MANAGE_ID = '01092'))
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 = '01092' OR
MGR.MGR_ID IN (SELECT USER_ID
FROM ADMIN_AUTH_MANAGE_ACCOUNT
WHERE MANAGE_ID = '01092')))))
ORDER BY to_number(SUM_CI_BALANCE) DESC, to_number(SUM_LN_BALANCE) DESC;
Plan hash value: 4232450453
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48729 | 11M| | 10916 (1)| 00:02:11 |
| 1 | SORT ORDER BY | | 48729 | 11M| 12M| 10916 (1)| 00:02:11 |
|* 2 | HASH JOIN RIGHT OUTER | | 48729 | 11M| | 8242 (1)| 00:01:39 |
| 3 | VIEW | | 4483 | 227K| | 2148 (1)| 00:00:26 |
| 4 | HASH GROUP BY | | 4483 | 170K| 4944K| 2148 (1)| 00:00:26 |
|* 5 | FILTER | | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| OCRM_F_CI_CUST_VIEW | 89357 | 3403K| | 1428 (1)| 00:00:18 |
|* 7 | INDEX RANGE SCAN | IDX_OCRM_F_CI_CUST_VIEW2 | 89357 | | | 215 (1)| 00:00:03 |
|* 8 | INDEX RANGE SCAN | ADMIN_AUTH_MANAGE_ACCOUNT_IDX2 | 1 | 36 | | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT SEMI | | 48729 | 9612K| | 6094 (1)| 00:01:14 |
| 10 | VIEW | VW_SQ_1 | 48655 | 950K| | 1380 (1)| 00:00:17 |
|* 11 | FILTER | | | | | | |
| 12 | INDEX FAST FULL SCAN | IDX_CI_BELONG_CUSTMGR | 969K| 24M| | 1380 (1)| 00:00:17 |
|* 13 | INDEX RANGE SCAN | ADMIN_AUTH_MANAGE_ACCOUNT_IDX2 | 1 | 36 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | OCRM_F_CI_CUST_DESC | 60233 | 10M| | 4714 (1)| 00:00:57 |
---------------------------------------------------------------------------------------------------------------------------
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"='01092' OR EXISTS (SELECT 0 FROM "ADMIN_AUTH_MANAGE_ACCOUNT" "ADMIN_AUTH_MANAGE_ACCOUNT"
WHERE "MANAGE_ID"='01092' AND "USER_ID"=:B1))
7 - access("FR_ID"='15601')
8 - access("USER_ID"=:B1 AND "MANAGE_ID"='01092')
9 - access("ITEM_1"="CD"."CUST_ID")
11 - filter("MGR"."MGR_ID"='01092' OR EXISTS (SELECT 0 FROM "ADMIN_AUTH_MANAGE_ACCOUNT"
"ADMIN_AUTH_MANAGE_ACCOUNT" WHERE "MANAGE_ID"='01092' AND "USER_ID"=:B1))
13 - access("USER_ID"=:B1 AND "MANAGE_ID"='01092')
14 - filter("CD"."FR_ID"='15601')
由于子查询里有OR，导致子查询无法展开,改写后:
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 = '01092'
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 = '01092')
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 = '01092'
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 = '01092')))
))
ORDER BY to_number(SUM_CI_BALANCE) DESC, to_number(SUM_LN_BALANCE) DESC;
