delete語句對索引的影響之分析
所用測試表與上篇文章相同。
刪除第一個葉節點中的key值對應的記錄：
SQL> delete from test_idx
2 where object_id<500
3 /
已刪除8行。
SQL> conn system/oracle
已連線。
重新匯出第一個資料塊：
SQL> alter system dump datafile 4 block 229;
系統已更改。
開啟匯出檔案，可以發現被刪除的記錄對應的key值被標記了一個字元“D”，但資料並未被真正刪除
row#0[8023] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 03 3b
col 1; len 6; (6): 01 00 00 a4 00 00
row#1[8010] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 03 3c
col 1; len 6; (6): 01 00 00 a4 00 01
row#2[7997] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 0c
col 1; len 6; (6): 01 00 00 a4 00 02
row#3[7984] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 0e
col 1; len 6; (6): 01 00 00 a4 00 03
row#4[7971] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 0f
col 1; len 6; (6): 01 00 00 a4 00 04
row#5[7958] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 11
col 1; len 6; (6): 01 00 00 a4 00 05
row#6[7945] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 12
col 1; len 6; (6): 01 00 00 a4 00 06
row#7[7932] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 14
col 1; len 6; (6): 01 00 00 a4 00 07
row#8[7919] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 06
再向表中新增記錄，使其對應索引中的key值被加入第一個葉節點：
SQL> conn scott/tiger
已連線。
SQL> insert into test_idx
2 select * from all_objects
3 where object_id<500
4 /
已建立8行。
SQL> commit;
提交完成。
SQL> conn system/oracle
已連線。
SQL> alter system dump datafile 4 block 229;
系統已更改。
檢視新的匯出檔案內容，注意上面被標記為刪除的那些鍵值的變化：
row#0[1709] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 03 3b--資料與刪除前相同
col 1; len 6; (6): 01 00 00 df 00 00--rowid發生改變
row#1[1722] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 03 3c
col 1; len 6; (6): 01 00 00 df 00 01
row#2[1735] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 0c
col 1; len 6; (6): 01 00 00 df 00 02
row#3[1748] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 0e
col 1; len 6; (6): 01 00 00 df 00 03
row#4[1761] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 0f
col 1; len 6; (6): 01 00 00 df 00 04
row#5[1774] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 11
col 1; len 6; (6): 01 00 00 df 00 05
row#6[1787] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 12
col 1; len 6; (6): 01 00 00 df 00 06
row#7[1800] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 14
col 1; len 6; (6): 01 00 00 df 00 07
row#8[7919] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 07 06--原來未被刪除的資料
col 1; len 6; (6): 01 00 00 a4 00 08--rowid未發生改變
我們發現，刪除標記沒有了，雖然鍵值還是原來的(因為我們新增的記錄與剛才刪除的相同)，但是rowid已經發生改變(側面說明表中新新增的記錄不是嚴格重用其對應的刪除記錄所佔用的空間)，而原來未被刪除的鍵值的rowid沒有發生變化。
如果新新增的資料不和原來刪除的重複呢？
我們繼續實驗：
SQL> conn scott/tiger
已連線。
SQL> delete from test_idx
2 where object_id<800
3 /
已刪除9行。
SQL> commit;
提交完成。
SQL> conn system/oracle
已連線。
SQL> alter system dump datafile 4 block 229;
系統已更改。
檢視匯出檔案內容：
row#0[1709] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 03 3b
col 1; len 6; (6): 01 00 00 df 00 00
row#1[1722] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 03 3c
col 1; len 6; (6): 01 00 00 df 00 01
row#2[1735] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 0c
col 1; len 6; (6): 01 00 00 df 00 02
row#3[1748] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 0e
col 1; len 6; (6): 01 00 00 df 00 03
row#4[1761] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 0f
col 1; len 6; (6): 01 00 00 df 00 04
row#5[1774] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 11
col 1; len 6; (6): 01 00 00 df 00 05
row#6[1787] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 12
col 1; len 6; (6): 01 00 00 df 00 06
row#7[1800] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 04 14
col 1; len 6; (6): 01 00 00 df 00 07
row#8[7919] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 07 06
col 1; len 6; (6): 01 00 00 a4 00 08
row#9[7906] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 09 57
col 1; len 6; (6): 01 00 00 a4 00 09
row#10[7893] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 09 58
col 1; len 6; (6): 01 00 00 a4 00 0a
……
row#476[1839] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 12 2d
col 1; len 6; (6): 01 00 00 a9 00 4e
row#477[1826] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 12 2e
col 1; len 6; (6): 01 00 00 a9 00 4f
row#478[1813] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 12 2f
col 1; len 6; (6): 01 00 00 aa 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 229 maxblk 229
我們再新增記錄，然後匯出資料塊：
SQL> alter table test_idx modify created null;
表已更改。
SQL> alter table test_idx modify last_ddl_time null;
表已更改。
SQL> insert into test_idx(owner,object_id,object_name)
2 values('LAW',345,'HELLO')
3 /
已建立 1 行。
SQL> commit;
提交完成。
SQL> conn system/oracle
已連線。
SQL> alter system dump datafile 4 block 229;
系統已更改。
檢視匯出檔案內容：
row#0[1696] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 04 2e--塊中的第一個key值發生改變，繼續檢視可以發現，塊中的所有鍵值經過了重新排列
col 1; len 6; (6): 01 00 00 df 00 08
row#1[7906] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 09 57
col 1; len 6; (6): 01 00 00 a4 00 09
row#2[7893] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 09 58
col 1; len 6; (6): 01 00 00 a4 00 0a
row#3[7880] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 09 5a
col 1; len 6; (6): 01 00 00 a4 00 0b
row#4[7867] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 09 5b
col 1; len 6; (6): 01 00 00 a4 00 0c
row#5[7854] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 02
col 1; len 6; (6): 01 00 00 a4 00 0d
row#6[7841] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 04
col 1; len 6; (6): 01 00 00 a4 00 0e
row#7[7828] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 06
col 1; len 6; (6): 01 00 00 a4 00 0f
row#8[7815] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 08
col 1; len 6; (6): 01 00 00 a4 00 10
row#9[7802] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 0a
col 1; len 6; (6): 01 00 00 a4 00 11
row#10[7789] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 0c
col 1; len 6; (6): 01 00 00 a4 00 12
row#11[7776] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 0a 0e
col 1; len 6; (6): 01 00 00 a4 00 13
……
row#467[1852] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 12 2b
col 1; len 6; (6): 01 00 00 a9 00 4d
row#468[1839] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 12 2d
col 1; len 6; (6): 01 00 00 a9 00 4e
row#469[1826] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 12 2e
col 1; len 6; (6): 01 00 00 a9 00 4f
row#470[1813] flag: ------, lock: 0, len=13--row＃比原來減少8
col 0; len 3; (3): c2 12 2f
col 1; len 6; (6): 01 00 00 aa 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 229 maxblk 229
整個資料塊中，標記刪除的已經都被清空，現在第一個葉節點只剩下471個鍵值(原來最後一個row#為478)，鍵值也經過了重新排序。
結論：
- 表中記錄被刪除後，索引中對應的鍵值不會被刪除(即所佔用空間不會釋放)，而只是加上刪除標記。
- 如果以後有新的鍵值(一個或多個)加進這個資料塊，則塊中標記刪除的鍵值都會清除。注意索引中鍵值的刪除與事務提交沒有關係。
- update語句相當於先delete再insert，實驗步驟與結果與上面相同。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/37724/viewspace-152534/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- shrink 與rebuild對索引高度的影響對比Rebuild索引
- 【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響Oracle索引
- 索引對直接路徑載入的影響索引
- 語言對思維的影響
- 表資料的儲存對索引的影響索引
- 分割槽表的不同操作對索引的影響索引
- 測試truncate，delete 對rman 備份集大小的影響delete
- MySQL中join語句的基本使用教程及其欄位對效能的影響MySql
- oracle點陣圖索引對DML操作的影響Oracle索引
- stopkey對索引掃描的影響測試TopK索引
- 索引及排序對執行計劃的影響索引排序
- CONTEXT索引對COMMIT操作的影響 （ZT）Context索引MIT
- 操作分割槽表對global和local索引的影響索引
- myisam對於update，insert,delete關於auto_incremant的影響deleteREM
- innodb對於update，insert,delete關於auto_incremant的影響deleteREM
- Swift語法對編譯速度的影響Swift編譯
- 有關Oracle表分割槽進行(DML)維護後對索引的影響的分析Oracle索引
- 對我影響最大的圖靈書之嗨翻C語言圖靈C語言
- 資料列not null對索引影響一例Null索引
- 【分析方法論】屬性對行為的影響分析
- 【SQL】11 SQL DELETE 語句SQLdelete
- Sql Server系列：Delete語句SQLServerdelete
- FORALL執行DELETE語句delete
- LINQ to SQL語句之Insert/Update/Delete操作SQLdelete
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- oracle本地分割槽索引跨分割槽對成本的影響Oracle索引
- 對列進行連線操作會影響索引的使用索引
- [非常急] js 語句無法對下拉選擇框產生影響的問題JS
- 檢視insert,delete,update對基表的影響（檢視初識）delete
- 修改主機時區對Oracle的影響分析Oracle
- 複合索引中前導列對sql查詢的影響索引SQL
- KunlunDB功能之insert/update/delete...returning語句delete
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- PL/sql中怎樣得到insert，update，delete語句執行後，受影響的紀錄行數關鍵字SQLdelete
- Sql Server之旅——第十站 看看DML操作對索引的影響SQLServer索引