CTAS Performance II: TESTING

Following last week post on CTAS, a few comments were posted on LinkedIn stating that we could further improve the CTAS execution time by compressing the table. So I proceeded to run several tests using as baseline the CTAS execution without any COMPRESS option. To start with, I tested and comapred the following three CTAS options:

-- SQL1: Baseline

CREATE TABLE radu.observations_SYS_P13915_bck
    --
    PARALLEL 28
    NOLOGGING
    AS
        SELECT
            /*+parallel(radu.observations 28) */
            *
        FROM
        radu.observations partition(SYS_P13915)
;

-- SQL2: COMPRESS FOR OLTP
CREATE TABLE radu.observations_SYS_P13915_bck
    COMPRESS FOR OLTP
    PARALLEL 28
    NOLOGGING
    AS
        SELECT
            /*+parallel(radu.observations 28) */
            *
        FROM
            radu.observations  partition(SYS_P13915)
        ;            

-- SQL3: COMPRESS
CREATE TABLE radu.observations_SYS_P13915_bck
    COMPRESS
    PARALLEL 28
    NOLOGGING
    AS
        SELECT
            /*+parallel(radu.observations 28) */
            *
        FROM
            radu.observations  partition(SYS_P13915)
        ;    

The results of running these three variants of CTAS in exactly the same environment are:

TC#	SQL1: Baseline	SQL2: COMPRESS FOR OLTP	SQL3: COMPRESS
1	Elapsed: 00:09:58.457	Elapsed: 00:06:20.477	Elapsed: 00:05:37.490

Turns out that adding compression might be a good improvement for the CTAS operations!