quick Q on rebuild index 1

[Scunningham99]

Hi

After we have rebuilt an index. Will we loose the stats. Ie do we have to gather stats again after it has been rebuilt?

My question is prompted because we are getting alot of full scans in v$session_longops after we rebuilt alot of indexes without gathering stats again!

Thanks in advance!



Sy UK

 

[rasprer]

Sy...good question. I just rebuilt all my indexes as well. Anyone with input on this?

 

[Scunningham99]

We ran stats after rebuild.... because del_lf_rows was greater than 20%.

The After we ran the stats on the indexes belonging to the tables that were displaying full table scans we noticed that the opname in v$session_longops changed from "Table Scan" to "Index Fast Full Scan".

Before we ran the stats users were complaining about performance after the rebuild. It is starting to look alot better now. This would suggest to me that this was the problem!!!!

Please can anyone clarify this is the case as this is only my personal experience as I cannot find any notes on it!

Thanks in advance!



Sy UK

 

[dbtoo2001]

Did you tell it to?

Gathering Index Statistics
Oracle can gather some statistics automatically while creating or rebuilding a B-tree or bitmap index. The COMPUTE STATISTICS option of CREATE INDEX or ALTER INDEX ... REBUILD enables this gathering of statistics.

The statistics that Oracle gathers for the COMPUTE STATISTICS option depend on whether the index is partitioned or nonpartitioned.

For a nonpartitioned index, Oracle gathers index, table, and column statistics while creating or rebuilding the index. In a concatenated-key index, the column statistics refer only to the leading column of the key.

REBUILD
[ {PARTITION partition | SUBPARTITION subpartition}
| {REVERSE | NOREVERSE} ]
[ parallel_clause
| TABLESPACE tablespace
| PARAMETERS ('ODCI_parameters')
| ONLINE
| COMPUTE STATISTICS
| physical_attributes_clause
| key_compression
| logging_clause ]
[ parallel_clause
| TABLESPACE tablespace
| PARAMETERS ('ODCI_parameters')
| ONLINE
| COMPUTE STATISTICS
| physical_attributes_clause
| key_compression
| logging_clause ]...

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm

 

[Scunningham99]

thanks for your help! ver usefull!!

rgds



Sy UK