本帖最后由 happywangkui 于 2014-11-10 22:16 編輯

直接上之前遇到的問題：

1、三個組合索引如下：

CREATE??INDEX IDX_TRAD_SK_HKDAILY_STM ON TRAD_SK_HKDAILY

(SECUCODE,TDATE, MARKET)

CREATE??INDEX IDX_TRAD_SK_HKDAILY_TSM ON TRAD_SK_HKDAILY

(TDATE, SECUCODE, MARKET)

CREATE??INDEX IDX_TRAD_SK_HKDAILY_SMT ON TRAD_SK_HKDAILY

(SECUCODE, MARKE,TTDATE)

2、說明一下列的區分度：

TDATE：8000多個不同值

SECUCODE：1000多個不同值

MARKET：1個唯一值

區分度大小：TDATE>SECUCODE>MARKET

3、下面做三種測試：分別走上面不同索引：

第一種走：IDX_TRAD_SK_HKDAILY_STM索引

SELECT??COUNT(ID)

FROM

(

SELECT /*+ index(E IDX_TRAD_SK_HKDAILY_STM)*/

rownum ID,

A.CDSY_SECUCODE_EID? ?? ?? ?? ?,

A.SPTM_MARKETRELATION_EID? ?? ?,

B.HK_SHORTTD_LIST_EID? ?? ?? ? ,

case when C.PUBLISHCODE??like '402%' then C.CUST_PUBLISHSTOCKRELATION_EID else null end CUST_PUBLISHSTOCKRELATION_EID,

case when C.PUBLISHCODE??like '402%' then C.CUST_PUBLISHRELATION_EID else null end CUST_PUBLISHRELATION_EID,

E.TRAD_SK_HKDAILY_EID? ?? ?? ? ,

A.MSECUCODE? ?? ?? ?? ?? ?? ???,

A.SECURITYSHORTNAME? ?? ?? ?? ?,

B.TDATE DAT_TDATE? ?? ?? ?? ???,

B.TIMEFRAME? ?? ?? ?? ?? ?? ???,

B.CURRENCY? ?? ?? ?? ?? ?? ?? ?,

B.TVOL? ?? ?? ?? ?? ?? ?? ?? ? ,

B.TVAL? ?? ?? ?? ?? ?? ?? ?? ? ,

E.TNUM? ?? ?? ?? ?? ?? ?? ?? ? ,

E.TAMT? ?? ?? ?? ?? ?? ?? ?? ? ,

case when C.PUBLISHCODE??like '402%' then C.PUBLISHNAME else null end STR_GICS,

case when C.PUBLISHCODE??like '403%' then C.PUBLISHNAME else null end STR_GJSHY,

A.SECURITYTYPE? ?? ?? ?? ?? ???,

A.TRADEMARKET? ?? ?? ?? ?? ?? ?,

A.TRADEMARKETCODE

FROM

(

SELECT??A.EID CDSY_SECUCODE_EID? ?? ?,

B.EID SPTM_MARKETRELATION_EID,

SECURITYCODE? ?? ?? ?? ?? ???,

A.SECURITYCODE

||

B.MARKETRELEATION MSECUCODE,

SECURITYSHORTNAME? ?? ?? ? ,

SECURITYTYPE? ?? ?? ?? ?? ?,

TRADEMARKET? ?? ?? ?? ?? ? ,

COMPANYCODE? ?? ?? ?? ?? ? ,

SECURITYVARIETYCODE? ?? ???,

TRADEMARKETCODE

FROM? ? CDSY_SECUCODE A

JOIN SPTM_MARKETRELATION B

ON? ?? ?A.TRADEMARKETCODE=B.MARKETCODE

WHERE? ?SECURITYTYPECODE LIKE'058001003%'

AND USESTATE? ???='1'

AND LISTINGSTATE<>'2'

)

A

JOIN

(

SELECT??EID HK_SHORTTD_LIST_EID? ?? ?? ? ,

HKCODE? ?? ?? ?? ?? ?? ?? ?? ?? ?,

SECURITYCODE? ?? ?? ?? ?? ?? ?? ?,

TO_DATE(TDATE,'YYYY/MM/DD') TDATE,

CURRENCY? ?? ?? ?? ?? ?? ?? ?? ? ,

TIMEFRAME? ?? ?? ?? ?? ?? ?? ?? ?,

TVOL? ?? ?? ?? ?? ?? ?? ?? ?? ???,

TVAL

FROM? ? HK_SHORTTD_LIST

)

B

ON? ?? ?A.SECURITYVARIETYCODE=B.HKCODE

AND A.SECURITYCODE? ?=B.SECURITYCODE

LEFT JOIN

(

SELECT??A.EID CUST_PUBLISHSTOCKRELATION_EID,

B.EID CUST_PUBLISHRELATION_EID? ???,

COMPANYCODE? ?? ?? ?? ?? ?? ?? ?? ?,

SECURITYCODE? ?? ?? ?? ?? ?? ?? ???,

PUBLISHNAME,

a.PUBLISHCODE

FROM

(

SELECT??EID? ?? ?? ?,

COMPANYCODE ,

SECURITYCODE,

PUBLISHCODE

FROM? ? CUST_PUBLISHSTOCKRELATION

WHERE? ?PUBLISHCODE LIKE'402%' or PUBLISHCODE LIKE'403%'

)

A

LEFT JOIN

(

SELECT??EID? ?? ???,

PUBLISHCODE,

PUBLISHNAME

FROM? ? CUST_PUBLISHRELATION

WHERE? ?PUBLISHCODE LIKE'402%' or PUBLISHCODE LIKE'403%'

)

B

ON? ?? ?A.PUBLISHCODE=B.PUBLISHCODE

)

C ON A.COMPANYCODE=C.COMPANYCODE

AND A.SECURITYCODE=C.SECURITYCODE

LEFT JOIN

(

SELECT??EID TRAD_SK_HKDAILY_EID,

SECUCODE? ?? ?? ?? ?? ?,

TDATE? ?? ?? ?? ?? ?? ?,

TNUM? ?? ?? ?? ?? ?? ? ,

TAMT

FROM? ? TRAD_SK_HKDAILY

)

E

ON? ???B.SECURITYCODE=E.SECUCODE

AND B.TDATE? ?=E.TDATE

)

統計信息如下：

call? ???count? ?? ? cpu? ? elapsed? ?? ? disk? ?? ?query? ? current? ?? ???rows

------- ------??-------- ---------- ---------- ---------- ----------??----------

Parse? ?? ???1? ?? ?0.05? ?? ? 0.05? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0

Execute? ?? ?1? ?? ?0.00? ?? ? 0.00? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0

Fetch? ?? ???1? ?? ?2.62? ?? ? 2.63? ?? ?? ? 1? ?? ?61565? ?? ?? ? 0? ?? ?? ???1

------- ------??-------- ---------- ---------- ---------- ----------??----------

total? ?? ???3? ?? ?2.68? ???2.69? ???1? ?61565? ?? ?? ?0? ?? ?? ???1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 157

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)??Row Source Operation

---------- ---------- ----------??---------------------------------------------------

1? ?? ?? ? 1? ?? ?? ? 1??SORT AGGREGATE (cr=61565 pr=1 pw=0 time=2632915 us)

176130? ???176130? ???176130? ?VIEW??(cr=61565 pr=1 pw=0 time=3204960 us cost=4094 size=13 card=1)

176130? ???176130? ???176130? ? COUNT??(cr=61565 pr=1 pw=0 time=2908975 us)

176130? ???176130? ???176130? ???NESTED LOOPS OUTER (cr=61565 pr=1 pw=0 time=2653411 us cost=4094 size=189 card=1)

176130? ???176130? ???176130? ?? ?HASH JOIN OUTER (cr=22567 pr=0 pw=0 time=1103999 us cost=4092 size=170 card=1)

89999? ?? ?89999? ?? ?89999? ?? ? NESTED LOOPS??(cr=2588 pr=0 pw=0 time=1000620 us cost=637 size=128 card=1)

89999? ?? ?89999? ?? ?89999? ?? ???HASH JOIN??(cr=2585 pr=0 pw=0 time=235999 us cost=637 size=112 card=1)

2302? ?? ? 2302? ?? ? 2302? ?? ?? ?TABLE ACCESS BY INDEX ROWID CDSY_SECUCODE (cr=817 pr=0 pw=0 time=15547 us cost=350 size=222998 card=2593)

2326? ?? ? 2326? ?? ? 2326? ?? ?? ? INDEX SKIP SCAN IDX_CDSY_SECUCODE_LS (cr=35 pr=0 pw=0 time=6057 us cost=74 size=0 card=2598)(object id 345164)

90234? ?? ?90234? ?? ?90234? ?? ?? ?TABLE ACCESS FULL HK_SHORTTD_LIST (cr=1768 pr=0 pw=0 time=97702 us cost=287 size=2164968 card=83268)

89999? ?? ?89999? ?? ?89999? ?? ???INDEX RANGE SCAN SPTM_MARKETRELATION_MARKETCODE (cr=3 pr=0 pw=0 time=380949 us cost=0 size=16 card=1)(object id 96435)

3487? ?? ? 3487? ?? ? 3487? ?? ? VIEW??(cr=19979 pr=0 pw=0 time=28541 us cost=3455 size=136080 card=3240)

3487? ?? ? 3487? ?? ? 3487? ?? ???HASH JOIN RIGHT OUTER (cr=19979 pr=0 pw=0 time=22913 us cost=3455 size=139320 card=3240)

385? ?? ???385? ?? ???385? ?? ?? ?INDEX FAST FULL SCAN IDX_CUST_PUBLISHRELATION1 (cr=126 pr=0 pw=0 time=3246 us cost=12 size=4440 card=370)(object id 140038)

3487? ?? ? 3487? ?? ? 3487? ?? ?? ?TABLE ACCESS FULL CUST_PUBLISHSTOCKRELATION (cr=19853 pr=0 pw=0 time=8106 us cost=3443 size=100440 card=3240)

171831? ???171831? ???171831? ???INDEX RANGE SCAN IDX_TRAD_SK_HKDAILY_STM (cr=38998 pr=1 pw=0 time=879908 us cost=2 size=19 card=1)(object id 402666)

第二種走：IDX_TRAD_SK_HKDAILY_TSM索引

SELECT??COUNT(ID)

FROM

(

SELECT /*+ index(E IDX_TRAD_SK_HKDAILY_TSM)*/

rownum ID,

A.CDSY_SECUCODE_EID? ?? ?? ?? ?,

A.SPTM_MARKETRELATION_EID? ?? ?,

B.HK_SHORTTD_LIST_EID? ?? ?? ? ,

case when C.PUBLISHCODE??like '402%' then C.CUST_PUBLISHSTOCKRELATION_EID else null end CUST_PUBLISHSTOCKRELATION_EID,

case when C.PUBLISHCODE??like '402%' then C.CUST_PUBLISHRELATION_EID else null end CUST_PUBLISHRELATION_EID,

E.TRAD_SK_HKDAILY_EID? ?? ?? ? ,

A.MSECUCODE? ?? ?? ?? ?? ?? ???,

A.SECURITYSHORTNAME? ?? ?? ?? ?,

B.TDATE DAT_TDATE? ?? ?? ?? ???,

B.TIMEFRAME? ?? ?? ?? ?? ?? ???,

B.CURRENCY? ?? ?? ?? ?? ?? ?? ?,

B.TVOL? ?? ?? ?? ?? ?? ?? ?? ? ,

B.TVAL? ?? ?? ?? ?? ?? ?? ?? ? ,

E.TNUM? ?? ?? ?? ?? ?? ?? ?? ? ,

E.TAMT? ?? ?? ?? ?? ?? ?? ?? ? ,

case when C.PUBLISHCODE??like '402%' then C.PUBLISHNAME else null end STR_GICS,

case when C.PUBLISHCODE??like '403%' then C.PUBLISHNAME else null end STR_GJSHY,

A.SECURITYTYPE? ?? ?? ?? ?? ???,

A.TRADEMARKET? ?? ?? ?? ?? ?? ?,

A.TRADEMARKETCODE

FROM

(

SELECT??A.EID CDSY_SECUCODE_EID? ?? ?,

B.EID SPTM_MARKETRELATION_EID,

SECURITYCODE? ?? ?? ?? ?? ???,

A.SECURITYCODE

||

B.MARKETRELEATION MSECUCODE,

SECURITYSHORTNAME? ?? ?? ? ,

SECURITYTYPE? ?? ?? ?? ?? ?,

TRADEMARKET? ?? ?? ?? ?? ? ,

COMPANYCODE? ?? ?? ?? ?? ? ,

SECURITYVARIETYCODE? ?? ???,

TRADEMARKETCODE

FROM? ? CDSY_SECUCODE A

JOIN SPTM_MARKETRELATION B

ON? ?? ?A.TRADEMARKETCODE=B.MARKETCODE

WHERE? ?SECURITYTYPECODE LIKE'058001003%'

AND USESTATE? ???='1'

AND LISTINGSTATE<>'2'

)

A

JOIN

(

SELECT??EID HK_SHORTTD_LIST_EID? ?? ?? ? ,

HKCODE? ?? ?? ?? ?? ?? ?? ?? ?? ?,

SECURITYCODE? ?? ?? ?? ?? ?? ?? ?,

TO_DATE(TDATE,'YYYY/MM/DD') TDATE,

CURRENCY? ?? ?? ?? ?? ?? ?? ?? ? ,

TIMEFRAME? ?? ?? ?? ?? ?? ?? ?? ?,

TVOL? ?? ?? ?? ?? ?? ?? ?? ?? ???,

TVAL

FROM? ? HK_SHORTTD_LIST

)

B

ON? ?? ?A.SECURITYVARIETYCODE=B.HKCODE

AND A.SECURITYCODE? ?=B.SECURITYCODE

LEFT JOIN

(

SELECT??A.EID CUST_PUBLISHSTOCKRELATION_EID,

B.EID CUST_PUBLISHRELATION_EID? ???,

COMPANYCODE? ?? ?? ?? ?? ?? ?? ?? ?,

SECURITYCODE? ?? ?? ?? ?? ?? ?? ???,

PUBLISHNAME,

a.PUBLISHCODE

FROM

(

SELECT??EID? ?? ?? ?,

COMPANYCODE ,

SECURITYCODE,

PUBLISHCODE

FROM? ? CUST_PUBLISHSTOCKRELATION

WHERE? ?PUBLISHCODE LIKE'402%' or PUBLISHCODE LIKE'403%'

)

A

LEFT JOIN

(

SELECT??EID? ?? ???,

PUBLISHCODE,

PUBLISHNAME

FROM? ? CUST_PUBLISHRELATION

WHERE? ?PUBLISHCODE LIKE'402%' or PUBLISHCODE LIKE'403%'

)

B

ON? ?? ?A.PUBLISHCODE=B.PUBLISHCODE

)

C ON A.COMPANYCODE=C.COMPANYCODE

AND A.SECURITYCODE=C.SECURITYCODE

LEFT JOIN

(

SELECT??EID TRAD_SK_HKDAILY_EID,

SECUCODE? ?? ?? ?? ?? ?,

TDATE? ?? ?? ?? ?? ?? ?,

TNUM? ?? ?? ?? ?? ?? ? ,

TAMT

FROM? ? TRAD_SK_HKDAILY

)

E

ON? ???B.SECURITYCODE=E.SECUCODE

AND B.TDATE? ?=E.TDATE

)

統計信息如下：

call? ???count? ?? ? cpu? ? elapsed? ?? ? disk? ?? ?query? ? current? ?? ???rows

------- ------??-------- ---------- ---------- ---------- ----------??----------

Parse? ?? ???2? ?? ?0.00? ?? ? 0.00? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0

Execute? ?? ?2? ?? ?0.00? ?? ? 0.00? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0

Fetch? ?? ???2? ?? ?6.84? ?? ? 6.84? ?? ?? ? 0? ???751972? ?? ?? ? 0? ?? ?? ???2

------- ------??-------- ---------- ---------- ---------- ----------??----------

total? ?? ???6? ?? ?6.84? ?? ?6.84? ?? ?? ?0? ? 751972? ?? ?? ? 0? ?? ?? ???2

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 157

Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)??Row Source Operation

---------- ---------- ----------??---------------------------------------------------

1? ?? ?? ? 1? ?? ?? ? 1??SORT AGGREGATE (cr=375986 pr=0 pw=0 time=3422318 us)

176130? ???176130? ???176130? ?VIEW??(cr=375986 pr=0 pw=0 time=3980666 us cost=4094 size=13 card=1)

176130? ???176130? ???176130? ? COUNT??(cr=375986 pr=0 pw=0 time=3664535 us)

176130? ???176130? ???176130? ???NESTED LOOPS OUTER (cr=375986 pr=0 pw=0 time=3384154 us cost=4094 size=189 card=1)

176130? ???176130? ???176130? ?? ?HASH JOIN OUTER (cr=22567 pr=0 pw=0 time=1208307 us cost=4092 size=170 card=1)

89999? ?? ?89999? ?? ?89999? ?? ? NESTED LOOPS??(cr=2588 pr=0 pw=0 time=1026279 us cost=637 size=128 card=1)

89999? ?? ?89999? ?? ?89999? ?? ???HASH JOIN??(cr=2585 pr=0 pw=0 time=248928 us cost=637 size=112 card=1)

2302? ?? ? 2302? ?? ? 2302? ?? ?? ?TABLE ACCESS BY INDEX ROWID CDSY_SECUCODE (cr=817 pr=0 pw=0 time=20036 us cost=350 size=222998 card=2593)

2326? ?? ? 2326? ?? ? 2326? ?? ?? ? INDEX SKIP SCAN IDX_CDSY_SECUCODE_LS (cr=35 pr=0 pw=0 time=6840 us cost=74 size=0 card=2598)(object id 345164)

90234? ?? ?90234? ?? ?90234? ?? ?? ?TABLE ACCESS FULL HK_SHORTTD_LIST (cr=1768 pr=0 pw=0 time=111762 us cost=287 size=2164968 card=83268)

89999? ?? ?89999? ?? ?89999? ?? ???INDEX RANGE SCAN SPTM_MARKETRELATION_MARKETCODE (cr=3 pr=0 pw=0 time=384732 us cost=0 size=16 card=1)(object id 96435)

3487? ?? ? 3487? ?? ? 3487? ?? ? VIEW??(cr=19979 pr=0 pw=0 time=30230 us cost=3455 size=136080 card=3240)

3487? ?? ? 3487? ?? ? 3487? ?? ???HASH JOIN RIGHT OUTER (cr=19979 pr=0 pw=0 time=25166 us cost=3455 size=139320 card=3240)

385? ?? ???385? ?? ???385? ?? ?? ?INDEX FAST FULL SCAN IDX_CUST_PUBLISHRELATION1 (cr=126 pr=0 pw=0 time=3522 us cost=12 size=4440 card=370)(object id 140038)

3487? ?? ? 3487? ?? ? 3487? ?? ?? ?TABLE ACCESS FULL CUST_PUBLISHSTOCKRELATION (cr=19853 pr=0 pw=0 time=8789 us cost=3443 size=100440 card=3240)

171831? ???171831? ???171831? ???INDEX RANGE SCAN IDX_TRAD_SK_HKDAILY_TSM (cr=353419 pr=0 pw=0 time=1474190 us cost=2 size=19 card=1)(object id 402680)

第三種走：IDX_TRAD_SK_HKDAILY_SMT索引

SELECT??COUNT(ID)

FROM

(

SELECT /*+ index(E IDX_TRAD_SK_HKDAILY_SMT)*/

rownum ID,

A.CDSY_SECUCODE_EID? ?? ?? ?? ?,

A.SPTM_MARKETRELATION_EID? ?? ?,

B.HK_SHORTTD_LIST_EID? ?? ?? ? ,

case when C.PUBLISHCODE??like '402%' then C.CUST_PUBLISHSTOCKRELATION_EID else null end CUST_PUBLISHSTOCKRELATION_EID,

case when C.PUBLISHCODE??like '402%' then C.CUST_PUBLISHRELATION_EID else null end CUST_PUBLISHRELATION_EID,

E.TRAD_SK_HKDAILY_EID? ?? ?? ? ,

A.MSECUCODE? ?? ?? ?? ?? ?? ???,

A.SECURITYSHORTNAME? ?? ?? ?? ?,

B.TDATE DAT_TDATE? ?? ?? ?? ???,

B.TIMEFRAME? ?? ?? ?? ?? ?? ???,

B.CURRENCY? ?? ?? ?? ?? ?? ?? ?,

B.TVOL? ?? ?? ?? ?? ?? ?? ?? ? ,

B.TVAL? ?? ?? ?? ?? ?? ?? ?? ? ,

E.TNUM? ?? ?? ?? ?? ?? ?? ?? ? ,

E.TAMT? ?? ?? ?? ?? ?? ?? ?? ? ,

case when C.PUBLISHCODE??like '402%' then C.PUBLISHNAME else null end STR_GICS,

case when C.PUBLISHCODE??like '403%' then C.PUBLISHNAME else null end STR_GJSHY,

A.SECURITYTYPE? ?? ?? ?? ?? ???,

A.TRADEMARKET? ?? ?? ?? ?? ?? ?,

A.TRADEMARKETCODE

FROM

(

SELECT??A.EID CDSY_SECUCODE_EID? ?? ?,

B.EID SPTM_MARKETRELATION_EID,

SECURITYCODE? ?? ?? ?? ?? ???,

A.SECURITYCODE

||

B.MARKETRELEATION MSECUCODE,

SECURITYSHORTNAME? ?? ?? ? ,

SECURITYTYPE? ?? ?? ?? ?? ?,

TRADEMARKET? ?? ?? ?? ?? ? ,

COMPANYCODE? ?? ?? ?? ?? ? ,

SECURITYVARIETYCODE? ?? ???,

TRADEMARKETCODE

FROM? ? CDSY_SECUCODE A

JOIN SPTM_MARKETRELATION B

ON? ?? ?A.TRADEMARKETCODE=B.MARKETCODE

WHERE? ?SECURITYTYPECODE LIKE'058001003%'

AND USESTATE? ???='1'

AND LISTINGSTATE<>'2'

)

A

JOIN

(

SELECT??EID HK_SHORTTD_LIST_EID? ?? ?? ? ,

HKCODE? ?? ?? ?? ?? ?? ?? ?? ?? ?,

SECURITYCODE? ?? ?? ?? ?? ?? ?? ?,

TO_DATE(TDATE,'YYYY/MM/DD') TDATE,

CURRENCY? ?? ?? ?? ?? ?? ?? ?? ? ,

TIMEFRAME? ?? ?? ?? ?? ?? ?? ?? ?,

TVOL? ?? ?? ?? ?? ?? ?? ?? ?? ???,

TVAL

FROM? ? HK_SHORTTD_LIST

)

B

ON? ?? ?A.SECURITYVARIETYCODE=B.HKCODE

AND A.SECURITYCODE? ?=B.SECURITYCODE

LEFT JOIN

(

SELECT??A.EID CUST_PUBLISHSTOCKRELATION_EID,

B.EID CUST_PUBLISHRELATION_EID? ???,

COMPANYCODE? ?? ?? ?? ?? ?? ?? ?? ?,

SECURITYCODE? ?? ?? ?? ?? ?? ?? ???,

PUBLISHNAME,

a.PUBLISHCODE

FROM

(

SELECT??EID? ?? ?? ?,

COMPANYCODE ,

SECURITYCODE,

PUBLISHCODE

FROM? ? CUST_PUBLISHSTOCKRELATION

WHERE? ?PUBLISHCODE LIKE'402%' or PUBLISHCODE LIKE'403%'

)

A

LEFT JOIN

(

SELECT??EID? ?? ???,

PUBLISHCODE,

PUBLISHNAME

FROM? ? CUST_PUBLISHRELATION

WHERE? ?PUBLISHCODE LIKE'402%' or PUBLISHCODE LIKE'403%'

)

B

ON? ?? ?A.PUBLISHCODE=B.PUBLISHCODE

)

C ON A.COMPANYCODE=C.COMPANYCODE

AND A.SECURITYCODE=C.SECURITYCODE

LEFT JOIN

(

SELECT??EID TRAD_SK_HKDAILY_EID,

SECUCODE? ?? ?? ?? ?? ?,

TDATE? ?? ?? ?? ?? ?? ?,

TNUM? ?? ?? ?? ?? ?? ? ,

TAMT

FROM? ? TRAD_SK_HKDAILY

)

E

ON? ???B.SECURITYCODE=E.SECUCODE

AND B.TDATE? ?=E.TDATE

)

統計信息如下：

call? ???count? ?? ? cpu? ? elapsed? ?? ? disk? ?? ?query? ? current? ?? ???rows

------- ------??-------- ---------- ---------- ---------- ----------??----------

Parse? ?? ???1? ?? ?0.04? ?? ? 0.04? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0

Execute? ?? ?1? ?? ?0.00? ?? ? 0.00? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ? 0? ?? ?? ???0

Fetch? ?? ???1? ? 497.77? ???513.55? ?? ?22608? ? 3729688? ?? ?? ? 0? ?? ?? ???1

------- ------??-------- ---------- ---------- ---------- ----------??----------

total? ?? ???3? ? 497.82? ???513.60? ???22608? ? 3729688? ?? ? 0? ?? ?? ???1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 157

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)??Row Source Operation

---------- ---------- ----------??---------------------------------------------------

1? ?? ?? ? 1? ?? ?? ? 1??SORT AGGREGATE (cr=3729688 pr=22608 pw=0 time=513556021 us)

177954? ???177954? ???177954? ?VIEW??(cr=3729688 pr=22608 pw=0 time=509401174 us cost=4113 size=13 card=1)

177954? ???177954? ???177954? ? COUNT??(cr=3729688 pr=22608 pw=0 time=508990000 us)

177954? ???177954? ???177954? ???NESTED LOOPS OUTER (cr=3729688 pr=22608 pw=0 time=508677448 us cost=4113 size=189 card=1)

177954? ???177954? ???177954? ?? ?HASH JOIN OUTER (cr=24391 pr=1933 pw=0 time=2690219 us cost=4092 size=170 card=1)

90929? ?? ?90929? ?? ?90929? ?? ? NESTED LOOPS??(cr=4190 pr=1760 pw=0 time=1079324 us cost=637 size=128 card=1)

90929? ?? ?90929? ?? ?90929? ?? ???HASH JOIN??(cr=4187 pr=1760 pw=0 time=302237 us cost=637 size=112 card=1)

2302? ?? ? 2302? ?? ? 2302? ?? ?? ?TABLE ACCESS BY INDEX ROWID CDSY_SECUCODE (cr=811 pr=1 pw=0 time=28829 us cost=350 size=222998 card=2593)

2326? ?? ? 2326? ?? ? 2326? ?? ?? ? INDEX SKIP SCAN IDX_CDSY_SECUCODE_LS (cr=34 pr=1 pw=0 time=6623 us cost=74 size=0 card=2598)(object id 345164)

91164? ?? ?91164? ?? ?91164? ?? ?? ?TABLE ACCESS FULL HK_SHORTTD_LIST (cr=3376 pr=1759 pw=0 time=146284 us cost=287 size=2164968 card=83268)

90929? ?? ?90929? ?? ?90929? ?? ???INDEX RANGE SCAN SPTM_MARKETRELATION_MARKETCODE (cr=3 pr=0 pw=0 time=406676 us cost=0 size=16 card=1)(object id 96435)

3483? ?? ? 3483? ?? ? 3483? ?? ? VIEW??(cr=20201 pr=173 pw=0 time=92198 us cost=3455 size=136080 card=3240)

3483? ?? ? 3483? ?? ? 3483? ?? ???HASH JOIN RIGHT OUTER (cr=20201 pr=173 pw=0 time=86335 us cost=3455 size=139320 card=3240)

385? ?? ???385? ?? ???385? ?? ?? ?INDEX FAST FULL SCAN IDX_CUST_PUBLISHRELATION1 (cr=158 pr=119 pw=0 time=38012 us cost=12 size=4440 card=370)(object id 140038)

3483? ?? ? 3483? ?? ? 3483? ?? ?? ?TABLE ACCESS FULL CUST_PUBLISHSTOCKRELATION (cr=20043 pr=54 pw=0 time=23255 us cost=3443 size=100440 card=3240)

173601? ???173601? ???173601? ???INDEX RANGE SCAN IDX_TRAD_SK_HKDAILY_SMT (cr=3705297 pr=20675 pw=0 time=510094009 us cost=21 size=19 card=1)(object id 368255)

4、下面分析上面執行情況

首先看三種索引的統計信息：

SQL> select index_name,

2? ?? ?? ?blevel,

3? ?? ?? ?leaf_blocks,

4? ?? ?? ?distinct_keys,

5? ?? ?? ?clustering_factor,

6? ?? ?? ?num_rows

from dba_indexes t

7? ? 8? ?where t.index_name in

9? ?? ?? ?('IDX_TRAD_SK_HKDAILY_STM', 'IDX_TRAD_SK_HKDAILY_TSM', 'IDX_TRAD_SK_HKDAILY_SMT');

INDEX_NAME? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?BLEVEL? ?LEAF_BLOCKS??DISTINCT_KEYSCLUSTERING_FACTOR? ?NUM_ROWS

------------------------------? ?? ?? ?? ?? ?? ?? ?? ? ----------? ? -----------? ?? ?? ?-------------? ?? ?? ?? ? -----------------? ?? ?? ?? ?? ?? ???----------

IDX_TRAD_SK_HKDAILY_SMT? ?? ?? ?? ?? ???2? ?? ? 36579? ?? ?? ?? ?? ?5340489? ?? ?? ???4686724? ?? ?? ?? ?? ?? ?? ???5340489

IDX_TRAD_SK_HKDAILY_TSM? ?? ?? ?? ?? ???2? ?? ? 36488? ?? ?? ?? ?? ?5403645? ?? ?? ???3938252? ?? ?? ?? ?? ?? ?? ???5403645

IDX_TRAD_SK_HKDAILY_STM? ?? ?? ?? ?? ???2? ?? ? 36423? ?? ?? ?? ?? ?5304582? ?? ?? ???4654493? ?? ?? ?? ?? ?? ?? ???5304582

注意到blevel都是2,即每找一個數據，搜索的代價應該是一樣的，集簇因子也都差不多

根據大牛lewis書中cbo說的索引的代價如下：

cost =blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)

那計算結果應該上面三個索引相差不大

可是問題來了：

問題1：按理說走索引的速度應該是:

IDX_TRAD_SK_HKDAILY_TSM>IDX_TRAD_SK_HKDAILY_STM>IDX_TRAD_SK_HKDAILY_SMT(按列的區分度)，可實際情況是：

IDX_TRAD_SK_HKDAILY_STM>IDX_TRAD_SK_HKDAILY_TSM>IDX_TRAD_SK_HKDAILY_SMT ?

問題2:根據lewis公式的說法，三種代價應該差不多？

5、問題剖析

因為三種索引的葉塊是相同的，數量上沒有多大差別，存儲的索引建，也都基本相同(不考慮索引建的存儲順序)

下面主要看一下分支塊的區別：

首先查看索引段的id號，便于dump分支塊結構

SQL> select object_name,object_id from dba_objects where object_name in ('IDX_TRAD_SK_HKDAILY_STM',

2? ?? ?? ?? ?? ?? ?? ?? ???'IDX_TRAD_SK_HKDAILY_TSM','IDX_TRAD_SK_HKDAILY_SMT');

OBJECT_NAME? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???OBJECT_ID

-------------------------------------------------------------------------------------------------------------------------------- ----------

IDX_TRAD_SK_HKDAILY_STM? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???402666

IDX_TRAD_SK_HKDAILY_TSM? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???402680

IDX_TRAD_SK_HKDAILY_SMT? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???368255

dump??IDX_TRAD_SK_HKDAILY_STM??索引結構：

alter session set events 'immediate trace name treedump level 402666';

結構如下：

branch: 0x926fc84 153549956 (0: nrow: 105, level: 2)??--root分支塊

branch: 0x7f66c68 133590120 (-1: nrow: 350, level: 1)??---分支塊

leaf: 0x926fc85 153549957 (-1: nrow: 146 rrow: 146) ----葉子塊

下面查詢該分支塊的文件號和塊號：

SQL> select dbms_utility.data_block_address_file(153549956) "file",

2? ?? ?? ?dbms_utility.data_block_address_block(153549956) "block"

3? ? from dual;

file? ?? ?block

---------- ----------

36? ? 2555012

dump分支塊內容：

alter system dump datafile 36 block 2555012;

內容如下：

Branch block dump

=================

row#0[8035] dba: 150702413=0x8fb894d

col 0; len 10; (10):??00 30 00 30 00 30 00 30 00 36

col 1; len 4; (4):??78 71 03 07

col 2; TERM

row#1[8014] dba: 153550258=0x926fdb2

col 0; len 10; (10):??00 30 00 30 00 30 00 31 00 34

col 1; len 4; (4):??78 68 08 11

col 2; TERM

row#2[7995] dba: 28638616=0x1b4fd98

col 0; len 10; (10):??00 30 00 30 00 30 00 32 00 31

col 1; len 2; (2):??78 66

col 2; TERM

row#3[7974] dba: 150702715=0x8fb8a7b

col 0; len 10; (10):??00 30 00 30 00 30 00 32 00 38

col 1; len 4; (4):??78 69 0c 13

col 2; TERM

row#4[7953] dba: 153550560=0x926fee0

col 0; len 10; (10):??00 30 00 30 00 30 00 33 00 36

col 1; len 4; (4):??78 64 02 09

col 2; TERM

row#5[7932] dba: 28638917=0x1b4fec5

col 0; len 10; (10):??00 30 00 30 00 30 00 34 00 34

col 1; len 4; (4):??77 c5 07 1d

col 2; TERM

row#6[7911] dba: 133590698=0x7f66eaa

col 0; len 10; (10):??00 30 00 30 00 30 00 35 00 33

col 1; len 4; (4):??78 6d 04 10

col 2; TERM

下面同樣對其他兩個索引結構進行分析：

alter session set events 'immediate trace name treedump level 402680';

branch: 0x8fbb784 150714244 (0: nrow: 115, level: 2)

branch: 0x1b52bcc 28650444 (-1: nrow: 322, level: 1)

leaf: 0x8fbb785 150714245 (-1: nrow: 149 rrow: 149)

SQL> select dbms_utility.data_block_address_file(150714244) "file",

2? ?? ?? ?dbms_utility.data_block_address_block(150714244) "block"

3? ? from dual;

file? ?? ?block

---------- ----------

35? ? 3913604

alter system dump datafile 35 block 3913604;

Branch block dump

=================

row#0[8032] dba: 133705495=0x7f82f17

col 0; len 7; (7):??77 b6 0b 0f 01 01 01

col 1; len 10; (10):??00 30 00 30 00 30 00 30 00 35

col 2; TERM

row#1[8008] dba: 153562587=0x9272ddb

col 0; len 7; (7):??77 b9 0b 0e 01 01 01

col 1; len 10; (10):??00 30 00 30 00 30 00 30 00 35

col 2; TERM

row#2[7984] dba: 28650656=0x1b52ca0

col 0; len 7; (7):??77 bb 09 04 01 01 01

col 1; len 10; (10):??00 30 00 30 00 30 00 37 00 38

col 2; TERM

row#3[7960] dba: 150714722=0x8fbb962

col 0; len 7; (7):??77 bc 0c 09 01 01 01

col 1; len 10; (10):??00 30 00 30 00 31 00 34 00 37

col 2; TERM

row#4[7936] dba: 153562789=0x9272ea5

col 0; len 7; (7):??77 be 01 17 01 01 01

col 1; len 10; (10):??00 30 00 30 00 30 00 31 00 32

col 2; TERM

row#5[7912] dba: 133709671=0x7f83f67

col 0; len 7; (7):??77 bf 01 0f 01 01 01

col 1; len 10; (10):??00 30 00 30 00 31 00 34 00 35

col 2; TERM

row#6[7890] dba: 150714924=0x8fbba2c

col 0; len 7; (7):??77 bf 0b 13 01 01 01

col 1; len 8; (8):??00 30 00 30 00 30 00 31

col 2; TERM

row#7[7866] dba: 28651119=0x1b52e6f

col 0; len 7; (7):??77 c0 08 15 01 01 01

col 1; len 10; (10):??00 30 00 30 00 30 00 33 00 36

col 2; TERM

alter session set events 'immediate trace name treedump level 368255';

branch: 0x927bd84 153599364 (0: nrow: 191, level: 2)

branch: 0x7f92ac8 133769928 (-1: nrow: 192, level: 1)

leaf: 0x927bd85 153599365 (-1: nrow: 146 rrow: 146)

SQL>? ?? ? select dbms_utility.data_block_address_file(153599364) "file",

2? ?? ?? ?dbms_utility.data_block_address_block(153599364) "block"

3? ? from dual;

file? ?? ?block

---------- ----------

36? ? 2604420

alter system dump datafile 36 block 2604420;

row#0[8016] dba: 153599501=0x927be0d

col 0; len 10; (10):??00 30 00 30 00 30 00 30 00 34

col 1; len 18; (18):??00 30 00 36 00 39 00 30 00 30 00 32 00 30 00 30 00 34

col 2; len 4; (4):??77 bd 04 18

col 3; TERM

row#1[7976] dba: 133770064=0x7f92b50

col 0; len 10; (10):??00 30 00 30 00 30 00 30 00 38

col 1; len 18; (18):??00 30 00 36 00 39 00 30 00 30 00 32 00 30 00 30 00 34

col 2; len 4; (4):??78 65 09 0b

col 3; TERM

row#2[7936] dba: 153599637=0x927be95

col 0; len 10; (10):??00 30 00 30 00 30 00 31 00 32

col 1; len 18; (18):??00 30 00 36 00 39 00 30 00 30 00 32 00 30 00 30 00 34

col 2; len 4; (4):??77 c7 05 1a

col 3; TERM

row#3[7896] dba: 133770200=0x7f92bd8

col 0; len 10; (10):??00 30 00 30 00 30 00 31 00 36

col 1; len 18; (18):??00 30 00 36 00 39 00 30 00 30 00 32 00 30 00 30 00 34

col 2; len 4; (4):??77 c4 01 05

col 3; TERM

row#4[7857] dba: 153599773=0x927bf1d

col 0; len 10; (10):??00 30 00 30 00 30 00 31 00 39

col 1; len 18; (18):??00 30 00 36 00 39 00 30 00 30 00 32 00 30 00 30 00 34

col 2; len 3; (3):??78 70 09

col 3; TERM

row#5[7817] dba: 133770336=0x7f92c60

col 0; len 10; (10):??00 30 00 30 00 30 00 32 00 33

col 1; len 18; (18):??00 30 00 36 00 39 00 30 00 30 00 32 00 30 00 30 00 34

col 2; len 4; (4):??78 66 0a 0b

col 3; TERM

6、結論猜想

IDX_TRAD_SK_HKDAILY_STM 索引塊的分支塊由兩列構成，第一列長度為10，即SECUCODE列的全部，因為通過該列，

沒法判斷索引搜索路線，區分度不高，需要借助第二列加以區分，第二列長度不固定，只要索引能知道向左走，還是向右走，

就可以了，沒必要存儲第二列的全部，這樣每次訪問分支塊的代價，用分支塊中存儲列的長度來比作：10+4=14

IDX_TRAD_SK_HKDAILY_TSM 索引塊的分支塊由兩列構成，第一列長度為7，即TDATE列的全部，該列依舊區分度不高，

并且大部分需要借助第二列全部(SECUCODE列)，即長度為10，這樣代價就比第一高點，這樣每次訪問分支塊的代價，用分支塊中存儲列的長度來比作：10+7=17

IDX_TRAD_SK_HKDAILY_SMT 索引塊的分支塊由三列構成，第一列長度為10，即SECUCODE列的全部，因為通過該列，

沒法判斷索引搜索路線，需要借助第二列：MARKET，由于第二列的區分度為0，所以還需借助第三列TDATE的部分來進行區分，這樣代價就是：10+18+4=32

似乎從這個可以知道上面的原因：

INDEX RANGE SCAN IDX_TRAD_SK_HKDAILY_STM (cr=38998

INDEX RANGE SCAN IDX_TRAD_SK_HKDAILY_TSM (cr=353419

INDEX RANGE SCAN IDX_TRAD_SK_HKDAILY_SMT (cr=3705297

邏輯讀居然相差這么大，就是因為每次讀取分支快的大小不同。。。。。

從上面可以看到，并不是區分度越大，作為主導列，效果就越好，

當然了，如果只需要該列，就可以區分了，那當然是該列作為主導列

是比較好的，不然，還要看跟后面幾個列組合起來的區分度，

即相同的區分度，如果我組合所需要的字節比你少，即我的代價比你優

總結

以上是生活随笔為你收集整理的oracle区分度公式,区分度越大的列，作为主导列，索引效果越好？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。