Re: My indexes are breaking things...

"Ramon F Herrera" <ramon_at_conexus.net> wrote in message news:c9bc36ff.0208061147.2ca962c0_at_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

First of all: There are many reasons NOT to load data DIRECTLY in a production table. You've just discovered a few of them. So I would recommend loading them into a staging table (without any constraints) and transfer them in a second step.
You should never ever sacrifice a constraint for speed, you're going to regret it, after the disaster of course.

One of your questions is just a syntax question create or replace table &1(
 ssn char(9) not null ,
 name varchar2(20) not null,
 address varchar2(25),
 city varchar2(15),
 state char(2),
 zip_code char(5),
 phone_number varchar2(12),
constraint ssn_unq unique (ssn) using index tablespace ....  )

I would never ever consider putting each table in a separate tablespace.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address