0

I have following code in script:

execute immediate 'truncate table tab_a drop storage';

insert /*+APPEND*/ into tab_a
select (...)

I am wondering if this APPEND hint can cause any performance change, since table is truncated and storage dropped.
Any info, hints or resources are welcome.

  • 1
    Instead of truncate + insert append; table drop + CTAS + recreate permissions and indexes will be considerably faster with fewer performance issues. This approach is the preferred method from Tom Kyte for rebuilding large tables (asktom.oracle.com/pls/asktom/…). Of course, this may not work for you due to potential "table doesn't exist" errors during CTAS, but the same is somewhat true for truncate (to a lesser degree) with missing data during rebuild process since truncate is DDL – Kris Johnston Jan 12 '17 at 14:08
2

insert /*+APPEND*/ bypases buffer caches and writes directly into the table segment. It also means there should not be any triggers on the table nor unique constraints. And other conditions must be met, otherwise the hint is silently ignored. Check query exec plan. If you see LOAD AS SELECT the direct-path write is used. If you see LOAD TABLE CONVENTIONAL then APPEND hint is ignored.

See this SO answer about constraints:

https://stackoverflow.com/a/10239578/836215

2

Without knowing far more about what you are doing its impossible to say. However, I can say what APPEND, essentially, does. It will do a direct-path insert to the table which simply means it will write the data directly to data blocks and will bypass the buffer cache. So generally you will use the hint when loading a large dataset and definitely not for just a handful of records. There are some things to consider such as serialisation, space re-use, etc. For a very quick list take a look at AskTom https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484 .

It is another option you can consider using depending on the work you are doing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.