Search code examples
sqloracle-databasehint

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?

Solution

  • CREATE TABLE AS SELECT uses append automatically. It's because the other sessions will not see the table until it 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).