RE: Unusable index

From: Glenn Travis <c-glenn.travis_at_wcom.com>
Date: Mon, 30 Jul 2001 08:49:11 -0700
Message-ID: <F001.00358F3F.20010730084023@fatcity.com>

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

--
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: teci_at_the-testas.net

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Travis
  INET: c-glenn.travis_at_wcom.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).

Received on Mon Jul 30 2001 - 10:49:11 CDT