Home -> Mailing Lists -> Oracle-L -> Re: CTAS running long

Re: CTAS running long

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 7 May 2022 16:24:00 -0400
Message-ID: <8c68a9d9-84c2-b9e0-f88d-a3d4d0db0f57_at_gmail.com> 





  
    
  
  
    
On 5/7/22 13:37, Lok P wrote:

    

    

      
      

        
Resending as bounced back...


        
Hi , We are trying to purge data from a non partition table, as part of which we are creating an interim table which would be partitioned and that will hold the required data and then we will exchange that partition with the main table to purge data from the main table. 

        
The issue is that the create interim partitioned table statement which is supposed to pick and purge ~60million records from the non partition table(TABLE1) is running long. The base/main table (Say  TABLE1) is ~91GB in size and holds 71million rows and avg_row_len is showing as 1178.  The sql monitor we captured is as below. It shows almost all time spent on "log file switch (checkpoint incomplete)". The interim table creation is running in Parallel-32.


      

    

    
Have you considered filtered table move, in order to purge the
      rows? Here is how it's done:

    
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9541030000346124574

    
Alter table <table> move including rows where <filter
      condition> is typically faster than a plain delete or even
      sophisticated bulk PL/SQL procedures.

    

    


    

    
-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com


  

--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 07 2022 - 22:24:00 CEST












Original text of this message