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: BaselineSQL2: COMPRESS FOR OLTPSQL3: COMPRESS
1Elapsed: 00:09:58.457Elapsed: 00:06:20.477Elapsed: 00:05:37.490
Test results

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

Leave a Reply

%d bloggers like this: