Comments for APPEND_VALUES Hint in Oracle Database 11g Release 2
lesio said...APPEND should work like APPEND_VALUES !
Oracle is crazy, another thing to remember.
APPEND is only supported in "INSERT INTO ... SELECT" statments. If you use in an "INSERT INTO ... VALUES" statement it is ignored.
I agree, it would have been nice if they had just used the same hint text in the two statements. :)
Ted Chyn said...I ran the query on a 2 nodes cluster under Centos and got exactly the opposite result.can someone explain this.
Bulk Inserts : 678
Bulk Inserts /*+ APPEND_VALUES */ : 936
PL/SQL procedure successfully completed.
No idea. I guess you need to trace it to see what the extra time is taken up doing. Use SQL Trace with waits switched on.
DarkAngelStrike said...URLs to the Oracle Documentation has changed :
About Direct-Path INSERT >> download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables004.htm#i1009864
APPEND_VALUES Hint >> download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF51109
Thanks for the heads-up. It seems Oracle change the URLs every time they release a new patchset these days. :)
I've corrected the links in the article now.
Vijay said...Thanks for the simple examples you've shown to understand the benefits of the new APPEND_VALUES hint.
MarkS said...On my system, your example runs marginally slower with APPEND_VALUES:
Bulk Inserts : 137
Bulk Inserts /*+ APPEND_VALUES */ : 143
Without the DELETE and setting ID + CODE to "l_size + i*2" the difference is significant:
Bulk Inserts : 291
Bulk Inserts /*+ APPEND_VALUES */ : 140
As with most tests, mileage will vary depending on the kit. I've just run this again on my desktop (which is pretty good), and a rather bad Dev VM and the results come out amazingly similar. :)
Bulk Inserts : 144
Bulk Inserts /*+ APPEND_VALUES */ : 131
Bulk Inserts : 144
Bulk Inserts /*+ APPEND_VALUES */ : 129
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.