RE: unique index

From: David Boyd <davidb158_at_hotmail.com>
Date: Tue, 21 Oct 2003 10:19:25 -0800
Message-ID: <F001.005D3E18.20031021101925@fatcity.com>

Dick,

Thanks for your reply. Unfortunately, the loader's log file was overwritten before our developer called me since she tried to rerun the job.

Dave

>From: "Goulet, Dick" <DGoulet_at_vicr.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: unique index
>Date: Tue, 21 Oct 2003 08:44:32 -0800
>
>Dave,
>
> If memory is functioning normally: When you use direct=y in Sql*Loader it
>flags all of your indexes as invalid and then revalidates/rebuilds then
>when the load is complete. The reason is that loading data is faster when
>you don't have to parse index entries all the time and an invalid index
>does not need to be maintained. It would appear from your message that
>something caused the one index to not validate during the Sql*Loader run.
>Why might be revealed in the loader's log file.
>
>Dick Goulet
>Senior Oracle DBA
>Oracle Certified 8i DBA
>
>-----Original Message-----
>Sent: Tuesday, October 21, 2003 12:29 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi List,
>
>We have a job that appends records to a table using SQL Loader
>(DIRECT=TRUE). The table has two unique indexes (no constraints). Last
>Sunday, the job loaded 11839 records into the table successfully, but the
>one of the unique indexes became unusable for unknown reason. I dropped
>the
>unusable index and recreated it. The index became valid. Then the
>developer reran the job and loaded the same 11839 records into the table
>(at
>that time we did not know the first run already loaded the records). Of
>course, two unique indexes became unusable again. I could not recreate the
>unique indexes due to the duplicate keys found. Finally, I deleted all of
>23678 newly loaded records, recreated the unique indexes, and reloaded the
>11839 records. Every thing is fine now. Here are my questions:
>
>1. Why the same data crashed the index at the first time, but not at the
>end
>2. After I recreated the unique index at the first time, those records were
>already in the table. Why did not the unique index complain for the
>duplicates when we reloaded the same 11839 records into the table?
>
>Dave
>
>_________________________________________________________________
>Send and receive larger attachments with Hotmail Extra Storage.
>http://join.msn.com/?PAGE=features/es
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: David Boyd
> INET: davidb158_at_hotmail.com
>
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Goulet, Dick
> INET: DGoulet_at_vicr.com
>
>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).

---

Surf and talk on the phone at the same time with broadband Internet access. Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: David Boyd
  INET: davidb158_at_hotmail.com

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 Tue Oct 21 2003 - 13:19:25 CDT