Oracle-L: RE: Difference - Rebuild and Analyze index

From: <John.Hallas_at_vodafone.co.uk>
Date: Wed, 16 Oct 2002 04:23:33 -0800
Message-ID: <F001.004EA3EC.20021016042333@fatcity.com>

Marul,
These are 2 different utilities with 2 different functions and end results

Analyze schema with compute looks at all the indexes in that schema , reads every block and produces statistics which the cost based optimiser can use to determine the best execution path

Rebuilding an index can be used for one of 2 purposes. Either to move an index to another tablespace without dropping it first (or re-enabling the index after the table itself has been moved) or to defragment the index.

Only you can now how your system is being used, whether a lot of updates/inserts/deletes are taking place, what the growth rate is. From that information you can determine when to rebuild an object. However the analyze routine does need to be run at least once if you wish to have Oracle select the best execution path. After that it needs to be run on any objects (tables/indexes) where volume or content has changed significantly from the last time the analyze was run.

John

-----Original Message-----
Sent: 16 October 2002 12:44
To: Multiple recipients of list ORACLE-L

Hi,

Can anybody please tell me the difference between -

SQL > execute DBMS_UTILITY.ANALYZE_SCHEMA('BLAH','COMPUTE',NULL,30,'FOR ALL INDEXES');
and
SQL > select 'ALTER INDEX ' || INDEX_NAME || ' REBUILD ONLINE;' from USER_INDEXES If I execute any one the above do i need to execute the other also?

After how many days/hour it should be executed.

TIA,
Marul.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Oct 16 2002 - 07:23:33 CDT