8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

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.

Tim... said...

Hi.

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. :)

Cheers

Tim...

Ted Chyn said...

I ran the query on a 2 nodes cluster under Centos and got exactly the opposite result.can someone explain this.

see below
================
Bulk Inserts : 678
Bulk Inserts /*+ APPEND_VALUES */ : 936

PL/SQL procedure successfully completed.

Tim... said...

Hi.

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.

Cheers

Tim...

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
:]

Tim... said...

Hi.

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.

Cheers

Tim...

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

Tim... said...

Hi.

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

Cheers

Tim...

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.