update表中index索引列對原索引條目做什麼操作?

guocun09發表於2020-04-17

update表中index索引列對原索引條目做什麼操作?

--將原索引條目刪除,然後再插入新值條目。


實驗驗證:

方法:透過修改index列值後,檢視index碎片的變化

--建立table測試
CREATE TABLE MES1.T_OBJECT
AS
SELECT * FROM DBA_OBJECTS
WHERE ROWNUM<=20000;
--當前20000筆資料
SELECT COUNT(1) FROM MES1.T_OBJECT;
20000
--建立index
CREATE INDEX MES1.IDX_OWNER ON  MES1.T_OBJECT(OWNER);
--當前index使用大小
SELECT SEGMENT_NAME,BYTES,BLOCKS FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='IDX_OWNER'
SEGMENT_NAME    BYTES    BLOCKS
IDX_OWNER    458752    56


update index列前索引的使用狀況

--使用ANALYZE INDEX ... VALIDATE STRUCTURE可分析index具體使用情況和是否有壞塊,
注意執行不使用online話會產生鎖,阻塞DML
analyze index  MES1.IDX_OWNER validate structure;
--分析結果可透過index_stats檢視
select NAME,BLOCKS,DEL_LF_ROWS,LF_ROWS,DEL_LF_ROWS/LF_ROWS from index_stats;
NAME    BLOCKS    DEL_LF_ROWS    LF_ROWS    DEL_LF_ROWS/LF_ROWS
IDX_OWNER    56    0    20000    0
--BLOCKS: Blocks allocated to the segment,index segment使用56個block
--DEL_LF_ROWS: Number of deleted leaf rows in the index,索引中被刪除的葉子行數,當前沒有刪除所以是0
--LF_ROWS: Number of leaf rows,葉子總行數,為建表時的20000
--DEL_LF_ROWS/LF_ROWS:index中被刪除行佔總行數的百分比,
  即碎片數(如果用del_lf_rows_len/lf_rows_len更準確),這裡沒有刪除行所以為0


update index列

--修改10000筆索引列值
UPDATE  MES1.T_OBJECT SET OWNER='TESTUIDX'
WHERE ROWNUM<=10000;
commit;


update index列後索引的使用狀況

--再次分析index使用狀況
analyze index  MES1.IDX_OWNER validate structure;
select NAME,BLOCKS,DEL_LF_ROWS,LF_ROWS,DEL_LF_ROWS/LF_ROWS from index_stats;
select * from index_stats
NAME    BLOCKS    DEL_LF_ROWS    LF_ROWS    DEL_LF_ROWS/LF_ROWS
IDX_OWNER    112    10000    30000    0.333333333333333
--BLOCKS: 增加到了112
--DEL_LF_ROWS: update後索引中被刪除的葉子行數變為10000,說明update會將原來索引條目行刪除
--LF_ROWS: 葉子總行數變為30000,
--進一步證實是刪除了10000行,但index中之前空間並沒有釋放,再加上update後新插入的10000,也就是30000了
--DEL_LF_ROWS/LF_ROWS:因為update被刪除的行產生的碎片為10000/30000,正好為0.333333333333333


以上是針對update時的情況,delete時實際是一樣機制,這裡不做驗證。


結論:

在update更新index索引列操作時是 將原索引條目刪除,再插入新值條目。

所以在有頻繁對table索引列update和delete  table操作的,可以使用 ANALYZE INDEX ... VALIDATE STRUCTURE定期分析index, 並執行alter index... rebuild,shrink,coalesce之類整理索引碎片

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

相關文章