Re: analyze indexes

From: Rajagopal Venkataramany <rajagopalvr_at_hotmail.com>
Date: Sat, 13 May 2000 16:26:47 GMT
Message-Id: <10496.105588@fatcity.com>

Hi Gregory,

My observations on the situation :

If you do not drop the indexes before the load process, if the load volume is high, you might end up spending more time loading the data. Generally, it is not a recommended practice.
Analyze is an independent step. The table statistics do not get updated automatically. So irrespective of whether the index is there or not, the analyze command need to be fired independantly at the end of the load operation.

     Again, whether to issue a analyze command depends on the volume of
     data that has been added. If the volume is in-significant then we
     do not have to analyze the tables/indexes as they would not matter
     anyway. This is not true if the load volume is high.

When you rebuild the index, the storage requirements would be high as the existing index space is still retained till the rebuild is completed. In other words you would require twice the size of the index space when you attempt to rebuild. Alternatively, you can drop and recreate the index details, if that is feasible.

Regards
Rajagopal Venkataramany

----Original Message Follows----

From: Gregory Conron <c>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Subject: analyze indexes
Date: Fri, 12 May 2000 15:45:25 -0800

Quick question - after a direct path load into a table via sqlloader, we re-create the indexes (we have had problems leaving the indexes on in the past during direct loads) and then analyze the table and indexes to gather statistics. However, re-creating the indexes takes way too long (55+million row table - about 4 hours to rebuild the indexes), so we are going to switch to a conventional path load and leave the indexes on. My question is should the analyze step be left in, or can it be removed since the indexes aren't being dropped?

Thanks,
GC
--

There is something inherently wrong with the world when Bill Gates is a famous billionaire and Dennis Ritchie lives in relative obscurity.
--

Author: Gregory Conron

INET: gconron_at_hfx.andara.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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).

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com Received on Sat May 13 2000 - 11:26:47 CDT