OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Unique Indexes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unique Indexes [message #58789]

Tue, 30 September 2003 11:41

Kapil
Messages: 145
Registered: May 2002

Senior Member

I have Unique indexes on my tables, b4 i load data into the tables, i want to disable the indexes and load the data. can you please tell the syntax for it to disable the unique and non-unique indexes, not primary key indexes. using 8i.
Thanks

Report message to a moderator

Re: Unique Indexes [message #58791 is a reply to message #58789]

Tue, 30 September 2003 12:28

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

i beleive,
you are looking into 'disabling the unique keys' 
and not indexes.
You need to drop the indexes.
by default unique keys create unique indexes.
if you have have explicitly crated those indexes
you may want to drop them.
the general practise is to drop all the indexes, unique or primary (which is created by a primary key,which again forces unique) , to fasten the data load.

Report message to a moderator

Re: Unique Indexes [message #58792 is a reply to message #58791]

Tue, 30 September 2003 12:31

Kapil
Messages: 145
Registered: May 2002

Senior Member

GoDDDDDDDDDDDDD!!!!!!!, Thanks buddy. so i have to drop and re-create them. ok. so there is no way i can disable them. fine. good to know. Thanks again.

Report message to a moderator

Re: Unique Indexes [message #58793 is a reply to message #58792]

Tue, 30 September 2003 13:00

Thiru
Messages: 1089
Registered: May 2002

Senior Member

You may also want to look into setting the Indexes into UNUSABLE state for non-unique indexes. For Primary key or Unique keys, you will be disabling and reenabling the constraints with the appropriate index storage clause to recreate the associated indexes.

Example for Non-unique index :

SQL> select index_name,status from user_indexes where table_name='T';

INDEX_NAME STATUS
------------------------------ --------
T_EMP_IDX VALID

-- Mark the Index UNUSABLE

SQL> alter index t_emp_idx unusable;

Index altered.

-- Disable error reporting for unusable index

SQL> alter session set SKIP_UNUSABLE_INDEXES=true;

Session altered.

-- Do the load.You will not get the index error now.

SQL> insert into t select * from t;

17 rows created.

SQL> commit;

Commit complete.

SQL> select index_name,status from user_indexes where table_name='T';

INDEX_NAME STATUS
------------------------------ --------
T_EMP_IDX UNUSABLE

-- Rebuild the index now

SQL> alter index t_emp_idx rebuild;

Index altered.

SQL> select index_name,status from user_indexes where table_name='T';

INDEX_NAME STATUS
------------------------------ --------
T_EMP_IDX VALID

Report message to a moderator

Re: Unique Indexes [message #58794 is a reply to message #58793]

Tue, 30 September 2003 13:16

Kapil
Messages: 145
Registered: May 2002

Senior Member

Well, i am having Unique indexes and non-unique indexes and I am using sql loader to load the data. so alter session wouldn't help me i guess. and these unique indexes have been create like
create unique index ind_unq on tab(col1,col2);
so is there a way to disable this index b4 i use sqlldr? is so how i would do this.
Thanks a lot.

Report message to a moderator

Re: Unique Indexes [message #58797 is a reply to message #58794]

Tue, 30 September 2003 19:36

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Since skipping indexes on a Unique(or Primary) key would pave way for duplicate values during the load,Oracle does report error about UNUSABLE indexes even when you set SKIP_UNUSABLE_INDEXES=TRUE . Hence this is useful for only Non-Unique indexes as I mentioned earlier.

If you want to do this with SQLLoader, its not much different.
1. Make the non-unique indexes UNUSABLE
2. Use sqlloader parameter skip_unusable_indexes=true
when loading

eg) sqlldr test/test data=data.dat control=control.ctl skip_unusable_indexes=true bad=.....

3.After the load is complete,rebuild the indexes.

Again, for Unique(or Primary) key indexes, you cant skip in such a fashion(for data integrity purposes) and you should disable/reenable the constraint using appropriate index clause which inturn will drop and recreate the index.

-Thiru

Report message to a moderator

Re: Unique Indexes [message #58798 is a reply to message #58797]

Tue, 30 September 2003 20:05

Kapil
Messages: 145
Registered: May 2002

Senior Member

whats wrong with this syntax?
i created an index on (id,name)

create unique index tst_ind on tstlob(id,name);

and now i am trying to disable with this following cmd, but it doesn't work.. what is the correct syntax?

alter table tstlob disable novalidate unique (ID,NAME);

Report message to a moderator

Re: Unique Indexes [message #58802 is a reply to message #58798]

Wed, 01 October 2003 06:45

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Are you trying to disable the constraint ?

First of all,I find it easier to track constraints when I create constraints instead of unique indexes.ie I generally dont create unique indexes.I create unique constraints so that I can see them in USER_CONSTRAINTS and USER_CONS_COLUMNS.

In your case,you havent created unique constraint,just an unique index and hence that syntax wont work.

For eg)

alter table t add constraint t_empno_UK unique(empno);

creates the constraint and the unique index.

Now I can disable the constraint (and drop the index) via

alter table t disable unique(empno);

Let's enable the constraint(and hence recreate the index)

alter table t enable validate constraint t_empno_UK;

and lets disable using constraint name this time via

alter table t disable constraint t_empno_UK;

This will drop the index too.

When you are explicitly creating such unique indexes, you will need to drop the indexes, becos you have no constraints to disable , although Oracle enforces the unique constraint via the index.

Do you understand now ?

-Thiru

Report message to a moderator

Re: Unique Indexes [message #58805 is a reply to message #58802]

Wed, 01 October 2003 08:57

Kapil
Messages: 145
Registered: May 2002

Senior Member

Yes, Sir..Thanks a lot for the information.. u can charge me for this.. just kidding.. Thanks again. wow..

Report message to a moderator

Re: Unique Indexes [message #58807 is a reply to message #58805]

Wed, 01 October 2003 10:40

Thiru
Messages: 1089
Registered: May 2002

Senior Member

You are right, I can use some extra income :-) . I dont usually charge my clients much, just $100 per hour.

But for you,its free !

-Thiru

Report message to a moderator

Re: Unique Indexes [message #58814 is a reply to message #58807]

Wed, 01 October 2003 14:38

Kapil
Messages: 145
Registered: May 2002

Senior Member

I am seriuos !!! Thiru. i would like to send you some gift at least. if you can provide me your address.
Thanks men.. u have been a gr8 help to me.
Thanks again.

Report message to a moderator

Previous Topic:	Rollback Segments Parameter
Next Topic:	recovery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 08:28:39 CDT 2024