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

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