title: Create table as - takes really long time
body: <p>I'm creating a table using:</p>
  
  <pre><code>create table tbl_data_separated as
      with DTE as
      (
          select file_name, 
                 to_char(file_content) as file_content -- preconvert the clob to a varchar
          from tbl_data
      )
      SELECT file_name,
             file_content,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 1, NULL, 1) r_id,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 2, NULL, 1) p_id_,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 3, NULL, 1) batch_num,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 4, NULL, 1) service_id,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 5, NULL, 1) id_qualifier,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 6, NULL, 1) flag,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 7, NULL, 1) n_code,
              REGEXP_SUBSTR(file_content, '(.*?)(\||$)', 1, 8, NULL, 1) p_name
      FROM dte
  </code></pre>
  
  <p>In file_content, the pipe delimited has more than 150 such values which I need to compare using REGEX.</p>
  
  <p>The table - tbl_data has more than 400K rows. When I run the above query, the estimated time it shows is 36 hours.</p>
  
  <p>Is it possible to optimize it so it can be done faster? I'm thinking of creating a table and inserting rows. What would be the best way to speed it up?</p>