Re: What will be the rollback plan for a table move + index rebuild operation?

You may also consider online alter table shrink, instead of move. Does not require extra space.

https://asktom.oracle.com/pls/apex/asktom.search?tag=what-is-the-difference-between-shrink-move-and-impdp

This does not address your backout question though ...

thanks,
Albert

On Tue, May 31, 2022 at 12:33 PM Lok P <loknath.73_at_gmail.com> wrote:

>
> Thank You so much Mark. I was thinking of simply running commands like
> 'alter table move subpartition XXXX'; and 'alter index XXXX rebuild
> subpartition XXXX'; to reclaim the space back. But as you pointed out, I
> saw table subpartition level the pct_free and ini_trans have non default
> values set both in table and index level. These tables are OLTP kind of
> tables which will go through heavy insert/update/deletes. So do you mean to
> say , in such scenarios we should rebuild the table with say default
> PCTFREE- 10 and then set the pctfree back to non default(as these are
> currently set) post rebuild? And I hope other non default params like
> ini_trans etc. will not play any role in this table move and rebuild
> operation.
>
> Below is two sample partition and subpartition table/index storage
> parameters:-
>
> *Table subpartition:- *
>
>
> PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
>
> 30 40 255 8388608
> 1048576 1 2147483645
>
> *Local subpartition Index:- *
>
>
> PCT_FREE INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
>
> 30 40 255 65536 1048576
> 1 2147483645
>
> *global index:-*
>
> PCT_FREE INI_TRANS MAX_TRANS
>
> 10 20 255
>
> *********
>
> *Table partition :- *
>
>
> PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
>
> 10 20 255 8388608
> 1048576 1 2147483645
>
> *global index:- *
>
> PCT_FREE INI_TRANS MAX_TRANS
>
> 10 20 255
>
> On Tue, May 31, 2022 at 11:51 PM Powell, Mark <mark.powell2_at_dxc.com>
> wrote:
>
>> LOK, while it is technically possible that reorganizing a table/index can
>> have an adverse performance impact I would not be that concerned. Just
>> check to see if any of the tables in your plan have non-default percent
>> free reservations. For example, if you have a table with a pctfree of 50
>> you probably want to lower the pctfree, perform the move, and set it back.
>> Otherwise you would create a lot of basically half full blocks that will
>> never use the free space resulting in the need to read more blocks during a
>> full table scan operation. Likewise, an index range scan on the same
>> values used before the table move could also have to visit more block after
>> the rebuild.
>>
>> An index rebuild can adversely impact an insert task if after the index
>> rebuild significantly more block splits are required to support the
>> inserts. This issue tends to fix itself with additional running of the
>> insert task.
>>
>> Again, I would not be overly concerned, but you should always check the
>> object space usage and parameter values in use before performing
>> maintenance. This should help you in spotting objects that may need
>> special handling.
>>
>>
>>
>> Mark Powell
>> Database Administration
>> (313) 592-5148
>>
>>
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Pap <oracle.developer35_at_gmail.com>
>> *Sent:* Tuesday, May 31, 2022 12:19 PM
>> *To:* Lok P <loknath.73_at_gmail.com>
>> *Cc:* Oracle L <oracle-l_at_freelists.org>
>> *Subject:* Re: What will be the rollback plan for a table move + index
>> rebuild operation?
>>
>> You are rebuilding means just dropping and creating the same table and
>> index without any change in structure and data pattern. So how can it
>> affect the performance negatively?
>>
>> On Tue, May 31, 2022 at 7:52 PM Lok P <loknath.73_at_gmail.com> wrote:
>>
>> As we are trying to move forward with table move+index rebuild operation
>> to get some storage space back on a 12.1 version database. Management asks
>> about the rollback plan just in case we see negative performance reading
>> that table/index or while performing DML on the same table , post the table
>> move+index rebuild operation. We don't have an exact lower environment with
>> similar data volume and that too fragmented one to test application. So
>> trying to understand, is there any way out here to get the exact fragmented
>> table and index back in place as it was before?
>>
>> Regards
>> Lok
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l