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!