NemanjaT NemanjaT - 1 year ago 174
SQL Question

Append hint and create table as

currently we're loading a big amount of data via the following (simplified) sql:

create table temp_table as (
select /*+parallel(2) */ col_a, col_b, col_c
from tab_a

I want to increase the performance even more by adding the
hint. Is it possible to add it to
? or is it an insert only hint? How could I change this to apply the

Answer Source

CREATE TABLE AS SELECT uses append automatically. It's because the other sessions will not see the table until has been loaded so the rows can be stored directly to data file, bypassing the buffer cache.

You can simply check that using EXPLAIN PLAN on CREATE TABLE AS SELECT. If you see LOAD AS SELECT, it means direct path (APPEND). If there is LOAD TABLE CONVENTIONAL it isn't direct path (NOAPPEND).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download