Re: My indexes are breaking things...

Comments below

Anurag

"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?

* Firstly, Yes there is a way to name the index using a "using index ..."

   clause in the create table statement. For details, see the SQL reference    manual at tahiti.oracle.com
* In your case, my suggestion would be that if you are using 8i and above,

   you should look into the deferrable and/or (no)validate option for unique index creation.    All of which you will find in the SQL reference. * Even if you are not using 8i, once you know the name of the unique constraint .. and know that

   you can drop it and recreate it .. you should have your solution

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

* post your control file and specify what version Oracle you are using.

   Only then can somebody give a more suitable answer.

>
>
> Thanks for any insight and comments.
>
> -Ramon F. Herrera
Received on Tue Aug 06 2002 - 20:48:12 CDT