本文通过一个实际案例，展示了如何诊断并解决SQL报表查询性能问题，包括禁用子查询展开及将IN子句转换为EXISTS以提高执行效率。
报表的 End User发来邮件说，打开报表，超过15分钟了还没出结果，叫DBA调查一下
报表要跑的SQL如下：
select sum(nvl(T1796547.ACTL_GIV_AMT , 0)) as c1,
T1792779.ACCT_LONG_NAME as c2,
T1792779.NAME as c3,
T1796631.PRMTN_NAME as c4,
T1796631.PRMTN_ID as c5,
case when case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end is null then 'Private' else case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end end as c6,
T1796631.PRMTN_STTUS_CODE as c7,
T1796631.APPRV_BY_DESC as c8,
T1796631.APPRV_STTUS_CODE as c9,
T1796631.AUTO_UPDT_GTIN_IND as c10,
T1796631.CREAT_DATE as c11,
T1796631.PGM_START_DATE as c12,
T1796631.PGM_END_DATE as c13,
nvl(case when T1796631.PRMTN_STTUS_CODE = 'Confirmed' then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as VARCHAR ( 10 ) ) end , '') as c14,
T1796631.PRMTN_STOP_DATE as c15,
T1796631.SHPMT_START_DATE as c16,
T1796631.SHPMT_END_DATE as c17,
T1796631.CNBLN_WK_CNT as c18,
T1796631.ACTVY_DETL_POP as c19,
T1796631.CMMNT_DESC as c20,
T1796631.PRMTN_AVG_POP as c21,
T1792779.CHANL_TYPE_DESC as c22,
T1796631.PRMTN_SKID as c23
from
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T1796263,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_AP11.OPT_PRMTN_FDIM T1796631,
ADWG_OPTIMA_AP11.OPT_BASLN_FCT T1796547
where ( T1792779.ACCT_SKID = T1796547.ACCT_SKID
and T1792779.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID
and T1796263.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID
and T1796547.WK_SKID = T1796564.CAL_MASTR_SKID
and T1796547.BUS_UNIT_SKID = T1796631.BUS_UNIT_SKID
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326'
and T1796263.BUS_UNIT_NAME = 'Japan'
and T1796547.PRMTN_SKID = T1796631.PRMTN_SKID
and T1796564.FISC_YR_ABBR_NAME = 'FY10/11'
and T1792779.ACCT_LONG_NAME is not null
and (case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'
else T1796631.CORP_PRMTN_TYPE_CODE end in ('Corporate', 'Planned', 'Private'))
and T1796631.PRMTN_LONG_NAME in (select distinct T1796631.PRMTN_LONG_NAME as c1
from
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T1796263,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_AP11.OPT_PRMTN_FDIM T1796631,
ADWG_OPTIMA_AP11.OPT_PRMTN_PROD_FLTR_LKP T1796906
where ( T1792779.ACCT_SKID = T1796906.ACCT_PRMTN_SKID
and T1792779.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and T1796263.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and T1796564.CAL_MASTR_SKID = T1796906.DATE_SKID
and T1796631.PRMTN_SKID = T1796906.PRMTN_SKID
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326'
and T1796263.BUS_UNIT_NAME = 'Japan' and T1796564.FISC_YR_ABBR_NAME = 'FY10/11'
and T1796631.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and (case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end in ('Corporate', 'Planned', 'Private')) and ROWNUM >= 1 ) ) )
group by T1792779.NAME, T1792779.CHANL_TYPE_DESC,
T1792779.ACCT_LONG_NAME, T1796631.PRMTN_SKID,
T1796631.PRMTN_ID, T1796631.PRMTN_NAME,
T1796631.SHPMT_END_DATE, T1796631.SHPMT_START_DATE,
T1796631.PRMTN_STTUS_CODE, T1796631.APPRV_STTUS_CODE,
T1796631.CMMNT_DESC, T1796631.PGM_START_DATE,
T1796631.PGM_END_DATE, T1796631.CREAT_DATE,
T1796631.APPRV_BY_DESC, T1796631.AUTO_UPDT_GTIN_IND,
T1796631.PRMTN_STOP_DATE, T1796631.ACTVY_DETL_POP,
T1796631.CNBLN_WK_CNT, T1796631.PRMTN_AVG_POP,
case when case
when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account'
then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end is null
then 'Private' else case when
T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'
else T1796631.CORP_PRMTN_TYPE_CODE end end , nvl(case when
T1796631.PRMTN_STTUS_CODE = 'Confirmed'
then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as VARCHAR ( 10 ) )
end , '')
order by c23, c2;
这个SQL 如果用ADWG_OPTIMA_AP11账号跑，只需要16秒，该SQL 的执行计划如下：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 632594279
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 426 | 3697 (27)| 00:00:16 | | |
| 1 | SORT GROUP BY | | 1 | 426 | 3697 (27)| 00:00:16 | | |
| 2 | NESTED LOOPS | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 426 | 3696 (27)| 00:00:16 | | |
| 4 | NESTED LOOPS | | 1 | 413 | 3695 (27)| 00:00:16 | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 157 | 3694 (27)| 00:00:16 | | |
| 6 | NESTED LOOPS | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 116 | 183 (1)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 90 | 34 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 10 | PARTITION LIST ALL | | 1 | 76 | 33 (0)| 00:00:01 | 1 | 16 |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 76 | 33 (0)| 00:00:01 | 1 | 16 |
|* 12 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 32 (0)| 00:00:01 | 1 | 16 |
| 13 | PARTITION LIST ITERATOR | | | | | | KEY | KEY |
| 14 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 15 | BITMAP MINUS | | | | | | | |
|* 16 | BITMAP INDEX SINGLE VALUE | OPT_BASLN_FCT_NX3 | | | | | KEY | KEY |
|* 17 | BITMAP INDEX SINGLE VALUE | OPT_BASLN_FCT_NX4 | | | | | KEY | KEY |
|* 18 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_BASLN_FCT | 1 | 81 | 183 (1)| 00:00:01 | 1 | 1 |
| 19 | BUFFER SORT | | 1 | 41 | 3329 (24)| 00:00:15 | | |
| 20 | VIEW | VW_NSO_1 | 1 | 41 | 3511 (28)| 00:00:16 | | |
| 21 | HASH UNIQUE | | 1 | 156 | | | | |
| 22 | COUNT | | | | | | | |
|* 23 | FILTER | | | | | | | |
| 24 | NESTED LOOPS | | | | | | | |
| 25 | NESTED LOOPS | | 1 | 156 | 3511 (28)| 00:00:16 | | |
| 26 | NESTED LOOPS | | 1 | 143 | 3510 (28)| 00:00:16 | | |
| 27 | NESTED LOOPS | | 24 | 1968 | 3486 (28)| 00:00:15 | | |
| 28 | NESTED LOOPS | | 1 | 60 | 34 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 30 | PARTITION LIST ALL | | 1 | 46 | 33 (0)| 00:00:01 | 1 | 16 |
| 31 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 1 | 46 | 33 (0)| 00:00:01 | 1 | 16 |
|* 32 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 32 (0)| 00:00:01 | 1 | 16 |
| 33 | PARTITION LIST ITERATOR | | 24 | 528 | 3452 (29)| 00:00:15 | KEY | KEY |
|* 34 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FLTR_LKP | 24 | 528 | 3452 (29)| 00:00:15 | KEY | KEY |
|* 35 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 61 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 36 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 37 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 39 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 256 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 40 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("T1796263"."BUS_UNIT_NAME"='Japan')
12 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
filter("T1792779"."ACCT_LONG_NAME" IS NOT NULL)
16 - access("T1792779"."ACCT_SKID"="T1796547"."ACCT_SKID")
17 - access("T1796547"."PRMTN_SKID" IS NULL)
18 - filter("T1792779"."BUS_UNIT_SKID"="T1796547"."BUS_UNIT_SKID" AND "T1796263"."BUS_UNIT_SKID"="T1796547"."BUS_UNIT_SKID")
23 - filter(ROWNUM>=1)
29 - access("T1796263"."BUS_UNIT_NAME"='Japan')
32 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
34 - filter("T1792779"."ACCT_SKID"="T1796906"."ACCT_PRMTN_SKID" AND "T1792779"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID" AND
"T1796263"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID")
35 - filter(CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE"
END ='Corporate' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE
"T1796631"."CORP_PRMTN_TYPE_CODE" END ='Planned' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate'
ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END ='Private')
36 - access("T1796631"."PRMTN_SKID"="T1796906"."PRMTN_SKID" AND "T1796631"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID")
37 - access("T1796564"."CAL_MASTR_SKID"="T1796906"."DATE_SKID")
38 - filter("T1796564"."FISC_YR_ABBR_NAME"='FY10/11')
39 - filter((CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE"
END ='Corporate' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE
"T1796631"."CORP_PRMTN_TYPE_CODE" END ='Planned' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate'
ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END ='Private') AND "T1796631"."PRMTN_LONG_NAME"="C1")
40 - access("T1796547"."PRMTN_SKID"="T1796631"."PRMTN_SKID" AND "T1796547"."BUS_UNIT_SKID"="T1796631"."BUS_UNIT_SKID")
41 - access("T1796547"."WK_SKID"="T1796564"."CAL_MASTR_SKID")
42 - filter("T1796564"."FISC_YR_ABBR_NAME"='FY10/11')
78 rows selected.
其实上面的SQL还能优化，不过既然16秒可以出结果了，我也懒得去优化了。我们的报表系统不能直接用ADWG_OPTIMA_AP11账号，只能用报表专用的账号，而且在数据库中，我们对报表专用的账号设置了RLS(ROW LEVEL SECURITY),RLS其实就是在SQL语句中加入某些where限制条件。
RLS的限制条件 是 ---add RLS, -- end RLS部分
select sum(nvl(T1796547.ACTL_GIV_AMT , 0)) as c1,
T1792779.ACCT_LONG_NAME as c2,
T1792779.NAME as c3,
T1796631.PRMTN_NAME as c4,
T1796631.PRMTN_ID as c5,
case when case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end is null then 'Private' else case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end end as c6,
T1796631.PRMTN_STTUS_CODE as c7,
T1796631.APPRV_BY_DESC as c8,
T1796631.APPRV_STTUS_CODE as c9,
T1796631.AUTO_UPDT_GTIN_IND as c10,
T1796631.CREAT_DATE as c11,
T1796631.PGM_START_DATE as c12,
T1796631.PGM_END_DATE as c13,
nvl(case when T1796631.PRMTN_STTUS_CODE = 'Confirmed' then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as VARCHAR ( 10 ) ) end , '') as c14,
T1796631.PRMTN_STOP_DATE as c15,
T1796631.SHPMT_START_DATE as c16,
T1796631.SHPMT_END_DATE as c17,
T1796631.CNBLN_WK_CNT as c18,
T1796631.ACTVY_DETL_POP as c19,
T1796631.CMMNT_DESC as c20,
T1796631.PRMTN_AVG_POP as c21,
T1792779.CHANL_TYPE_DESC as c22,
T1796631.PRMTN_SKID as c23
from
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T1796263,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_AP11.OPT_PRMTN_FDIM T1796631,
ADWG_OPTIMA_AP11.OPT_BASLN_FCT T1796547
where ( T1792779.ACCT_SKID = T1796547.ACCT_SKID
and T1792779.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID
and T1796263.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID
and T1796547.WK_SKID = T1796564.CAL_MASTR_SKID
and T1796547.BUS_UNIT_SKID = T1796631.BUS_UNIT_SKID
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326'
and T1796263.BUS_UNIT_NAME = 'Japan'
and T1796547.PRMTN_SKID = T1796631.PRMTN_SKID
and T1796564.FISC_YR_ABBR_NAME = 'FY10/11'
and T1792779.ACCT_LONG_NAME is not null
-- add RLS
and T1796547.acct_skid IN (select org.org_skid from (SELECT DISTINCT ap.org_skid
FROM adwg_optima_ap11.opt_acct_postn_lkp ap, adwg_optima_ap11.opt_party_persn_lkp pp, adwg_optima_ap11.opt_user_lkp u
WHERE ap.postn_id = pp.party_id
AND pp.persn_id = u.user_id
AND u.login_name = 'BT0016'
union select 0 as org_skid
from sys.dual) org
)
and T1792779.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796547.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796263.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796631.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
-- end RLS
and (case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'
else T1796631.CORP_PRMTN_TYPE_CODE end in ('Corporate', 'Planned', 'Private'))
and T1796631.PRMTN_LONG_NAME in (select distinct T1796631.PRMTN_LONG_NAME as c1
from
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T1796263,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_AP11.OPT_PRMTN_FDIM T1796631,
ADWG_OPTIMA_AP11.OPT_PRMTN_PROD_FLTR_LKP T1796906
where ( T1792779.ACCT_SKID = T1796906.ACCT_PRMTN_SKID
and T1792779.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and T1796263.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and T1796564.CAL_MASTR_SKID = T1796906.DATE_SKID
and T1796631.PRMTN_SKID = T1796906.PRMTN_SKID
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326'
and T1796263.BUS_UNIT_NAME = 'Japan' and T1796564.FISC_YR_ABBR_NAME = 'FY10/11'
and T1796631.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and (case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end in ('Corporate', 'Planned', 'Private')) and ROWNUM >= 1 ) ) )
group by T1792779.NAME, T1792779.CHANL_TYPE_DESC,
T1792779.ACCT_LONG_NAME, T1796631.PRMTN_SKID,
T1796631.PRMTN_ID, T1796631.PRMTN_NAME,
T1796631.SHPMT_END_DATE, T1796631.SHPMT_START_DATE,
T1796631.PRMTN_STTUS_CODE, T1796631.APPRV_STTUS_CODE,
T1796631.CMMNT_DESC, T1796631.PGM_START_DATE,
T1796631.PGM_END_DATE, T1796631.CREAT_DATE,
T1796631.APPRV_BY_DESC, T1796631.AUTO_UPDT_GTIN_IND,
T1796631.PRMTN_STOP_DATE, T1796631.ACTVY_DETL_POP,
T1796631.CNBLN_WK_CNT, T1796631.PRMTN_AVG_POP,
case when case
when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account'
then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end is null
then 'Private' else case when
T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'
else T1796631.CORP_PRMTN_TYPE_CODE end end , nvl(case when
T1796631.PRMTN_STTUS_CODE = 'Confirmed'
then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as VARCHAR ( 10 ) )
end , '')
order by c23, c2;
加入了RLS限制条件之后，这个SQL就跑很久了，其实这里RLS没加完,它要跑1分钟左右，如果加完了，跑不出结果，直接latch:cache buffers chains.
现在要关心的就是，不加RLS，16秒，加了RLS也不应该超过20秒。好的，来看看执行计划
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 205764488
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 398 | 4089 (25)| 00:00:18 | | |
| 1 | SORT GROUP BY | | 1 | 398 | 4089 (25)| 00:00:18 | | |
|* 2 | FILTER | | | | | | | |
|* 3 | HASH JOIN | | 1 | 398 | 577 (2)| 00:00:03 | | |
| 4 | NESTED LOOPS | | | | | | | |
| 5 | NESTED LOOPS | | 1 | 385 | 558 (1)| 00:00:03 | | |
| 6 | NESTED LOOPS | | 1 | 372 | 557 (1)| 00:00:03 | | |
| 7 | NESTED LOOPS | | 1 | 116 | 556 (1)| 00:00:03 | | |
| 8 | NESTED LOOPS | | 1 | 90 | 32 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 10 | PARTITION LIST INLIST | | 1 | 76 | 31 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 76 | 31 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 12 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 30 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 13 | PARTITION LIST INLIST | | 1 | 40 | 556 (1)| 00:00:03 |KEY(I) |KEY(I) |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_BASLN_FCT | 1 | 40 | 556 (1)| 00:00:03 |KEY(I) |KEY(I) |
| 15 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 16 | BITMAP MINUS | | | | | | | |
|* 17 | BITMAP INDEX SINGLE VALUE | OPT_BASLN_FCT_NX3 | | | | |KEY(I) |KEY(I) |
|* 18 | BITMAP INDEX SINGLE VALUE | OPT_BASLN_FCT_NX4 | | | | |KEY(I) |KEY(I) |
|* 19 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 256 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 20 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 21 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | |
| 23 | VIEW | | 14430 | 183K| 17 (36)| 00:00:01 | | |
| 24 | SORT UNIQUE | | 14430 | 704K| 17 (48)| 00:00:01 | | |
| 25 | UNION-ALL | | | | | | | |
| 26 | NESTED LOOPS | | 14429 | 704K| 9 (0)| 00:00:01 | | |
| 27 | NESTED LOOPS | | 2 | 70 | 3 (0)| 00:00:01 | | |
| 28 | TABLE ACCESS BY INDEX ROWID | OPT_USER_LKP | 1 | 17 | 2 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN | OPT_USER_LKP_IDX | 1 | | 1 (0)| 00:00:01 | | |
|* 30 | INDEX RANGE SCAN | OPT_PARTY_PERSN_LKP_NX2 | 2 | 36 | 1 (0)| 00:00:01 | | |
|* 31 | INDEX RANGE SCAN | OPT_ACCT_POSTN_LKP_NX1 | 6640 | 99600 | 3 (0)| 00:00:01 | | |
| 32 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
|* 33 | FILTER | | | | | | | |
| 34 | COUNT | | | | | | | |
|* 35 | FILTER | | | | | | | |
| 36 | NESTED LOOPS | | | | | | | |
| 37 | NESTED LOOPS | | 1 | 156 | 3511 (28)| 00:00:16 | | |
| 38 | NESTED LOOPS | | 1 | 143 | 3510 (28)| 00:00:16 | | |
| 39 | NESTED LOOPS | | 24 | 1968 | 3486 (28)| 00:00:15 | | |
| 40 | NESTED LOOPS | | 1 | 60 | 34 (0)| 00:00:01 | | |
|* 41 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 42 | PARTITION LIST ALL | | 1 | 46 | 33 (0)| 00:00:01 | 1 | 16 |
| 43 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 1 | 46 | 33 (0)| 00:00:01 | 1 | 16 |
|* 44 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 32 (0)| 00:00:01 | 1 | 16 |
| 45 | PARTITION LIST ITERATOR | | 24 | 528 | 3452 (29)| 00:00:15 | KEY | KEY |
|* 46 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FLTR_LKP | 24 | 528 | 3452 (29)| 00:00:15 | KEY | KEY |
|* 47 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 61 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 48 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 49 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 50 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (<not feasible>)
3 - access("T1796547"."ACCT_SKID"="ORG"."ORG_SKID")
9 - access("T1796263"."BUS_UNIT_NAME"='Japan')
filter("T1796263"."BUS_UNIT_SKID"=0 OR "T1796263"."BUS_UNIT_SKID"=11769 OR "T1796263"."BUS_UNIT_SKID"=11772 OR
"T1796263"."BUS_UNIT_SKID"=11774 OR "T1796263"."BUS_UNIT_SKID"=11777 OR "T1796263"."BUS_UNIT_SKID"=11779 OR
"T1796263"."BUS_UNIT_SKID"=11780 OR "T1796263"."BUS_UNIT_SKID"=14329 OR "T1796263"."BUS_UNIT_SKID"=14334 OR
"T1796263"."BUS_UNIT_SKID"=14339 OR "T1796263"."BUS_UNIT_SKID"=14340 OR "T1796263"."BUS_UNIT_SKID"=14341 OR
"T1796263"."BUS_UNIT_SKID"=14350 OR "T1796263"."BUS_UNIT_SKID"=14800 OR "T1796263"."BUS_UNIT_SKID"=14801)
12 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
filter("T1792779"."ACCT_LONG_NAME" IS NOT NULL)
14 - filter("T1792779"."BUS_UNIT_SKID"="T1796547"."BUS_UNIT_SKID" AND "T1796263"."BUS_UNIT_SKID"="T1796547"."BUS_UNIT_SKID")
17 - access("T1792779"."ACCT_SKID"="T1796547"."ACCT_SKID")
18 - access("T1796547"."PRMTN_SKID" IS NULL)
19 - filter(CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE
"T1796631"."CORP_PRMTN_TYPE_CODE" END ='Corporate' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN
'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END ='Planned' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target
Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END ='Private')
20 - access("T1796547"."PRMTN_SKID"="T1796631"."PRMTN_SKID" AND "T1796547"."BUS_UNIT_SKID"="T1796631"."BUS_UNIT_SKID")
filter("T1796631"."BUS_UNIT_SKID"=0 OR "T1796631"."BUS_UNIT_SKID"=11769 OR "T1796631"."BUS_UNIT_SKID"=11772 OR
"T1796631"."BUS_UNIT_SKID"=11774 OR "T1796631"."BUS_UNIT_SKID"=11777 OR "T1796631"."BUS_UNIT_SKID"=11779 OR
"T1796631"."BUS_UNIT_SKID"=11780 OR "T1796631"."BUS_UNIT_SKID"=14329 OR "T1796631"."BUS_UNIT_SKID"=14334 OR
"T1796631"."BUS_UNIT_SKID"=14339 OR "T1796631"."BUS_UNIT_SKID"=14340 OR "T1796631"."BUS_UNIT_SKID"=14341 OR
"T1796631"."BUS_UNIT_SKID"=14350 OR "T1796631"."BUS_UNIT_SKID"=14800 OR "T1796631"."BUS_UNIT_SKID"=14801)
21 - access("T1796547"."WK_SKID"="T1796564"."CAL_MASTR_SKID")
22 - filter("T1796564"."FISC_YR_ABBR_NAME"='FY10/11')
29 - access("U"."LOGIN_NAME"='BT0016')
30 - access("PP"."PERSN_ID"="U"."USER_ID")
31 - access("AP"."POSTN_ID"="PP"."PARTY_ID")
33 - filter("T1796631"."PRMTN_LONG_NAME"=:B1)
35 - filter(ROWNUM>=1)
41 - access("T1796263"."BUS_UNIT_NAME"='Japan')
44 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
46 - filter("T1792779"."ACCT_SKID"="T1796906"."ACCT_PRMTN_SKID" AND "T1792779"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID" AND
"T1796263"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID")
47 - filter(CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE
"T1796631"."CORP_PRMTN_TYPE_CODE" END ='Corporate' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN
'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END ='Planned' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target
Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END ='Private')
48 - access("T1796631"."PRMTN_SKID"="T1796906"."PRMTN_SKID" AND "T1796631"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID")
49 - access("T1796564"."CAL_MASTR_SKID"="T1796906"."DATE_SKID")
50 - filter("T1796564"."FISC_YR_ABBR_NAME"='FY10/11')
102 rows selected.
情况id=23，有个VIEW关键字，这里表示 CBO把子查询当成了一个视图，因为该子查询里面有UNION ALL关键字，所以CBO不能进行VIEW MERGE(视图合并)，好的我们看看RLS的where条件
-- add RLS
and T1796547.acct_skid IN (select org.org_skid from (SELECT DISTINCT ap.org_skid
FROM adwg_optima_ap11.opt_acct_postn_lkp ap, adwg_optima_ap11.opt_party_persn_lkp pp, adwg_optima_ap11.opt_user_lkp u
WHERE ap.postn_id = pp.party_id
AND pp.persn_id = u.user_id
AND u.login_name = 'BT0016'
union select 0 as org_skid
from sys.dual) org
)
and T1792779.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796547.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796263.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796631.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
-- end RLS
因为 RLS 条件中有 IN ，IN 一个子查询CBO 会进行Subquery Unnesting,Subquery Unnesting 不能从执行计划中显示的看出来，但是也能看出来，不过需要你有很久的看执行计划的经验
这里，CBO进行了Subquery Unnesting,因为我们不加 RLS，SQL跑的飞快，加了就慢了，而且还进行了Subquery Unnesting，ok 那我禁止 Subquery Unnesting,具体就是加个HINT
/*+ NO_UNNEST */
select sum(nvl(T1796547.ACTL_GIV_AMT , 0)) as c1,
T1792779.ACCT_LONG_NAME as c2,
T1792779.NAME as c3,
T1796631.PRMTN_NAME as c4,
T1796631.PRMTN_ID as c5,
case when case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end is null then 'Private' else case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end end as c6,
T1796631.PRMTN_STTUS_CODE as c7,
T1796631.APPRV_BY_DESC as c8,
T1796631.APPRV_STTUS_CODE as c9,
T1796631.AUTO_UPDT_GTIN_IND as c10,
T1796631.CREAT_DATE as c11,
T1796631.PGM_START_DATE as c12,
T1796631.PGM_END_DATE as c13,
nvl(case when T1796631.PRMTN_STTUS_CODE = 'Confirmed' then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as VARCHAR ( 10 ) ) end , '') as c14,
T1796631.PRMTN_STOP_DATE as c15,
T1796631.SHPMT_START_DATE as c16,
T1796631.SHPMT_END_DATE as c17,
T1796631.CNBLN_WK_CNT as c18,
T1796631.ACTVY_DETL_POP as c19,
T1796631.CMMNT_DESC as c20,
T1796631.PRMTN_AVG_POP as c21,
T1792779.CHANL_TYPE_DESC as c22,
T1796631.PRMTN_SKID as c23
from
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T1796263,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_AP11.OPT_PRMTN_FDIM T1796631,
ADWG_OPTIMA_AP11.OPT_BASLN_FCT T1796547
where ( T1792779.ACCT_SKID = T1796547.ACCT_SKID
and T1792779.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID
and T1796263.BUS_UNIT_SKID = T1796547.BUS_UNIT_SKID
and T1796547.WK_SKID = T1796564.CAL_MASTR_SKID
and T1796547.BUS_UNIT_SKID = T1796631.BUS_UNIT_SKID
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326'
and T1796263.BUS_UNIT_NAME = 'Japan'
and T1796547.PRMTN_SKID = T1796631.PRMTN_SKID
and T1796564.FISC_YR_ABBR_NAME = 'FY10/11'
and T1792779.ACCT_LONG_NAME is not null
-- add RLS
and T1796547.acct_skid IN (select /*+ NO_UNNEST */ org.org_skid from (SELECT DISTINCT ap.org_skid
FROM adwg_optima_ap11.opt_acct_postn_lkp ap, adwg_optima_ap11.opt_party_persn_lkp pp, adwg_optima_ap11.opt_user_lkp u
WHERE ap.postn_id = pp.party_id
AND pp.persn_id = u.user_id
AND u.login_name = 'BT0016'
union select 0 as org_skid
from sys.dual) org
)
and T1792779.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796547.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796263.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
and T1796631.bus_unit_skid IN (0,11769,11772,11774,11777,11779,11780,14329,14334,14339,14340,14341,14350,14800,14801)
-- end RLS
and (case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'
else T1796631.CORP_PRMTN_TYPE_CODE end in ('Corporate', 'Planned', 'Private'))
and T1796631.PRMTN_LONG_NAME in (select distinct T1796631.PRMTN_LONG_NAME as c1
from
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T1792779 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T1796263,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T1796564 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_AP11.OPT_PRMTN_FDIM T1796631,
ADWG_OPTIMA_AP11.OPT_PRMTN_PROD_FLTR_LKP T1796906
where ( T1792779.ACCT_SKID = T1796906.ACCT_PRMTN_SKID
and T1792779.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and T1796263.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and T1796564.CAL_MASTR_SKID = T1796906.DATE_SKID
and T1796631.PRMTN_SKID = T1796906.PRMTN_SKID
and T1792779.ACCT_LONG_NAME = 'FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326'
and T1796263.BUS_UNIT_NAME = 'Japan' and T1796564.FISC_YR_ABBR_NAME = 'FY10/11'
and T1796631.BUS_UNIT_SKID = T1796906.BUS_UNIT_SKID
and (case when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end in ('Corporate', 'Planned', 'Private')) and ROWNUM >= 1 ) ) )
group by T1792779.NAME, T1792779.CHANL_TYPE_DESC,
T1792779.ACCT_LONG_NAME, T1796631.PRMTN_SKID,
T1796631.PRMTN_ID, T1796631.PRMTN_NAME,
T1796631.SHPMT_END_DATE, T1796631.SHPMT_START_DATE,
T1796631.PRMTN_STTUS_CODE, T1796631.APPRV_STTUS_CODE,
T1796631.CMMNT_DESC, T1796631.PGM_START_DATE,
T1796631.PGM_END_DATE, T1796631.CREAT_DATE,
T1796631.APPRV_BY_DESC, T1796631.AUTO_UPDT_GTIN_IND,
T1796631.PRMTN_STOP_DATE, T1796631.ACTVY_DETL_POP,
T1796631.CNBLN_WK_CNT, T1796631.PRMTN_AVG_POP,
case when case
when T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account'
then 'Corporate' else T1796631.CORP_PRMTN_TYPE_CODE end is null
then 'Private' else case when
T1796631.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'
else T1796631.CORP_PRMTN_TYPE_CODE end end , nvl(case when
T1796631.PRMTN_STTUS_CODE = 'Confirmed'
then cast(( TRUNC( TO_DATE('2011-04-26' , 'YYYY-MM-DD') ) - TRUNC( T1796631.PGM_END_DATE ) ) as VARCHAR ( 10 ) )
end , '')
order by c23, c2;
执行计划如下：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3864813311
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 335 | 11249 (27)| 00:00:49 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FDA22DD0_781CE429 | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 632 | 3532 (28)| 00:00:16 | | |
| 5 | NESTED LOOPS | | 1 | 376 | 3512 (28)| 00:00:16 | | |
|* 6 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 7 | VIEW | VW_NSO_1 | 1 | 362 | 3511 (28)| 00:00:16 | | |
| 8 | SORT UNIQUE | | 1 | 156 | | | | |
| 9 | COUNT | | | | | | | |
|* 10 | FILTER | | | | | | | |
| 11 | NESTED LOOPS | | | | | | | |
| 12 | NESTED LOOPS | | 1 | 156 | 3511 (28)| 00:00:16 | | |
| 13 | NESTED LOOPS | | 1 | 143 | 3510 (28)| 00:00:16 | | |
| 14 | NESTED LOOPS | | 24 | 1968 | 3486 (28)| 00:00:15 | | |
| 15 | NESTED LOOPS | | 1 | 60 | 34 (0)| 00:00:01 | | |
|* 16 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 17 | PARTITION LIST ALL | | 1 | 46 | 33 (0)| 00:00:01 | 1 | 16 |
| 18 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 1 | 46 | 33 (0)| 00:00:01 | 1 | 16 |
|* 19 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 32 (0)| 00:00:01 | 1 | 16 |
| 20 | PARTITION LIST ITERATOR | | 24 | 528 | 3452 (29)| 00:00:15 | KEY | KEY |
|* 21 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FLTR_LKP | 24 | 528 | 3452 (29)| 00:00:15 | KEY | KEY |
|* 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 61 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 23 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | |
| 26 | PARTITION LIST INLIST | | 5 | | 15 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 27 | INDEX RANGE SCAN | OPT_PRMTN_FDIM_NX3 | 5 | | 15 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 28 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 256 | 19 (0)| 00:00:01 | 1 | 1 |
| 29 | LOAD AS SELECT | SYS_TEMP_0FDA22DD0_781CE429 | | | | | | |
| 30 | NESTED LOOPS | | | | | | | |
| 31 | NESTED LOOPS | | 1 | 90 | 32 (0)| 00:00:01 | | |
|* 32 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | |
| 33 | PARTITION LIST INLIST | | 1 | | 30 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 34 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 30 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 35 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 76 | 31 (0)| 00:00:01 | 1 | 1 |
| 36 | SORT GROUP BY | | 1 | 335 | 7685 (27)| 00:00:33 | | |
|* 37 | FILTER | | | | | | | |
|* 38 | HASH JOIN | | 1 | 335 | 4111 (26)| 00:00:18 | | |
|* 39 | HASH JOIN | | 1 | 322 | 3599 (28)| 00:00:16 | | |
|* 40 | HASH JOIN | | 1 | 246 | 3567 (28)| 00:00:16 | | |
| 41 | PARTITION LIST INLIST | | | | 3564 (28)| 00:00:16 |KEY(I) |KEY(I) |
| 42 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_BASLN_FCT | | | 3564 (28)| 00:00:16 |KEY(I) |KEY(I) |
| 43 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 44 | BITMAP AND | | | | | | | |
| 45 | BITMAP MERGE | | | | | | | |
| 46 | BITMAP KEY ITERATION | | | | | | | |
| 47 | BUFFER SORT | | | | | | | |
| 48 | TABLE ACCESS FULL | SYS_TEMP_0FDA22DD0_781CE429 | 1 | 13 | 2 (0)| 00:00:01 | | |
|* 49 | BITMAP INDEX RANGE SCAN | OPT_BASLN_FCT_NX4 | | | | |KEY(I) |KEY(I) |
| 50 | BITMAP MERGE | | | | | | | |
| 51 | BITMAP KEY ITERATION | | | | | | | |
| 52 | BUFFER SORT | | | | | | | |
| 53 | TABLE ACCESS FULL | SYS_TEMP_0FDA22DD1_781CE429 | 1 | 13 | 2 (0)| 00:00:01 | | |
|* 54 | BITMAP INDEX RANGE SCAN | OPT_BASLN_FCT_NX3 | | | | |KEY(I) |KEY(I) |
| 55 | TABLE ACCESS FULL | SYS_TEMP_0FDA22DD0_781CE429 | 1 | 220 | 2 (0)| 00:00:01 | | |
| 56 | PARTITION LIST INLIST | | 1 | 76 | 32 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 57 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 76 | 32 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 58 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 1 | | 31 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 59 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 365 | 4745 | 511 (11)| 00:00:03 | | |
| 60 | VIEW | | 2 | 26 | 10 (30)| 00:00:01 | | |
| 61 | SORT UNIQUE | | 2 | 50 | 10 (50)| 00:00:01 | | |
| 62 | UNION-ALL | | | | | | | |
|* 63 | HASH JOIN | | 1 | 50 | 6 (17)| 00:00:01 | | |
| 64 | NESTED LOOPS | | 2 | 70 | 3 (0)| 00:00:01 | | |
| 65 | TABLE ACCESS BY INDEX ROWID | OPT_USER_LKP | 1 | 17 | 2 (0)| 00:00:01 | | |
|* 66 | INDEX RANGE SCAN | OPT_USER_LKP_IDX | 1 | | 1 (0)| 00:00:01 | | |
|* 67 | INDEX RANGE SCAN | OPT_PARTY_PERSN_LKP_NX2 | 2 | 36 | 1 (0)| 00:00:01 | | |
| 68 | TABLE ACCESS BY INDEX ROWID | OPT_ACCT_POSTN_LKP | 2 | 30 | 2 (0)| 00:00:01 | | |
|* 69 | INDEX RANGE SCAN | OPT_ACCT_POSTN_LKP_NX2 | 2 | | 1 (0)| 00:00:01 | | |
|* 70 | FILTER | | | | | | | |
| 71 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1796263"."BUS_UNIT_NAME"='Japan')
filter("T1796263"."BUS_UNIT_SKID"=0 OR "T1796263"."BUS_UNIT_SKID"=11769 OR "T1796263"."BUS_UNIT_SKID"=11772 OR
"T1796263"."BUS_UNIT_SKID"=11774 OR "T1796263"."BUS_UNIT_SKID"=11777 OR "T1796263"."BUS_UNIT_SKID"=11779 OR
"T1796263"."BUS_UNIT_SKID"=11780 OR "T1796263"."BUS_UNIT_SKID"=14329 OR "T1796263"."BUS_UNIT_SKID"=14334 OR
"T1796263"."BUS_UNIT_SKID"=14339 OR "T1796263"."BUS_UNIT_SKID"=14340 OR "T1796263"."BUS_UNIT_SKID"=14341 OR
"T1796263"."BUS_UNIT_SKID"=14350 OR "T1796263"."BUS_UNIT_SKID"=14800 OR "T1796263"."BUS_UNIT_SKID"=14801)
10 - filter(ROWNUM>=1)
16 - access("T1796263"."BUS_UNIT_NAME"='Japan')
19 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
21 - filter("T1792779"."ACCT_SKID"="T1796906"."ACCT_PRMTN_SKID" AND "T1792779"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID" AND
"T1796263"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID")
22 - filter(CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END
='Corporate' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE"
END ='Planned' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE"
END ='Private')
23 - access("T1796631"."PRMTN_SKID"="T1796906"."PRMTN_SKID" AND "T1796631"."BUS_UNIT_SKID"="T1796906"."BUS_UNIT_SKID")
24 - access("T1796564"."CAL_MASTR_SKID"="T1796906"."DATE_SKID")
25 - filter("T1796564"."FISC_YR_ABBR_NAME"='FY10/11')
27 - access("T1796631"."PRMTN_LONG_NAME"="C1")
28 - filter(CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE" END
='Corporate' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE"
END ='Planned' OR CASE "T1796631"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T1796631"."CORP_PRMTN_TYPE_CODE"
END ='Private')
32 - access("T1796263"."BUS_UNIT_NAME"='Japan')
filter("T1796263"."BUS_UNIT_SKID"=0 OR "T1796263"."BUS_UNIT_SKID"=11769 OR "T1796263"."BUS_UNIT_SKID"=11772 OR
"T1796263"."BUS_UNIT_SKID"=11774 OR "T1796263"."BUS_UNIT_SKID"=11777 OR "T1796263"."BUS_UNIT_SKID"=11779 OR
"T1796263"."BUS_UNIT_SKID"=11780 OR "T1796263"."BUS_UNIT_SKID"=14329 OR "T1796263"."BUS_UNIT_SKID"=14334 OR
"T1796263"."BUS_UNIT_SKID"=14339 OR "T1796263"."BUS_UNIT_SKID"=14340 OR "T1796263"."BUS_UNIT_SKID"=14341 OR
"T1796263"."BUS_UNIT_SKID"=14350 OR "T1796263"."BUS_UNIT_SKID"=14800 OR "T1796263"."BUS_UNIT_SKID"=14801)
34 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
filter("T1792779"."ACCT_LONG_NAME" IS NOT NULL)
37 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM ( (SELECT DISTINCT "AP"."ORG_SKID" "ORG_SKID" FROM
"ADWG_OPTIMA_AP11"."OPT_USER_LKP" "U","ADWG_OPTIMA_AP11"."OPT_PARTY_PERSN_LKP" "PP","ADWG_OPTIMA_AP11"."OPT_ACCT_POSTN_LKP" "AP" WHERE
"AP"."ORG_SKID"=:B1 AND "AP"."POSTN_ID"="PP"."PARTY_ID" AND "PP"."PERSN_ID"="U"."USER_ID" AND "U"."LOGIN_NAME"='BT0016')UNION (SELECT 0
"ORG_SKID" FROM "SYS"."DUAL" "DUAL" WHERE 0=:B2)) "ORG"))
38 - access("T1796547"."WK_SKID"="T1796564"."CAL_MASTR_SKID")
39 - access("T1792779"."ACCT_SKID"="T1796547"."ACCT_SKID" AND "T1792779"."BUS_UNIT_SKID"="T1796547"."BUS_UNIT_SKID")
40 - access("C1"="T1796547"."BUS_UNIT_SKID" AND "T1796547"."BUS_UNIT_SKID"="C2" AND "T1796547"."PRMTN_SKID"="C0")
49 - access("T1796547"."PRMTN_SKID"="C0")
filter("T1796547"."PRMTN_SKID" IS NOT NULL)
54 - access("T1796547"."ACCT_SKID"="C0")
58 - access("T1792779"."ACCT_LONG_NAME"='FN-AEON_GROUP(JUSCOJ4)(C005) - 1900001326')
filter("T1792779"."ACCT_LONG_NAME" IS NOT NULL)
59 - filter("T1796564"."FISC_YR_ABBR_NAME"='FY10/11')
63 - access("AP"."POSTN_ID"="PP"."PARTY_ID")
66 - access("U"."LOGIN_NAME"='BT0016')
67 - access("PP"."PERSN_ID"="U"."USER_ID")
69 - access("AP"."ORG_SKID"=:B1)
70 - filter(0=:B1)
Note
-----
- star transformation used for this statement
135 rows selected.
跑一下SQL看看要多久
.....省略.......
80 rows selected.
Elapsed: 00:00:12.29
结果只需要12秒。
大家看执行计划 ID=37，其实CBO把IN 转换成了EXISTS，所以最终的优化方案应该是改写 IN，可以把IN改写为EXISTS，不过这个也懒得提了，就这样吧，俺就是个懒人。
那么大家也知道了，网上很多人说10g过后 in,exists是等价的，还有很多人看了什么时候该用in，什么时候该用exists，个人觉得，没有什么是必然的，最重要的一点就是要知道 in, exists可以相互转换的原理。
扫一扫
886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
