Re: Better Delete method

*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 < 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>
>
> An: "ahmed.fikri_at_t-online.de" <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 <
> 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>
>>
>> An: "Oracle L" <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 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