RE: Unusable index

[JOE TESTA]

Well believe what you want in the docs(they have been know to been wrong before, ask rachel today about LMT) :)   I'm speaking from experience of it happening at client site.    Docs are nice but I wouldnt rely on them as the final word, the final word is what happens on your version/platform ;)   joe   >>> [EMAIL PROTECTED] 07/30/01 12:40PM >>> Are you sure?  I was just reading about sqlldr and on page 8-10 of the Oracle 8i Utilities manual, it says; "During a direct path load, performance is improved by using temporary storage. After each block is formatted, the new index keys are put to a sort (temporary) segment. The old index and the new keys are merged at load finish time to create the new index. The old index, sort (temporary) segment, and new index segment all require storage until the merge is complete. Then the old index and temporary segment are removed. During a conventional path load, every time a row is inserted the index is updated. This method does not require temporary storage space, but it does add processing time." I don't this means the index is invalidated.  I know when using the direct path load, the table and index are inaccessible to other users though. That said, here's more info; "SQL*Loader will leave indexes in Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it. Any SQL statement that tries to use an index that is in Index Unusable state returns an error. The following conditions cause the direct path option to leave an index or a partition of a partitioned index in Index Unusable state: . SQL*Loader runs out of space for the index, and cannot update the index. . The data is not in the order specified by the SORTED INDEXES clause. . There is an instance failure, or the Oracle shadow process fails while building the index. . There are duplicate keys in a unique index. . Data save points are being used, and the load fails or is terminated by a keyboard interrupt after a data save point occurred." FYI. -----Original Message----- Sent: Saturday, July 28, 2001 10:30 AM To: Multiple recipients of list ORACLE-L an unusuable index is just that oracle can't use it. direct path load will make indexes unusable. joe prasad maganti wrote: > > Hi dba's > > can anybody clarify what is unusable index, in what > situation will become unusable? > > and the solution to reuse it? > > thanx in adva. > > prasad. > > __________________________________________________ > Do You Yahoo!? > Make international calls for as low as $.04/minute with Yahoo! Messenger > http://phonecard.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: prasad maganti >   INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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) -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa   INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis   INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).