My doubt was if at all we go by DELETE using ROWID with a bulk limit of
50K approach, will it not be necessary for a table reorg/move + index
rebuild considering we have removed a vast chunk of rows out of it. One of
the current issues which we are hoping to cater with this is this existing
3 column composite index has blevel-4, so perhaps getting rid of the
unnecessary 50% data will reduce the index blevel and thus cost and wil
help queries. And we have already fixed the regular data purge schedule for
this table, so in future data should not grow to this extent.
And yes, the plan is to partition the same table so as to avoid such
problems in future but that will happen in future after deciding the
designing strategy /partitioning key and the querying pattern in current
code and that will take some time.
So basically the requirement in the current scenario is to purge ~50% of
the data(without partitioning thid table) from this table based on certain
filter criteria(mainly based on create_date).
On Sun, Jul 11, 2021 at 1:52 AM Juliano Ribeiro <supernoi@xxxxxxx> wrote:
Hello,
In a similar situation I've already had, I used what you describe as
Method 1.
I created a procedure with a loop of records that should be deleted and
created a job.
It took about 3 days in my case, but the impact was minimal.
No problems with full FRA, backups, recreating objects, permissions,
invalid objects, etc...
Att.
*Juliano Ribeiro*
http://linkedin.com/in/supernoi ;<http://br.linkedin.com/in/supernoi>
Em sáb., 10 de jul. de 2021 às 21:47, Lok P <loknath.73@xxxxxxxxx>
escreveu:
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).
