One note: Oracle does not allow parallel creation of a VALIDATEd
constraint. You'll need to create it NOVALIDATE, which should return
immediately, then ALTER...VALIDATE. I don't recall atm if both session DDL
and the table need to be set to parallel prior to the ALTER though.
Testing will tell you the answer.
Also, wouldn't creating multiple indexes on the same table at the same time
cause more waits than using a single session with parallel?
Rich
On Sun, Jul 11, 2021, 14:25 Lok P <loknath.73@xxxxxxxxx> wrote:
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@xxxxxxxxxxx <
ahmed.fikri@xxxxxxxxxxx> 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@xxxxxxxxx>
An: "ahmed.fikri@xxxxxxxxxxx" <ahmed.fikri@xxxxxxxxxxx>
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).