-
I read some previous threads on unique contraint / index, and want to clarify some things... I understand that constraints are logical and indexes physical, that said...
When a table is altered, and an index created:
ALTER TABLE TAD.BBO ADD CONSTRAINT BBO_UK1
UNIQUE (BBO_ID) USING INDEX
TABLESPACE BBO_INDEX PCTFREE 1
STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 ) ;
The index created is a UNIQUE index, correct ?
1) Then...is it possible to disable the UNIQUE contraint on that column, and do nothing to the index ? Will it need to be rebuilt ?
2) ALSO, do you think that I will see a performance improvement on INSERTS if the column does not have a UNIQUE contraint (since it has to be checked at every insert) ?
-
1) As soon as u disable the unique constraint or primary key constraint , the indexes are dopped.
2) Yes performance would be better for non-unique index during insert but would require more
space and for primary key , disabling it would not drop the index.
-
Code:
SQL> create table emp (empno NUMBER(20),
2 emp_name VARCHAR2(100));
Table created.
SQL> ALTER TABLE emp ADD CONSTRAINT emp_uk
2 UNIQUE(empno) USING INDEX
3 TABLESPACE USERS PCTFREE 1
4 STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0);
Table altered.
SQL> INSERT INTO emp VALUES ( 1,'SAM');
1 row created.
SQL> INSERT INTO emp VALUES (2,'PAT');
1 row created.
SQL> INSERT INTO emp VALUES (1,'JESSICA');
INSERT INTO emp VALUES (1,'JESSICA')
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.EMP_UK) violated
SQL> ALTER TABLE emp DISABLE CONSTRAINT emp_uk;
Table altered.
SQL> INSERT INTO emp VALUES (1,'JESSICA');
1 row created.
SQL> DELETE emp WHERE emp_name = 'JESSICA';
1 row deleted.
SQL> commit;
Commit complete.
SQL> CREATE UNIQUE INDEX emp_u_indx ON emp
2 (empno) PCTFREE 1
3 STORAGE (INITIAL 1K NEXT 1K PCTINCREASE 0);
Index created.
SQL> INSERT INTO emp VALUES (1,'JESSICA');
INSERT INTO emp VALUES (1,'JESSICA')
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.EMP_U_INDX) violated
SQL> ALTER TABLE emp ENABLE CONSTRAINT emp_uk;
Table altered.
SQL> commit;
Commit complete.
SQL> INSERT INTO emp VALUES (1,'JESSICA');
INSERT INTO emp VALUES (1,'JESSICA')
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.EMP_UK) violated
As you can see here the Index that would be created at the time of creating a unique key with the index clause. Your unique key would be indexed.
Now to answer your second question:
No, when you disable the unique key constraint, the index associated with that also gets disabled/droped. On this case you would have to create a unique index which then would be similar to that of having unique key constraint.
The performance improvement would be very visible in the UPDATE, DELETE OR SELECT when you use the indexed colum in the condition. In the case of insert I don't think unless you INSERT with a SELECT clause with the indexed colum under the condition.
One other thing is say that you have created a constraint and you use the select, the select would use the unique key constraint index. Now you go and disable the unique key and create a unique index, and do a select oracle will now use the unique index. After this you enable the unique constraint, oracle would now use the unique index, over the unique constraint index. Now say that you disable that unique constaint and see what happens? :D
Sam
-
To clarify my second question:
"2) ALSO, do you think that I will see a performance improvement on INSERTS if the column does not have a UNIQUE contraint (since it has to be checked at every insert) ?"...
...what if the column has a regular index, not a UNIQUE index or a UNIQUE constraint, will I see performance improvement on inserts.
Any input anyone ?
[Edited by khussain on 11-21-2001 at 11:27 AM]
