NemanjaT NemanjaT - 4 months ago 28
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
APPEND
hint. Is it possible to add it to
CREATE TABLE AS
? or is it an insert only hint? How could I change this to apply the
append
hint?

Answer

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