一次使用 Extended Statistics 优化SQL案例

从Oracle 11g开始，Oracle提供了 Extended Statistics 新特征，本案例就恰好利用了这个新特征。

OBIEE终端用户发来邮件说某某报表慢（跑了30分钟还不出结果），请求DBA调查。通过和OBIEE的人合作，找到报表的SQL如下：
select sum（T2083114.MANUL_COST_OVRRD_AMT） as c1,
sum（nvl（T2083114.REVSD_VAR_ESTMT_COST_AMT , 0）） as c2,
T2084525.ACCT_LONG_NAME as c3,
T2084525.NAME as c4,
T2083424.PRMTN_NAME as c5,
T2083424.PRMTN_ID as c6,
case when case when T2083424.CORP_PRMTN_TYPE_CODE = "Target Account"
then "Corporate" else T2083424.CORP_PRMTN_TYPE_CODE end is null
then "Private" else case when T2083424.CORP_PRMTN_TYPE_CODE = "Target Account"
then "Corporate" else T2083424.CORP_PRMTN_TYPE_CODE end end as c7,
T2083424.PRMTN_STTUS_CODE as c8,
T2083424.APPRV_BY_DESC as c9,
T2083424.APPRV_STTUS_CODE as c10,
T2083424.AUTO_UPDT_GTIN_IND as c11,
T2083424.CREAT_DATE as c12,
T2083424.PGM_START_DATE as c13,
T2083424.PGM_END_DATE as c14,
nvl（case when T2083424.PRMTN_STTUS_CODE = "Confirmed"
then cast（（ TRUNC（ TO_DATE（"2011-06-07" , "YYYY-MM-DD"）） - TRUNC（ T2083424.PGM_END_DATE ）） as VARCHAR （ 10 ）） end , ""） as c15,
T2083424.PRMTN_STOP_DATE as c16,
T2083424.SHPMT_START_DATE as c17,
T2083424.SHPMT_END_DATE as c18,
T2083424.CNBLN_WK_CNT as c19,
T2083424.ACTVY_DETL_POP as c20,
T2083424.CMMNT_DESC as c21,
T2083424.PRMTN_AVG_POP as c22,
T2084525.CHANL_TYPE_DESC as c23,
T2083424.PRMTN_SKID as c24
from
ADWG_OPTIMA_LA11.OPT_ACCT_FDIM T2084525 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_LA11.OPT_BUS_UNIT_FDIM T2083056,
ADWG_OPTIMA_LA11.OPT_CAL_MASTR_DIM T2083357 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_LA11.OPT_PRMTN_FDIM T2083424,
ADWG_OPTIMA_LA11.OPT_ACTVY_FCT T2083114
where （ T2083056.BUS_UNIT_SKID = T2083114.BUS_UNIT_SKID and T2083114.BUS_UNIT_SKID = T2084525.BUS_UNIT_SKID
and T2083114.DATE_SKID = T2083357.CAL_MASTR_SKID and T2083114.BUS_UNIT_SKID = T2083424.BUS_UNIT_SKID
and T2083114.PRMTN_SKID = T2083424.PRMTN_SKID and T2083056.BUS_UNIT_NAME = "Chile"
and T2083114.ACCT_PRMTN_SKID = T2084525.ACCT_SKID and T2083357.FISC_YR_ABBR_NAME = "FY10/11"
and T2084525.ACCT_LONG_NAME is not null and （case when T2083424.CORP_PRMTN_TYPE_CODE = "Target Account"
then "Corporate" else T2083424.CORP_PRMTN_TYPE_CODE end in （"Alternate BDF", "Corporate", "Private"））
and （T2084525.ACCT_LONG_NAME in （"ADELCO - CHILE - 0066009018", "ALIMENTOS FRUNA - CHILE - 0066009049",
"CENCOSUD - CHILE - 0066009007", "COMERCIAL ALVI - CHILE - 0066009070", "D&S - CHILE - 0066009008",
"DIPAC - CHILE - 0066009024", "DIST. COMERCIAL - CHILE - 0066009087", "DISTRIBUCION LAGOS S.A. - CHILE - 2001146505",
"ECOMMERCE ESCALA 1 - 1900001746", "EMILIO SANDOVAL - CHILE - 2000402293", "F. AHUMADA - CHILE - 0066009023",
"FALABELLA - CHILE - 2000406971", "FRANCISCO LEYTON - CHILE - 0066009142", "MAICAO - CHILE - 0066009135",
"MARGARITA UAUY - CHILE - 0066009146", "PREUNIC - CHILE - 0066009032", "PRISA DISTRIBUCION - CHILE - 2001419970",
"RABIE - CHILE - 0066009015", "S Y B FARMACEUTICA S.A. - CHILE - 2000432938",
"SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967", "SOCOFAR - CHILE - 0066009028",
"SODIMAC - CHILE - 2000402358", "SOUTHERN CROSS - CHILE - 2002135799",
"SUPERM. MONSERRAT - CHILE - 0066009120", "TELEMERCADOS EUROPA - CHILE - 0066009044"））
and T2083424.PRMTN_LONG_NAME in （select distinct T2083424.PRMTN_LONG_NAME as c1
from
ADWG_OPTIMA_LA11.OPT_ACCT_FDIM T2084525 /* OPT_ACCT_PRMTN_FDIM */ ,
ADWG_OPTIMA_LA11.OPT_BUS_UNIT_FDIM T2083056,
ADWG_OPTIMA_LA11.OPT_CAL_MASTR_DIM T2083357 /* OPT_CAL_MASTR_DIM01 */ ,
ADWG_OPTIMA_LA11.OPT_PRMTN_FDIM T2083424,
ADWG_OPTIMA_LA11.OPT_PRMTN_PROD_FLTR_LKP T2083698
where （ T2083056.BUS_UNIT_SKID = T2083698.BUS_UNIT_SKID and T2083357.CAL_MASTR_SKID = T2083698.DATE_SKID
and T2083698.ACCT_PRMTN_SKID = T2084525.ACCT_SKID and T2083424.PRMTN_SKID = T2083698.PRMTN_SKID
and T2083424.BUS_UNIT_SKID = T2083698.BUS_UNIT_SKID and T2083056.BUS_UNIT_NAME = "Chile"
and T2083357.FISC_YR_ABBR_NAME = "FY10/11" and T2083698.BUS_UNIT_SKID = T2084525.BUS_UNIT_SKID
and （case when T2083424.CORP_PRMTN_TYPE_CODE = "Target Account" then "Corporate"
else T2083424.CORP_PRMTN_TYPE_CODE end in （"Alternate BDF", "Corporate", "Private"））
and （T2084525.ACCT_LONG_NAME in （"ADELCO - CHILE - 0066009018",
"ALIMENTOS FRUNA - CHILE - 0066009049", "CENCOSUD - CHILE - 0066009007",
"COMERCIAL ALVI - CHILE - 0066009070", "D&S - CHILE - 0066009008",
"DIPAC - CHILE - 0066009024", "DIST. COMERCIAL - CHILE - 0066009087",
"DISTRIBUCION LAGOS S.A. - CHILE - 2001146505", "ECOMMERCE ESCALA 1 - 1900001746",
"EMILIO SANDOVAL - CHILE - 2000402293", "F. AHUMADA - CHILE - 0066009023",
"FALABELLA - CHILE - 2000406971", "FRANCISCO LEYTON - CHILE - 0066009142",
"MAICAO - CHILE - 0066009135", "MARGARITA UAUY - CHILE - 0066009146",
"PREUNIC - CHILE - 0066009032", "PRISA DISTRIBUCION - CHILE - 2001419970",
"RABIE - CHILE - 0066009015", "S Y B FARMACEUTICA S.A. - CHILE - 2000432938",
"SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967", "SOCOFAR - CHILE - 0066009028",
"SODIMAC - CHILE - 2000402358", "SOUTHERN CROSS - CHILE - 2002135799",
"SUPERM. MONSERRAT - CHILE - 0066009120", "TELEMERCADOS EUROPA - CHILE - 0066009044"）））））
group by T2083424.PRMTN_SKID, T2083424.PRMTN_ID, T2083424.PRMTN_NAME, T2083424.SHPMT_END_DATE,
T2083424.SHPMT_START_DATE, T2083424.PRMTN_STTUS_CODE, T2083424.APPRV_STTUS_CODE, T2083424.CMMNT_DESC,
T2083424.PGM_START_DATE, T2083424.PGM_END_DATE, T2083424.CREAT_DATE, T2083424.APPRV_BY_DESC,
T2083424.AUTO_UPDT_GTIN_IND, T2083424.PRMTN_STOP_DATE, T2083424.ACTVY_DETL_POP, T2083424.CNBLN_WK_CNT,
T2083424.PRMTN_AVG_POP, T2084525.NAME, T2084525.CHANL_TYPE_DESC, T2084525.ACCT_LONG_NAME,
case when case when T2083424.CORP_PRMTN_TYPE_CODE = "Target Account" then "Corporate"
else T2083424.CORP_PRMTN_TYPE_CODE end is null then "Private" else case
when T2083424.CORP_PRMTN_TYPE_CODE = "Target Account" then "Corporate"
else T2083424.CORP_PRMTN_TYPE_CODE end end ,
nvl（case when T2083424.PRMTN_STTUS_CODE = "Confirmed"
then cast（（ TRUNC（ TO_DATE（"2011-06-07" , "YYYY-MM-DD"）） - TRUNC（ T2083424.PGM_END_DATE ）） as VARCHAR （ 10 ）） end , ""）
order by c24, c3;
这个SQL要用到的表信息如下
OWNER TABLE_NAME Size（Mb） PARTITIONED DEGREE NUM_ROWS
-------------------- ------------------------------ ---------- -------------------- ---------- -------------
ADWG_OPTIMA_LA11 *OPT_BUS_UNIT_FDIM .001037598 NO 1 16
ADWG_OPTIMA_LA11 *OPT_CAL_MASTR_DIM 38.1284523 NO 1 37435
ADWG_OPTIMA_LA11 OPT_CAL_MASTR_DIM 38.1284523 NO 1 37435
ADWG_OPTIMA_LA11 *OPT_PRMTN_FDIM 74.6365929 YES 1 52140
ADWG_OPTIMA_LA11 OPT_PRMTN_FDIM 74.6365929 YES 1 52140
ADWG_OPTIMA_LA11 OPT_ACTVY_FCT 19.3430614 YES 1 157230
ADWG_OPTIMA_LA11 *OPT_ACCT_FDIM 36.6709185 YES 2 95415
ADWG_OPTIMA_LA11 OPT_ACCT_FDIM 36.6709185 YES 2 95415
ADWG_OPTIMA_LA11 OPT_PRMTN_PROD_FLTR_LKP 1523.87207 YES 2 30148975
带*表示它用到了索引那么这里只有表OPT_PRMTN_PROD_FLTR_LKP是大表，它有3千多万数据，1.5G 现在来看看这个SQL的执行计划：
SQL> select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3566115627
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 352 | 1551 （17）| 00:00:07 | | |
| 1 | SORT GROUP BY | | 1 | 352 | 1551 （17）| 00:00:07 | | |
| 2 | VIEW | VM_NWVW_2 | 1 | 352 | 1550 （17）| 00:00:07 | | |
| 3 | HASH UNIQUE | | 1 | 652 | 1550 （17）| 00:00:07 | | |
| 4 | NESTED LOOPS | | | | | | | |
| 5 | NESTED LOOPS | | 1 | 652 | 1549 （17）| 00:00:07 | | |
| 6 | NESTED LOOPS | | 1 | 639 | 1548 （17）| 00:00:07 | | |
| 7 | NESTED LOOPS | | 2 | 1180 | 1546 （17）| 00:00:07 | | |
| 8 | NESTED LOOPS | | 1 | 568 | 130 （5）| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 509 | 109 （6）| 00:00:01 | | |
| 10 | NESTED LOOPS | | 1 | 484 | 108 （6）| 00:00:01 | | |
|* 11 | HASH JOIN | | 5 | 830 | 103 （6）| 00:00:01 | | |
| 12 | PARTITION LIST SUBQUERY | | 47 | 4089 | 82 （3）| 00:00:01 |KEY（SQ）|KEY（SQ）|
| 13 | INLIST ITERATOR | | | | | | | |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 47 | 4089 | 82 （3）| 00:00:01 |KEY（SQ）|KEY（SQ）|
|* 15 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 47 | | 43 （5）| 00:00:01 |KEY（SQ）|KEY（SQ）|
| 16 | NESTED LOOPS | | 10482 | 808K| 20 （15）| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 40 | 2 （0）| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 26 | 1 （0）| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 （0）| 00:00:01 | | |
| 20 | PARTITION LIST ITERATOR | | 10482 | 1699K| 18 （17）| 00:00:01 | KEY | KEY |
|* 21 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 10482 | 1699K| 18 （17）| 00:00:01 | KEY | KEY |
|* 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 318 | 1 （0）| 00:00:01 | ROWID | ROWID |
|* 23 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 （0）| 00:00:01 | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 25 | 1 （0）| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 （0）| 00:00:01 | | |
| 26 | PARTITION LIST ALL | | 1 | 59 | 21 （0）| 00:00:01 | 1 | 17 |
|* 27 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 59 | 21 （0）| 00:00:01 | 1 | 17 |
|* 28 | INDEX RANGE SCAN | OPT_PRMTN_FDIM_NX3 | 4 | | 17 （0）| 00:00:01 | 1 | 17 |
| 29 | PARTITION LIST ITERATOR | | 39 | 858 | 1416 （18）| 00:00:07 | KEY | KEY |
|* 30 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FLTR_LKP | 39 | 858 | 1416 （18）| 00:00:07 | KEY | KEY |
|* 31 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 49 | 1 （0）| 00:00:01 | ROWID | ROWID |
|* 32 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 1 | | 0 （0）| 00:00:01 | | |
|* 33 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 （0）| 00:00:01 | | |
|* 34 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 （0）| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
11 - access（"T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID"）
15 - access（"T2084525"."ACCT_LONG_NAME"="ADELCO - CHILE - 0066009018" OR "T2084525"."ACCT_LONG_NAME"="ALIMENTOS FRUNA - CHILE -
0066009049" OR "T2084525"."ACCT_LONG_NAME"="CENCOSUD - CHILE - 0066009007" OR "T2084525"."ACCT_LONG_NAME"="COMERCIAL ALVI - CHILE
- 0066009070" OR "T2084525"."ACCT_LONG_NAME"="D&S - CHILE - 0066009008" OR "T2084525"."ACCT_LONG_NAME"="DIPAC - CHILE -
0066009024" OR "T2084525"."ACCT_LONG_NAME"="DIST. COMERCIAL - CHILE - 0066009087" OR "T2084525"."ACCT_LONG_NAME"="DISTRIBUCION
LAGOS S.A. - CHILE - 2001146505" OR "T2084525"."ACCT_LONG_NAME"="ECOMMERCE ESCALA 1 - 1900001746" OR
"T2084525"."ACCT_LONG_NAME"="EMILIO SANDOVAL - CHILE - 2000402293" OR "T2084525"."ACCT_LONG_NAME"="F. AHUMADA - CHILE -
0066009023" OR "T2084525"."ACCT_LONG_NAME"="FALABELLA - CHILE - 2000406971" OR "T2084525"."ACCT_LONG_NAME"="FRANCISCO LEYTON -
CHILE - 0066009142" OR "T2084525"."ACCT_LONG_NAME"="MAICAO - CHILE - 0066009135" OR "T2084525"."ACCT_LONG_NAME"="MARGARITA UAUY -
CHILE - 0066009146" OR "T2084525"."ACCT_LONG_NAME"="PREUNIC - CHILE - 0066009032" OR "T2084525"."ACCT_LONG_NAME"="PRISA
DISTRIBUCION - CHILE - 2001419970" OR "T2084525"."ACCT_LONG_NAME"="RABIE - CHILE - 0066009015" OR "T2084525"."ACCT_LONG_NAME"="S
Y B FARMACEUTICA S.A. - CHILE - 2000432938" OR "T2084525"."ACCT_LONG_NAME"="SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967" OR
"T2084525"."ACCT_LONG_NAME"="SOCOFAR - CHILE - 0066009028" OR "T2084525"."ACCT_LONG_NAME"="SODIMAC - CHILE - 2000402358" OR
"T2084525"."ACCT_LONG_NAME"="SOUTHERN CROSS - CHILE - 2002135799" OR "T2084525"."ACCT_LONG_NAME"="SUPERM. MONSERRAT - CHILE -
0066009120" OR "T2084525"."ACCT_LONG_NAME"="TELEMERCADOS EUROPA - CHILE - 0066009044"）
filter（"T2084525"."ACCT_LONG_NAME" IS NOT NULL）
18 - access（"T2083056"."BUS_UNIT_NAME"="Chile"）
19 - access（"T2083056"."BUS_UNIT_NAME"="Chile"）
21 - filter（"T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID"）
22 - filter（CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN "Target Account" THEN "Corporate" ELSE
"T2083424"."CORP_PRMTN_TYPE_CODE" END ="Alternate BDF" OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN "Target Account" THEN
"Corporate" ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ="Corporate" OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN "Target
Account" THEN "Corporate" ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ="Private"）
23 - access（"T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID"）
24 - filter（"T2083357"."FISC_YR_ABBR_NAME"="FY10/11"）
25 - access（"T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID"）
27 - filter（CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN "Target Account" THEN "Corporate" ELSE
"T2083424"."CORP_PRMTN_TYPE_CODE" END ="Alternate BDF" OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN "Target Account" THEN
"Corporate" ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ="Corporate" OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN "Target
Account" THEN "Corporate" ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ="Private"）
28 - access（"T2083424"."PRMTN_LONG_NAME"="T2083424"."PRMTN_LONG_NAME"）
30 - filter（"T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID" AND
"T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID"）
31 - filter（"T2084525"."ACCT_LONG_NAME"="ADELCO - CHILE - 0066009018" OR "T2084525"."ACCT_LONG_NAME"="ALIMENTOS FRUNA - CHILE -
0066009049" OR "T2084525"."ACCT_LONG_NAME"="CENCOSUD - CHILE - 0066009007" OR "T2084525"."ACCT_LONG_NAME"="COMERCIAL ALVI - CHILE
- 0066009070" OR "T2084525"."ACCT_LONG_NAME"="D&S - CHILE - 0066009008" OR "T2084525"."ACCT_LONG_NAME"="DIPAC - CHILE -
0066009024" OR "T2084525"."ACCT_LONG_NAME"="DIST. COMERCIAL - CHILE - 0066009087" OR "T2084525"."ACCT_LONG_NAME"="DISTRIBUCION
LAGOS S.A. - CHILE - 2001146505" OR "T2084525"."ACCT_LONG_NAME"="ECOMMERCE ESCALA 1 - 1900001746" OR
"T2084525"."ACCT_LONG_NAME"="EMILIO SANDOVAL - CHILE - 2000402293" OR "T2084525"."ACCT_LONG_NAME"="F. AHUMADA - CHILE -
0066009023" OR "T2084525"."ACCT_LONG_NAME"="FALABELLA - CHILE - 2000406971" OR "T2084525"."ACCT_LONG_NAME"="FRANCISCO LEYTON -
CHILE - 0066009142" OR "T2084525"."ACCT_LONG_NAME"="MAICAO - CHILE - 0066009135" OR "T2084525"."ACCT_LONG_NAME"="MARGARITA UAUY -
CHILE - 0066009146" OR "T2084525"."ACCT_LONG_NAME"="PREUNIC - CHILE - 0066009032" OR "T2084525"."ACCT_LONG_NAME"="PRISA
DISTRIBUCION - CHILE - 2001419970" OR "T2084525"."ACCT_LONG_NAME"="RABIE - CHILE - 0066009015" OR "T2084525"."ACCT_LONG_NAME"="S
Y B FARMACEUTICA S.A. - CHILE - 2000432938" OR "T2084525"."ACCT_LONG_NAME"="SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967" OR
"T2084525"."ACCT_LONG_NAME"="SOCOFAR - CHILE - 0066009028" OR "T2084525"."ACCT_LONG_NAME"="SODIMAC - CHILE - 2000402358" OR
"T2084525"."ACCT_LONG_NAME"="SOUTHERN CROSS - CHILE - 2002135799" OR "T2084525"."ACCT_LONG_NAME"="SUPERM. MONSERRAT - CHILE -
0066009120" OR "T2084525"."ACCT_LONG_NAME"="TELEMERCADOS EUROPA - CHILE - 0066009044"）
32 - access（"T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID"）
33 - access（"T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID"）
34 - filter（"T2083357"."FISC_YR_ABBR_NAME"="FY10/11"）
95 rows selected.
注意观察 ID=30 它走的是全表扫描并且优化器认为它只返回39行数据，www.linuxidc.com 那么问题可能出在这里了于是创建如下索引
SQL> create index OPT_PRMTN_PROD_FLTR_LKP_NX1 ON OPT_PRMTN_PROD_FLTR_LKP（BUS_UNIT_SKID,PRMTN_SKID） nologging parallel ;
Index created.
Elapsed: 00:00:33.04
关于为什么我要这样创建索引，这里就不说了，如果不明白的请看我前面博客，创建索引之后 SQL能在4分钟以内跑完，下面是这个SQL的特殊执行计划
Plan hash value: 1310530159
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1324 |00:02:42.23 |
| 1 | SORT GROUP BY | | 1 | 1 | 1324 |00:02:42.23 |
| 2 | VIEW | VM_NWVW_2 | 1 | 1 | 6808 |00:02:42.18 |
| 3 | HASH UNIQUE | | 1 | 1 | 6808 |00:02:42.18 |
| 4 | NESTED LOOPS |