oracle append into,insert /*+ append */ into 与insert into 的区别

分别在这两种情况下执行插入，用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均有所降低。