CTAS is slower when where condition is included-CodePudding

There is table with 412499154 records, I have to recreate based on where condition. The table has 6 columns and all of varchar2() datatype.

when I recreate with the entire content, it takes roughly around 10 minutes.

CREATE TABLE TEMP_NEW_01 NOLOGGING
    AS
    SELECT COL1,COL2,COL3,COL4,COL5,COL6
    FROM  TEMP_DATA ;

When a where clause is included, its running indefinitely,

CREATE TABLE TEMP_NEW_01 NOLOGGING
    AS
    select  COL1,COL2,COL3,COL4,COL5,COL6
    FROM  TEMP_DATA
    WHERE COL1 IN
    (SELECT COL1 FROM temp_m2
        where  SHORT_CAPTION  in ( select  SHORT_CAPTION from t_category 
        where scat_caption  in ('P','V'))
    );

Any suggestions for improving? Thanks!

CodePudding user response：

Instead of using (nested) subqueries, join tables.

Make sure columns you use in joins are indexed; if not, then:

CREATE INDEX i1_dat_col1 ON temp_data (col1);
CREATE INDEX i1_m2_col1  ON temp_m2 (col1);
CREATE INDEX i2_m2_capt  ON temp_m2 (short_caption);
CREATE INDEX i1_cat_capt ON t_category (short_caption);

Gather statistics on all tables and indexes before running the CREATE TABLE statement!

Finally:

CREATE TABLE temp_new_01
AS
   SELECT d.col1,
          d.col2,
          d.col3,
          d.col4,
          d.col5,
          d.col6
     FROM temp_data d
          JOIN temp_m2 m ON m.col1 = d.col1
          JOIN t_category c ON c.short_caption = m.short_caption
    WHERE c.scat_caption IN ('P', 'V');