NemanjaT NemanjaT - 8 months ago 62
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


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