My indexes are breaking things...

From: Ramon F Herrera <ramon_at_conexus.net>
Date: 6 Aug 2002 12:47:00 -0700
Message-ID: <c9bc36ff.0208061147.2ca962c0@posting.google.com>

My indexes are getting corrupted somehow, and I need some expert insight. I have several tables with _identical_ structure, which are created like this:

---

create or replace table &1(

	ssn		char(9) not null unique,
	name		varchar2(20) not null,
	address		varchar2(25),
	city		varchar2(15),
	state		char(2),
	zip_code	char(5),
	phone_number	varchar2(12)
	)

tablespace &1;

---

I always try to have every table and related files under the same name; for instance, a table named 'XYZ' will reside on a tablespace called 'XYZ' (see &1 above), and the data use by sqlldr will be in a file called 'xyx.dat' with a control file named 'xyz.ctl' (see $CLIENT below).

The following bash script is used to truncate and reload my tables periodically:

---

#!/bin/bash

if [ $# != 1 ]; then

	echo usage: load \<client-name\>
	exit 1

fi

CLIENT=$1 cd $HOME/oracle/

if [ ! -f $CLIENT.dat ]; then

	echo load\: unknown client \'$CLIENT\'
	exit 2

fi

sqlplus username/password @unindex $CLIENT

sqlplus username/password @truncate $CLIENT

sqlldr username/password errors=200000 \

        direct=true parallel=true control=$CLIENT

sqlplus username/password @newindex $CLIENT

exit 0

---

These are the issues:

When a table is created, the 'UNIQUE' constraint on the primary key (SSN) forces an index to be created. This index is making my life miserable. Is there any way to prevent this index from being created (I will add it afterwards)? Or is there at least a way to use my own name for that forced index?

The existence of that dawn index also prevents me from using the more efficient 'direct' and 'parallel' SQL*Loader options. One more time, this index (obviously needed for fast access and SSN uniqueness) is in the way.

After fiddling around a little with the script above (taking out te 'direct' and 'parallel'), my tables _seem_ to load properly, without any errors. However, later on I get a message about the indexes being in a bad or unusable state.

Thanks for any insight and comments.

-Ramon F. Herrera Received on Tue Aug 06 2002 - 14:47:00 CDT