Oracle-L: RE: unique index

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 21 Oct 2003 08:44:32 -0800
Message-ID: <F001.005D3DFF.20031021084432@fatcity.com>

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:

Why the same data crashed the index at the first time, but not at the end
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
---------------------------------------------------------------------