Oracle reclaim waste space : Index fragmentation

[SriKrishna]

Hi ,

got a doubt, hope it can be clarified.

when we check top growth tables in DB02, we noticed the below facts.

1. when tables grow and shrink due to DML operations, waste space is increasing in tables.

2. and indexes are getting fragmented too.

so, my queries are :

1. will oracle reclaim/reuse waste space ?

2. is it recommended to re-org tables regularly ?

3. is it recommended to rebuild indexes too ?

is there any standard documentation in any KBA etc?

Thanks !!!

[former_member224542]

Hello Krishna,

1. will oracle reclaim/reuse waste space ?

- the deleted space will still be reusable to the same table via freelist

2. is it recommended to re-org tables regularly ?

- not really. let's say someone performed mass deletion on a certain table and you want to take the freed up space back to your tablespace, then a reorg will do. But if you anticipate that the same table will be loaded again, why will you still perform a reorg?

3. is it recommended to rebuild indexes too ?

- yes, generally when you have performance issues, you should check if the index has a significantly large space versus its parent table; when it becomes unusable; and if the deleted leaf blocks is more than 20%. Check the link below for better explanation.

When to rebuild index ? (Oracle Database: Lets explore it)

[former_member207186]

Hi,

Regarding this issue/question, you can check carefully the SAP Notes below:

821687 - "FAQ: Space utilization and fragmentation in Oracle"

771929 - "FAQ: Index fragmentation"

541538 - "FAQ: Reorganization"

646681 - "Reorganization of tables with BRSPACE"

You can also refer to SAP KBA 1830923 - "Database Growth".

Regards,

Bíborka

[JamesZ]

Hi ,

Normally the waste space caused by fragment won't be used, so we have to reorganize table regularly. Then the waste space can be cleaned.

If you are using SAP brspace to do the reorganize a table, then index will be reorganized as well. Also it is recommended to rebuild index as well, the index fragment can decrease the performance.

SAP note 666061 and 771929 talk a little about fragment. To reorganize table, you can refer to SAP note 646681 to use brspace.

Best regards,

James