Hello All;
I had a situation in which I needed to optimize CTAS statement like below:
CREATE TABLE CP_PROD_FCT
TABLESPACE TBS_DATA
PCTUSED 95
PCTFREE 5
INITRANS 1
MAXTRANS 255
PARTITION BY HASH (PROD_ID)
PARTITIONS 16
STORE IN (TBS_DATA)
NOCOMPRESS
NOCACHE
NOPARALLEL
NOLOGGING
MONITORING
AS
SELECT /*+ driving_site(n) parallel(n,15) */
n.categ_id AS categ_id,
TO_NUMBER (n.prod_id) AS prod_id,
n.sal_val AS sal_val,
n.valid_ind AS valid,
n.offcl_ind AS offcl_ind
FROM CDW_PROD.PROD_FCT@CDWP n
WHERE EXISTS
(SELECT 1
FROM PROD_LIST_PRC a
WHERE n.crtm_long = SUBSTR (a.crtm, 1, 10)
AND n.categ_id = a.categ_id)
Rows: ~10 mln
As you can see table is optimized for reading data
I've changed this CTAS by making the following changes:
- Adding parallel (degree 8) instead of noparallel option (parallelism is not managed automaticaly as per settings)
- Decreasing DOP in a query to 8 (15 is to much, and it's not power of 2 as it was recommended by Oracle)
- Removing driving site hint (it doesn't work with CTAS)
SQL:
CREATE TABLE CP_PROD_FCT
TABLESPACE TBS_DATA
PCTUSED 95
PCTFREE 5
INITRANS 1
MAXTRANS 255
PARTITION BY HASH (PROD_ID)
PARTITIONS 16
STORE IN (TBS_DATA)
NOCOMPRESS
NOCACHE
p
NOLOGGING
MONITORING
AS
SELECT /*+ parallel(n, 8) */
n.categ_id AS categ_id,
TO_NUMBER (n.prod_id) AS prod_id,
n.sal_val AS sal_val,
n.valid_ind AS valid,
n.offcl_ind AS offcl_ind
FROM CDW_PROD.PROD_FCT@CDWP n
WHERE EXISTS
(SELECT 1
FROM PROD_LIST_PRC a
WHERE n.crtm_long = SUBSTR (a.crtm, 1, 10)
AND n.categ_id = a.categ_id)
Results are fine but I am curious about problems regarding to unstable execution plans and remote execution in the future. Do you have any experience in this topic?
Regards,
Bolo