Re: Better Delete method

A point I missed about the indexing being sufficient - you could create a new partitioned index on the big table that removes the costing and performance issues. Since you can start by creating it invisible and unusable (which means it won't take any space) you may be able to start the creation when there is very little risk of anything more than a couple of moments of locking waits. Then rebuild each partition ONLINE in turn; then make the index visible (and drop the old one).

Regards
Jonathan Lewis

On Sun, 11 Jul 2021 at 21:59, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> Your best strategy is to upgrade to a version that is at least 12.2, then
> use the mechanism:
>
> alter table modify
> partitoon ...
> including rows where ...
> online
> update indexes ...
>
> (see: https://jonathanlewis.wordpress.com/2017/06/09/12-2-partitions/)
>
> Or if you can't work out what the partiitoning should be then 12.2 also
> allows the simpler:
> alter table t1 move
> including rows where ...
> online
> ;
>
> Any other strategy is a waste of human effort, and if you stick with
> 11.2.0.4 you'll probably end up repeating the task some time in the future
> after you finally upgrade to a maintained version of Oracle.
>
> You've mentioned elsewhere that you have some concernt about a
> multi-column index that has a blevel (or was it height) of 4. If that's the
> main reason why you want to delete lots of and rebuild the index RIGHT NOW
> (rather than waiting for the upgrade) then you need to check whether you
> could minimise the effort to rebuilding the index; or even just collecting
> the stats differently:
>
> Questions to ask yourself:
>
> a) how big would the index be if it were rebuilt with the current data and
> default pctfree - how close is that to the actual size?
>
> b) How big would the index be if you reduced the data size to the half you
> want to - and would that reduce the height/blevel; would it improve
> efficiency?
>
> c) Has the index been created with compression - how repetitive are the
> leading columns, would a rebuild with the correct compression level make
> enough difference?
>
> d) Is the height a problem because the cost calculation for indexed access
> is high enough that Oracle chooses bad execution plans, or is it that
> Oracle has to (physically) read more index branch blocks than you think
> necessary. Remember the branch blocks are likely to take up only about 0.5%
> of the total index size and in most cases you tend to (physically) read
> only the leaf blocks. Remember that you can always "set_index_stats" to lie
> to the optimizer about the blevel - also that you can set a table
> preference that gives the optimizer better understanding of the real level
> of data clustering and it's often a bad clustering_factor that makes the
> optimizer choose the wrong index.
>
> e) Does the index result in access to historic data that is only filtered
> out after it has been read from the table - does this suggest that the
> index is the wrong index and needs at least one extra column so that the
> table visits are minimised.
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Sat, 10 Jul 2021 at 20:47, Lok P <loknath.73_at_gmail.com> wrote:
>
>> 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