Would you like the result to be partitioned so that future purging is cheap and
quick?
IF so, arrange the disjoint selections by desired partition to separate tables,
create the empty partitioned table and partition exchange the pieces in.
All your disjoint selects and be append mode, and it is likely that ordering
the selected results for insert is useful (you are on too backward a version to
use attribute clustering) to match your composite index.
Good luck.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Lok P
Sent: Saturday, July 10, 2021 3:48 PM
To: Oracle L
Subject: Better Delete method
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).
