Randolf Geist has written a test case to show what happens when a performance tuning exercise isn't continued because the Developer / DBA / Consultant only looks at the first set of rows returned by a query.
A front-end tool may present only the first N set of rows but if the query is part of an application code (eg copying/transforming a large set of rows), the application would actually be waiting for ALL the rows to be processed.
2 comments:
Hi Hemant;
Once I issue:
alter table move
or
rebuild index.
Is it necessary to analyze table or index?
When you do an ALTER TABLE MOVE you have to REBUILD the indexes.
It is not strictly necessary to GATHER_STATS again. Oracle retains previous stats. However, step back and think about why you might have done the MOVE and/or REBUILD.
Say you have deleted a very large portion of the rows, or the table contains data for multiple DEPARTMENTs and you have deleted data for one or more departments. Do you want the Table/Column/Index statistics to still reflect that deleted data ?
You have shrunk a table down from 1million rows and 50000 blocks to 200thousand rows and 10000 blocks. Do you want the statistics to reflect 1million rows and 50000 blocks ?
So, whether you need to GATHER_STATS depends on what has happened to the data !
Hemant
Post a Comment