WHEN TO REORGANIZE TABLES AND REBUILD INDEXES.

When to reorganize tables and rebuild indexes:

Table fragmentation:When rows are not stored contiguously or if rows are split auto more than one block,performance decrease because these rows required additional block accesses.
Note that table fragmentation is different from file fragmentation.When a lot of DML operations are applied on a table,the table will become fragmentation because DML does not release free space from the table below the HWM.
HWM is an indicator of used blocks in the database.Blocks below the high water mark (used blocks) have at least once contained data.This data might have been deleted.Since oracle knows that blocks before the high water mark didn't have data,it only reads block up to the high water when doing a full table scan.
*DDL statements always resets the HWM.

Table size (with fragmentation)
Sql>exec dbms_stat.gather_table_stats('SCOTT','BIG1');
Sql>select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name='BIG1'
output of the command:
table_name size
BIG1 72952 kb

Actual data in table
Sql>select table_name,round((num_rows*avg_row_len/1024),||'kb' "size" from user_tables where table_name='BIG1';
output
table_name size
BIG1 30604.2 kb

72952-30604=42348 kb is wasted space in table
The difference between two values is 60% and pct free 10%(default)-so,the table has 50% extra space which is wasted becuase there is no data.
How to reset HWM/remove fragmentation ?
4 options:
1)alter table .. move to another tablespace.
2)export,truncate or drop import.
3)CTAS method
4)dbms_redifinition

To check indexes
sql>select status,index_name from user_indexes where table_name='BIG1';
output
status index_name
unusable bigidx
sql>alter index bigidx rebuild;
select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) from index_stats where name='';
you may decide that index should be rebuilt if more than 20% of its rows are deleted.

How can you determine if an index needs to be dropped and rebuilt?
Level: Intermediate
Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio
BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3

To check the compress or not compress mode:
Sql>select compression from dba_tables where table_name in <'tablename'>;
If some times show actual size is greater than the table size it is due to in compress mode of table
If table is compress make this to no compress mode
Sql>alter table move nocompress;