AW: Re: Re: Better Delete method

Hi,

I meant that the problem is the index behind the pk that should be created anyway.
So just create the unique index separately with dop whatever you want and then create the pk using the created index.

This is the fastest way I know. At the end you should rest the parallel degree of the index to one. As this could lead to unexpectable execute plan.

• Original-Nachricht --- Von: Lok P Betreff: Re: Re: Better Delete method Datum: 11. Juli 2021, 21:59 An: ahmed.fikri_at_t-online.de Cc: Oracle L

Actually Validate status says all the existing data are obeying the constraints rule along with incoming data. But novalidate says there is no guarantee of existing data but new incoming rows will be validated against the constraint. So we want to keep the constraint in validate state if it's possible without any performance impact. So wanted to understand if it will happen in seconds if created post unique index creation or any other faster way possible for creating that pk constraints faster?

On Mon, 12 Jul 2021, 1:21 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:

  the index has to be rebuilt. Validate or no validate doesn't make   difference in your case.

  Gesendet mit der Telekom Mail App

• Original-Nachricht --- Von: Lok P Betreff: Re: Better Delete method Datum: 11. Juli 2021, 21:25 An: ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> Cc: list, oracle

  Thank you.

  If I get it correct, it looks like the CTAS way will be the fastest way   in our case and should be followed if we can afford a couple of hours of   downtime. And as you mentioned the delete based on ROWID approach in bulk   collect will generate gobs of UNDo/REDO and also we may need to reorg the   table/index at the end considering 50% of the data is getting deleted.

  And yes both the Primary key constraint/index of data integrity and other   composite indexes for Select queries are needed . But as you said we will   try to create these two indexes in parallel from two sessions to make   this process faster. But the doubt i had was, if i create a unique index   in parallel-32 from one session and later on will create PK constraint   using the previously created unique index will that happen in seconds?   and the constraint can be created in VALIDATE status? or the PK   constraint validation will separately happen and will take time then?

  On Sun, Jul 11, 2021 at 10:37 PM 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