表資料的儲存對索引的影響

newknight發表於2014-01-14
索引

表格資料的儲存對索引的影響

 

近期碰到一張包含日期欄位的表格，使用者反應即使在日期欄位上建立了索引，查詢報表時涉及的SQL仍然選擇走全表掃描，而忽略索引。

 

對應表名為：T_GATE_CTN_FT

查詢SQL如下：

select sum(T5253.TEU) as c1,

T2881.OWC_WEEK_DESC as c2,

T2881.OWC_WEEK_NUMBER as c3,

T2881.OWC_YEAR_NO as c4

from T_CTN_DM T2867, T_DATE_DM T2881, T_GATE_CTN_FT T5253

where (T2867.CTN_STATUS = 'F' and T2867.CTN_TYPE_UID = T5253.CTN_TYPE_UID and

T2881.DATE_UID = T5253.DATE_UID and T5253.MOVE_KIND = 'RECV' and

(T2881.OWC_YEAR_NO + 1 in (2012) or T2881.OWC_YEAR_NO in (2012)))

group by T2881.OWC_WEEK_NUMBER, T2881.OWC_YEAR_NO, T2881.OWC_WEEK_DESC

order by c2, c3

;

注：2012由前臺引數傳入，語句不建議修改

 

執行計劃如下：

PLAN_TABLE_OUTPUT

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

Plan hash value: 4280972504

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

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

| 0 | SELECT STATEMENT | | 436K| 37M| 29487 (2)| 00:0

| 1 | SORT GROUP BY | | 436K| 37M| 29487 (2)| 00:0

|* 2 | HASH JOIN | | 436K| 37M| 29460 (2)| 00:0

|* 3 | TABLE ACCESS FULL | T_CTN_DM | 5549 | 94333 | 103 (1)| 00:0

|* 4 | HASH JOIN | | 436K| 30M| 29354 (2)| 00:0

|* 5 | TABLE ACCESS FULL| T_DATE_DM | 359 | 16514 | 42 (0)| 00:0

|* 6 | TABLE ACCESS FULL| T_GATE_CTN_FT | 4783K| 127M| 29277 (1)| 00:0

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

Predicate Information (identified by operation id):

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

2 - access("T2867"."CTN_TYPE_UID"="T5253"."CTN_TYPE_UID")

3 - filter("T2867"."CTN_STATUS"='F')

4 - access("T2881"."DATE_UID"="T5253"."DATE_UID")

5 - filter("T2881"."OWC_YEAR_NO"=2012 OR "T2881"."OWC_YEAR_NO"+1=2012)

6 - filter("T5253"."MOVE_KIND"='RECV')

 

執行時間：

SQL>

ALTER SYSTEM FLUSH BUFFER_CACHE;

 

System altered

 

Executed in 0.156 seconds

ALTER SYSTEM FLUSH SHARED_POOL;

 

System altered

 

Executed in 0.468 seconds

select sum(T5253.TEU) as c1,

T2881.OWC_WEEK_DESC as c2,

T2881.OWC_WEEK_NUMBER as c3,

T2881.OWC_YEAR_NO as c4

from T_CTN_DM T2867, T_DATE_DM T2881, T_GATE_CTN_FT T5253

where (T2867.CTN_STATUS = 'F' and T2867.CTN_TYPE_UID = T5253.CTN_TYPE_UID and

T2881.DATE_UID = T5253.DATE_UID and T5253.MOVE_KIND = 'RECV' and

(T2881.OWC_YEAR_NO + 1 in (2012) or T2881.OWC_YEAR_NO in (2012)))

group by T2881.OWC_WEEK_NUMBER, T2881.OWC_YEAR_NO, T2881.OWC_WEEK_DESC

order by c2, c3

;

Result:

……

……

105 rows selected

 

Executed in 18.299 seconds

 

檢查表格和對應索引的統計資訊：

select * from user_tables where table_name='T_GATE_CTN_FT';

select * from user_indexes where table_name='T_GATE_CTN_FT' and index_name='I_GATE_CTN_DATE_UID';

注意上圖聚集因子達到了536655.

 

select * from user_ind_columns where index_name='I_GATE_CTN_DATE_UID';

 

如果強制使用索引：

語句改為：

select /*+index(T5253 I_GATE_CTN_DATE_UID)*/sum(T5253.TEU) as c1,

T2881.OWC_WEEK_DESC as c2,

T2881.OWC_WEEK_NUMBER as c3,

T2881.OWC_YEAR_NO as c4

from T_CTN_DM T2867, T_DATE_DM T2881, T_GATE_CTN_FT T5253

where (T2867.CTN_STATUS = 'F' and T2867.CTN_TYPE_UID = T5253.CTN_TYPE_UID and

T2881.DATE_UID = T5253.DATE_UID and T5253.MOVE_KIND = 'RECV' and

(T2881.OWC_YEAR_NO + 1 in (2012) or T2881.OWC_YEAR_NO in (2012)))

group by T2881.OWC_WEEK_NUMBER, T2881.OWC_YEAR_NO, T2881.OWC_WEEK_DESC

order by c2, c3

;

 

執行計劃：

PLAN_TABLE_OUTPUT

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

Plan hash value: 1969617402

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

| Id | Operation | Name | Rows | Bytes | C

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

| 0 | SELECT STATEMENT | | 436K| 37M| 5

| 1 | SORT GROUP BY | | 436K| 37M| 5

|* 2 | HASH JOIN | | 436K| 37M| 5

|* 3 | TABLE ACCESS FULL | T_CTN_DM | 5549 | 94333 |

| 4 | NESTED LOOPS | | | |

| 5 | NESTED LOOPS | | 436K| 30M| 5

|* 6 | TABLE ACCESS FULL | T_DATE_DM | 359 | 16514 |

|* 7 | INDEX RANGE SCAN | I_GATE_CTN_DATE_UID | 1894 | |

|* 8 | TABLE ACCESS BY INDEX ROWID| T_GATE_CTN_FT | 1214 | 33992 |

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

Predicate Information (identified by operation id):

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

2 - access("T2867"."CTN_TYPE_UID"="T5253"."CTN_TYPE_UID")

 

PLAN_TABLE_OUTPUT

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

3 - filter("T2867"."CTN_STATUS"='F')

6 - filter("T2881"."OWC_YEAR_NO"=2012 OR "T2881"."OWC_YEAR_NO"+1=2012)

7 - access("T2881"."DATE_UID"="T5253"."DATE_UID")

8 - filter("T5253"."MOVE_KIND"='RECV')

 

執行時間：

SQL>

ALTER SYSTEM FLUSH BUFFER_CACHE;

 

System altered

 

Executed in 0.125 seconds

ALTER SYSTEM FLUSH SHARED_POOL;

 

System altered

 

Executed in 0.686 seconds

select /*+index(T5253 I_GATE_CTN_DATE_UID)*/sum(T5253.TEU) as c1,

T2881.OWC_WEEK_DESC as c2,

T2881.OWC_WEEK_NUMBER as c3,

T2881.OWC_YEAR_NO as c4

from T_CTN_DM T2867, T_DATE_DM T2881, T_GATE_CTN_FT T5253

where (T2867.CTN_STATUS = 'F' and T2867.CTN_TYPE_UID = T5253.CTN_TYPE_UID and

T2881.DATE_UID = T5253.DATE_UID and T5253.MOVE_KIND = 'RECV' and

(T2881.OWC_YEAR_NO + 1 in (2012) or T2881.OWC_YEAR_NO in (2012)))

group by T2881.OWC_WEEK_NUMBER, T2881.OWC_YEAR_NO, T2881.OWC_WEEK_DESC

order by c2, c3

;

Result:

……

……

105 rows selected

 

Executed in 34.913 seconds

 

可見由於對於date_uid欄位來說，資料儲存到表格裡的時候是無序的，導致索引的聚集因子過大，如果在查詢中使用date_uid的索引，反而降低了查詢的效率。

 

 

改進測試：

對於date_uid欄位，因為是自增長型別，之後新填入的資料幾乎都是順序增長的。

那麼建議在倉庫比較空閒時重建此表格，按date_uid順序重新插入資料，測試一下索引的可用情況。

 

建立測試表格：

CREATE TABLE T_GATE_CTN_FT2 AS SELECT * FROM T_GATE_CTN_FT ORDER BY DATE_UID;

 

CREATE INDEX I_GATE_CTN_FT2_DATE_UID ON T_GATE_CTN_FT2(DATE_UID);

檢查統計資訊：

SELECT * FROM USER_TABLES WHERE TABLE_NAME='T_GATE_CTN_FT2';

 

SELECT * FROM USER_INDEXES WHERE TABLE_NAME='T_GATE_CTN_FT2';

注意聚集因子為106590.

 

替換表格，檢查執行計劃：

select sum(T5253.TEU) as c1,

T2881.OWC_WEEK_DESC as c2,

T2881.OWC_WEEK_NUMBER as c3,

T2881.OWC_YEAR_NO as c4

from T_CTN_DM T2867, T_DATE_DM T2881, T_GATE_CTN_FT2 T5253

where (T2867.CTN_STATUS = 'F' and T2867.CTN_TYPE_UID = T5253.CTN_TYPE_UID and

T2881.DATE_UID = T5253.DATE_UID and T5253.MOVE_KIND = 'RECV' and

(T2881.OWC_YEAR_NO + 1 in (2012) or T2881.OWC_YEAR_NO in (2012)))

group by T2881.OWC_WEEK_NUMBER, T2881.OWC_YEAR_NO, T2881.OWC_WEEK_DESC

order by c2, c3

;

 

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 746580745

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

| Id | Operation | Name | Rows | Bytes

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

| 0 | SELECT STATEMENT | | 325K| 28

| 1 | SORT GROUP BY | | 325K| 28

|* 2 | HASH JOIN | | 325K| 28

|* 3 | TABLE ACCESS FULL | T_CTN_DM | 5549 | 94333

| 4 | NESTED LOOPS | | |

| 5 | NESTED LOOPS | | 325K| 22

|* 6 | TABLE ACCESS FULL | T_DATE_DM | 359 | 16514

|* 7 | INDEX RANGE SCAN | I_GATE_CTN_FT2_DATE_UID | 1827 |

|* 8 | TABLE ACCESS BY INDEX ROWID| T_GATE_CTN_FT2 | 907 | 25396

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

Predicate Information (identified by operation id):

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

2 - access("T2867"."CTN_TYPE_UID"="T5253"."CTN_TYPE_UID")

 

PLAN_TABLE_OUTPUT

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

3 - filter("T2867"."CTN_STATUS"='F')

6 - filter("T2881"."OWC_YEAR_NO"=2012 OR "T2881"."OWC_YEAR_NO"+1=2012)

7 - access("T2881"."DATE_UID"="T5253"."DATE_UID")

8 - filter("T5253"."MOVE_KIND"='RECV')

 

執行情況：

SQL>

ALTER SYSTEM FLUSH BUFFER_CACHE;

 

System altered

 

Executed in 0.156 seconds

ALTER SYSTEM FLUSH SHARED_POOL;

 

System altered

 

Executed in 0.468 seconds

select sum(T5253.TEU) as c1,

T2881.OWC_WEEK_DESC as c2,

T2881.OWC_WEEK_NUMBER as c3,

T2881.OWC_YEAR_NO as c4

from T_CTN_DM T2867, T_DATE_DM T2881, T_GATE_CTN_FT2 T5253

where (T2867.CTN_STATUS = 'F' and T2867.CTN_TYPE_UID = T5253.CTN_TYPE_UID and

T2881.DATE_UID = T5253.DATE_UID and T5253.MOVE_KIND = 'RECV' and

(T2881.OWC_YEAR_NO + 1 in (2012) or T2881.OWC_YEAR_NO in (2012)))

group by T2881.OWC_WEEK_NUMBER, T2881.OWC_YEAR_NO, T2881.OWC_WEEK_DESC

order by c2, c3

;

Result:

……

……

105 rows selected

 

Executed in 8.391 seconds

 

可見當表格資料按照date_uid順序存入時，索引的聚集因子比較低，相應的索引的可用性比較好，oracle的CBO會自動選擇索引掃描。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10009036/viewspace-1070401/，如需轉載，請註明出處，否則將追究法律責任。

相關文章