insert /*+ append */ into 與insert into 的區別

轉自：http://space.itpub.net/10972173/viewspace-464459

分別在這兩種情況下執行插入，用tkprof生成統計資訊檔案，對比如下：

insert into test select * from bc_log_1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 5.88 30.35 20935 39792 85865 564546
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.88 30.37 20935 39792 85865 564546
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26

Rows Row Source Operation
------- ---------------------------------------------------
564546 TABLE ACCESS FULL BC_LOG_1 (cr=22885 r=20870 w=0 time=5441181 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 1333 0.15 3.11
log buffer space 149 0.36 7.92
log file switch completion 18 0.99 2.14
free buffer waits 12 0.93 6.31
latch free 1 0.00 0.00
db file sequential read 65 0.04 0.22
log file switch (checkpoint incomplete) 8 0.99 3.13
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 11.30 11.30

[@more@]

insert /*+ append */ into bc_log select * from bc_log_1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.22 0 1 0 0
Execute 1 4.88 23.11 22879 27806 1109 564546
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.89 23.34 22879 27807 1109 564546

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=27806 r=22879 w=14883 time=23097417 us)
564546 TABLE ACCESS FULL BC_LOG_1 (cr=27209 r=22878 w=0 time=15937799 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 3 0.00 0.00
db file sequential read 27 0.01 0.04
db file scattered read 1440 1.07 11.45
free buffer waits 23 0.99 3.11
latch free 1 0.01 0.01
direct path write 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 16.89 16.89

可以看出，使用/*+ append */後，一致讀和當前讀的數量均低於不使用hints，尤其是當前讀的數量大大降低，cpu time和elapsed time均有所降低。