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

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

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

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

| 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

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

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

| 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

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

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

| 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/，如需轉載，請註明出處，否則將追究法律責任。

Alter修改表結構對資料儲存的影響PP
2022-03-21
分割槽表的不同操作對索引的影響
2009-04-14
索引
關於InnoDB表資料和索引資料的儲存
2022-07-18
索引
【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響
2013-07-31
OracleMIT索引
shrink 操作對索引的影響
2010-06-25
索引
Update操作對索引的影響
2009-06-08
索引
操作分割槽表對global和local索引的影響
2015-03-01
索引
Oracle vs PostgreSQL，研發注意事項（4）- PageSize對資料儲存的影響
2018-07-28
OracleSQL
資料列not null對索引影響一例
2010-12-27
Null索引
表資料量影響MySQL索引選擇
2018-10-27
MySql索引
oracle分割槽表的常規操作導致對索引的影響
2017-11-09
Oracle索引
shrink 與rebuild對索引高度的影響對比
2012-08-07
Rebuild索引
表挪動儲存空間後，對之上的sql的執行計劃的影響的探究
2012-03-07
SQL
【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響
2013-08-02
Oracle索引
學習Oracle的索引、表的儲存
2008-07-20
Oracle索引
delete語句對索引的影響之分析
2006-04-24
delete索引
索引對直接路徑載入的影響
2011-12-30
索引
oracle點陣圖索引對DML操作的影響
2021-12-09
Oracle索引
stopkey對索引掃描的影響測試
2014-01-28
TopK索引
索引及排序對執行計劃的影響
2012-05-23
索引排序
CONTEXT索引對COMMIT操作的影響（ZT）
2009-06-03
Context索引MIT
影響儲存網路效能的因素有哪些？
2022-05-20
影響OLTP 系統效能的儲存因素解析
2009-07-15
SYSAUX表空間滿對資料庫的影響以及解決措施
2018-01-01
UX資料庫
驗證資料壓縮對DML的影響
2014-11-27
磁碟排序對Oracle資料庫效能的影響
2014-08-06
排序Oracle資料庫
有關Oracle表分割槽進行(DML)維護後對索引的影響的分析
2008-05-10
Oracle索引
Covid-19對英國藝術界的影響（附原資料表）
2020-11-04
MySQL null值儲存，null效能影響
2019-05-23
MySqlNull
pgsql資料庫的表儲存策略原理
2011-02-21
SQL資料庫
磁碟排序對Oracle資料庫效能的影響PT
2022-03-22
排序Oracle資料庫
容器化對資料庫的效能有影響嗎？
2024-02-02
資料庫
變更OS時間對資料庫的影響
2018-03-14
資料庫
大資料對法律行業產生的影響
2016-08-22
大資料行業
執行緒數目對資料庫的影響
2017-05-12
執行緒資料庫
iPad對各行業的影響–資料資訊圖
2013-06-05
iPad行業
【Qt】QVector對儲存資料的釋放
2018-01-29
QT
Cirium：資料揭示新冠肺炎對中國航空業的影響及對全球航空旅遊增長的影響
2020-02-19

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

相關文章