truncate 比 delete 慢的原因。

之前發過一個帖子，這次想總結一下。
SQL> delete  from ac95;

已刪除2行。

已用時間:  00: 00: 00.01

SQL> truncate table ac95;

表被截斷。

已用時間:  00: 01: 32.88


我們看到truncate 花費了93秒的時間。慢的出奇。
檢視建表語句：

------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "NCSI"."AC95"
   (     略。。。   CONSTRAINT "PK_AC95" PRIMARY KEY ("AAC001", "AAE140", "AAE002", "BAE415", "BAC468")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 973078528 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 3 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

---------------------------------------------------------------------------------------------------------------------------------------
發現索引段的第一個區非常大，將近一個G.

 

而且這個表上，還存在7個類似這樣的索引，索引的第一個區都比較大。

 

用10046跟蹤，tkprof以後結果如下：

truncate table ac95


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.15      82.94       2493         16       9069           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.15      82.95       2493         16       9069           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  reliable message                                8        0.00          0.00
  enq: RO - fast object reuse                     8        0.53          1.38
  db file sequential read                      2493        0.44         15.95
  local write wait                             2131        0.39         65.41
  rdbms ipc reply                                16        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
時間主要花費在了local write wait，db file sequential read上面。

 

由於tkprof以後隱藏了很多資訊，我們可以看看具體的local write wait，db file sequential read等待都發生在了那些資料上面。檢視後發現這些等待都發生在索引段，表段的第一個區上，ORACLE需要把這些段第一個區的資料塊讀入buffer進行格式化。

 

 

而delete為什麼比較塊呢，因為他只需要讀取高水位下使用過的塊即可。