Fragmented tables and indexes

[UKDBA]

Hi
I have many fragmented tables and indexes in the database and also chained rows in some tables. What i have to do in order to tune those tables and indexes.
There are few solution ihave in mind
1. change the pct increase to 1 for the tablespaces which will do the coleasing but i don't know how often it will do it.
2.recreate the tables and indexes with the bigger initial and next extents size.

but there is one problem i can havein future if i have bigger size of initial and next extent then i might have problems when those indexes and tables will try to create the next extent. so i might receive the error that unable to allocate the next extents.

please reply me what to do in order to do the tuning of those tables and indexes which are fragmented.

Cheers
UKDBA

[hrishy]

Hello

I ahvent tried it but if you are on 8i why not think about locally managed tablespaces.....

regards
hrishy

[UKDBA]

It is a good idea but i am talking about the databases which are on oracle734. and don't tell me to upgrade it to oracle 8i becasue it is in schedule but at the moment i have to tune the current database.
UKDBA

[jgmagnus]

- move data to a temporary table.
- drop all constraints for table
- delete table
- recreate table with desirable parameters (pct-free, pct-used)
- move data from temporary table back into NEW table
- enable constraints (lets hope they enable! otherwise use "exceptions" clause to find out why)
- drop temp table

voila!

- Magnus

[marist89]

or our old friend export/import...

[Sureshy]

Hi UKDBA,

If you are talking a lot of tables and indexes.

for indexes :

code :

spool rebuild_inds.sql

select 'alter index owner.'|| index_name || 'rebuild;'
from dba_indexes
where owner = 'owner'

spool off
@rebuild_ind

Replace owner with index owner name.

This will defragment your indexes, bear in mind that this will generate a lot of redo info, ensure that you have enough space to hold archived redo logs if in archivelog mode. You could also use the UNRECOVERABLE' keyword to stop the rebuild genrating any redo. It will also impact performance while rebuild is taking place. It is best to put a where condition in so that only the indexes you want to rebuild are rebuilt.

If you are not aware of which indexes to rebuild issue the :
alter index owner.index_name validate structure;

then..

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name'

If 20%+ of rows are deleted then the index should be rebuilt.

To defragment tablespaces, you could try a full export/import of your database with compress=y. This will import into one contiguous extent.

I you have OEM, you could use the re-organisation wizard.

BTW, what part of UK are you from?

Suresh

[marist89]

I forget, was REBUILD new in 7.3 or 8.0?