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@xxxxxxxxxxx <
ahmed.fikri@xxxxxxxxxxx> 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@xxxxxxxxx>
An: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
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).
