Skip to Main Content

SQL & PL/SQL

Create table as select (CTAS) takes forever, even its select query takes just 10 minutes

R4C_SuteeSDec 27 2019 — edited Jan 2 2020

I have the attached CTAS query which takes forever to run (the most I have waited was 24 hours+).
But when I try run just only the select query alone, it takes only 10 minutes to complete (I also try to export data to excel via SQL developer, it only take 10 minutes as well).
I also have tried use PARALLEL at CTAS only, at select query only and both (as below) bu still have the issue).

So, I'm not sure why CTAS takes forever to complete. Any idea?

CREATE TABLE XX_XXX_XXXXX01 PARALLEL NOLOGGING AS

  (SELECT

      /*+ PARALLEL */

      LEFT_CODE.SEQ,

      LEFT_CODE.C1   AS C1,

      LEFT_CODE.C2   AS C2,

      LEFT_CODE.C3   AS C3,

      LEFT_CODE.C4   AS C4,

      LEFT_CODE.C5   AS C5,

      LEFT_CODE.C6   AS C6,

      LEFT_CODE.C7   AS C7,

      LEFT_CODE.C8   AS C8,

      LEFT_CODE.C9   AS C9,

      LEFT_CODE.C10  AS C10,

      LEFT_CODE.C11  AS C11,

      LEFT_CODE.C12  AS C12,

      LEFT_CODE.C13  AS C13,

      LEFT_CODE.C14  AS C14,

      LEFT_CODE.C15  AS C15,

      LEFT_CODE.C16  AS C16,

      LEFT_CODE.C17  AS C17,

      LEFT_CODE.C18  AS C18,

      LEFT_CODE.C19  AS C19,

      LEFT_CODE.C20  AS C20,

      LEFT_CODE.C21  AS C21,

      RIGHT_CODE.C11 AS C22,

      RIGHT_CODE.C12 AS C23,

      RIGHT_CODE.C13 AS C24,

      RIGHT_CODE.C14 AS C25,

      RIGHT_CODE.C15 AS C26,

      RIGHT_CODE.C11 AS C27,

      RIGHT_CODE.C12 AS C28,

      RIGHT_CODE.C13 AS C29,

      RIGHT_CODE.C14 AS C30,

      RIGHT_CODE.C15 AS C31,

      RIGHT_CODE.C16 AS C32,

      RIGHT_CODE.C17 AS C33,

      RIGHT_CODE.C18 AS C34,

      RIGHT_CODE.C19 AS C35,

      RIGHT_CODE.C20 AS C36,

      RIGHT_CODE.C21 AS C37,

      RIGHT_CODE.C22 AS C38,

      RIGHT_CODE.C23 AS C39,

      RIGHT_CODE.C24 AS C40,

      RIGHT_CODE.C25 AS C41,

      RIGHT_CODE.C26 AS C42

    FROM

      (SELECT T1.*,

        (SELECT COL2

        FROM IMPORT_METADATA

        WHERE SNAPSHOT_ID=2

        AND TYPE         ='USER'

        AND COL1         =T1.XXX_LAST_UPDATED_BY

        ) AS LAST_UPDATED_BY_NAME,

        (SELECT COL2

        FROM IMPORT_METADATA

        WHERE SNAPSHOT_ID=2

        AND TYPE         ='USER'

        AND COL1         =T1.XXX_CREATED_BY

        ) AS CREATED_BY_NAME

      FROM

        (SELECT *

        FROM XX_XXX_T3351

        WHERE SNAPSHOT_ID              =2

        AND ( ( XXX_INV_ORG_ID        IS NOT NULL

        OR XXX_OU_ID                  IS NOT NULL

        OR XXX_LEDGER_ID              IS NOT NULL

        OR XXX_BG_ID                  IS NOT NULL )

        OR ( XXX_INV_ORG_ID           IS NULL

        AND XXX_OU_ID                 IS NULL

        AND XXX_LEDGER_ID             IS NULL

        AND XXX_BG_ID                 IS NULL ) )

        AND ( XXX_LAST_UPDATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129' )

        OR XXX_CREATED_BY NOT         IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129') )

        ) T1

      ORDER BY SEQ

      ) LEFT_CODE

    LEFT JOIN

      (SELECT T1.*,

        (SELECT COL2

        FROM IMPORT_METADATA

        WHERE SNAPSHOT_ID=2

        AND TYPE         ='USER'

        AND COL1         =T1.XXX_LAST_UPDATED_BY

        ) AS LAST_UPDATED_BY_NAME,

        (SELECT COL2

        FROM IMPORT_METADATA

        WHERE SNAPSHOT_ID=2

        AND TYPE         ='USER'

        AND COL1         =T1.XXX_CREATED_BY

        ) AS CREATED_BY_NAME

      FROM

        (SELECT *

        FROM XX_XXX_T3353

        WHERE SNAPSHOT_ID              =2

        AND ( ( XXX_INV_ORG_ID        IS NOT NULL

        OR XXX_OU_ID                  IS NOT NULL

        OR XXX_LEDGER_ID              IS NOT NULL

        OR XXX_BG_ID                  IS NOT NULL )

        OR ( XXX_INV_ORG_ID           IS NULL

        AND XXX_OU_ID                 IS NULL

        AND XXX_LEDGER_ID             IS NULL

        AND XXX_BG_ID                 IS NULL ) )

        AND ( XXX_LAST_UPDATED_BY NOT IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129' )

        OR XXX_CREATED_BY NOT         IN ('-1','0','1','2','3','4','5','6','7','120','121','122','123','124','125','126','127','128','129') )

        ) T1

      ORDER BY SEQ

      ) RIGHT_CODE

    ON 1                       =1

    AND NVL(LEFT_CODE.C8,'X')  = NVL(RIGHT_CODE.C8,'X')

    AND NVL(LEFT_CODE.C10,'X') = NVL(RIGHT_CODE.C10,'X')

    AND NVL(LEFT_CODE.C1,'X')  = NVL(RIGHT_CODE.C1,'X')

    AND NVL(LEFT_CODE.C1,'X')  = 'VALUE SET'

    AND NVL(LEFT_CODE.C8,'X') IS NOT NULL

  );

Comments
Post Details
Added on Dec 27 2019
16 comments
5,862 views