oracle append into,insert /*+ append */ into 与insert into 的区别
分别在这两种情况下执行插入，用tkprof生成统计信息文件，对比如下：insert into test select * from bc_log_1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Pa
分别在这两种情况下执行插入，用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均有所降低。
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献6条内容
所有评论(0)