Home -> Mailing Lists -> Oracle-L -> AW: Better Delete method

AW: Better Delete method

From: <ahmed.fikri_at_t-online.de>
Date: Sun, 11 Jul 2021 20:02:17 +0200 (CEST)
Message-ID: <1626026537584.2905562.77ed62e88a020c080af711a246cab0c862868bdc_at_spica.telekom.de> 








with the current Oracle architecture this would only work for tables that 
fit into part of the buffer cache (the changes would only be made in memory 
and not written to the data files in case of rollback).
My solution that I use is based on partition exchange and works very well 
for me, but this could be implemented internally by Oracle itself. And 
that's what I mean by the willingness to implement new things.
I'm pretty sure there are many ways to address such common use cases.
The user should be able to decide (and take the risk) whether or not to 
generate UNDO. As a user, I sometimes wonder why Oracle generates more data 
to manage my data.
 


Best regards


Ahmed
 
 
 
 


-----Original-Nachricht-----


Betreff: Re: Better Delete method


Datum: 2021-07-11T19:19:29+0200


Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com>
An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>
 
 
 


The problem is that Oracle is not ready to change concepts from the 
1970s.So why not introduce (implement) a note /*+ dml_no_consistency_read * 
/, for example. If this hint is used in a delete statement, nothing is 
written to the UNDO and if the user has chosen to roll back, appropriate 
redos are identified and skipped.
 


And how exactly does a user rollback when there is no undo information - 
other then doing a partial database recovery (which is how it could become 
possible to avoid applying the "dml_no_consistency read" redo vectors).
The same would apply, of course, if a big "dml_no_consistency_read" crashes 
since smon wouldn't be able to clean up the mess because there would be no 
undo.
 


Regards


Jonathan Lewis
 



On Sun, 11 Jul 2021 at 18:07, ahmed.fikri_at_t-online.de
<mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
<mailto:ahmed.fikri_at_t-online.de> > wrote:
  Given the size of the table you mentioned, I'm assuming that clearing the
  data via partition exchange will require a maintenance window of less
  than 60 minutes.
   


  Are the PK and the other index critical to the application (you mentioned
  no FKs are pointing to this table)? Why not create them in two sessions
  each with dop 32 afterwards? The whole operation should take less then 60
  minutes.
   


  If no downtime is tolerated, you can hide your table behind a view and
  use trigger to implement some sort of redo mechanism analog to the one
  from Oracle itself.
   


  To remove more than 400 GB of data, UNDO / REDO should be avoided as it
  only pollutes the entire database. They are just a boilerplate. Why do we
  need to generate all of this data when in some situations we are 300%
  sure that we don't need it?(Your first method is not efficient. Also
  requires one single block read for each row -- this is too huge)
   


  The problem is that Oracle is not ready to change concepts from the
  1970s.So why not introduce (implement) a note /*+ dml_no_consistency_read


    
  
  •   /, for example. If this hint is used in a delete statement, nothing is
  written to the UNDO and if the user has chosen to roll back, appropriate
  redos are identified and skipped.
   





  Best regards


  Ahmed
   
   
   
   
   
   
   


  -----Original-Nachricht-----


  Betreff: Re: Better Delete method


  Datum: 2021-07-11T15:07:14+0200


  Von: "Lok P" <loknath.73_at_gmail.com <mailto:loknath.73_at_gmail.com> >
  An: "ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> " <
  ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> >
   
   
   


  Thank you Ahmed. So this code is doing data purge by creating a temp
  table which will be of similar structure(indexes and constraints needs to
  be exactly same) as of main table but is partitioned , so as to take
  advantage of partition exchange approach. But yes, it seems like DB
  resource/time consumption in this method is the same as method-2, which I
  mentioned in my initial post. And it does need downtime , because in
  between the CTAS and final partition exchange if any DML operation
  happens on the base table , that data will be missed. 
   


  And is it correct that in either of the ways(using CTAS with/without
  partition exchange), the primary key constraint can be created with a
  VALIDATE state only without much time and resource , if we first create
  the UNIQUE index and then create PK constraints in the VALIDATE state
  using that same unique index? Please correct me if I'm wrong.
   
   
   
   



  On Sun, Jul 11, 2021 at 1:44 AM ahmed.fikri_at_t-online.de
  <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
  <mailto:ahmed.fikri_at_t-online.de> > wrote:
    Hi,
     


    in the attached file is a method to delete data from big table using
    partition exchange (you have to enhance the method to use indexes....)
     


    Best regards


    Ahmed

     
     
     




    -----Original-Nachricht-----


    Betreff: Better Delete method


    Datum: 2021-07-10T21:47:55+0200


    Von: "Lok P" <loknath.73_at_gmail.com <mailto:loknath.73_at_gmail.com> >
    An: "Oracle L" <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> 


    >

     
     
     




    Hello , this database version is 11.2.0.4 of Oracle Exadata.  A
    table(say TAB1) is there holding ~900 million rows with size ~222GB and
    it's not partitioned. It has two indexes , one with a three column
    composite index with size ~98Gb and other is the primary key on one
    column with size ~23GB. As a part of the requirement we need to
    delete/purge 50% of its data from this table. No referential
    constraints exist here. So I wanted to understand, out of the two
    below, which is the best method to opt for? or any other possible
    better option?
     


    I can understand method-1 is achievable ONLINE, but is slower while
    method-2 will be faster. So if we can afford ~1-2hrs of downtime, is it
    good to go for method -2 as the delete approach. As because deleting
    50% rows even in method-1 may also need a table move+index
    rebuild(which will again need downtime on 11.2) to lower the high water
    mark and make the indexes compact and back to normal. Please advise.

     
     
    Method-1:-
     
    steps- 1:
      In a cursor pick the ~450million rowids of the rows to be deleted 


    based on filter criteria;


    step2:-


       Delete based on ROW_IDS in a bulk collect fashion with LIMIT 50K
    rows ids at oneshot and commit within loop.
     


    Method-2:- 

     
      Step- 1
          Create a new table using CTAS a new table TAB1_BKP AS select *


    from TAB1 where (required filter criteria which will pick ~450 required
    rows);

      Step-2:-
         Create the composite index in<http://parallel.to> make it as fast
    as possible 
         Create the unique index on the same column as there in PK.
         Create the primary constraints  with NOVALIDATE(because creating


    it with validate may take a lot of time to validate existing data)
    using the above unique index (This operation should happen in seconds
    as the index is already created in the above step.)


       Rename the TAB1_BKP as TAB1 and TAB1 as TAB1_BKP(which can be served
    as backup for a few days and later dropped).

           
     
     
 






--
http://www.freelists.org/webpage/oracle-l


Received on Sun Jul 11 2021 - 20:02:17 CEST












Original text of this message