Thanks Tom,
This is very helpful!
"using index" will create a unique index with the same name as the PK constraint, but when we disable the constraint, the unique index is also gone. My another question is that how we keep the unique index even after we disabled the pk constraint? I know "defferrable" option will only keep the non-unique index.

you can disable keep index.


ops$tkyte@ORA9IR2> create table t ( x int, constraint t_pk primary key(x) );
 
Table created.
 
ops$tkyte@ORA9IR2> alter table t drop constraint t_pk KEEP INDEX;
 
Table altered.
 
 

Can anyone suggest a scenario under which they would use the KEEP option ?

All I can think is so that you can set it to unusable for a load (ie, disable cons, set idx unusable, load, rebuild index, enable cons) but its hard to see any benefit of this approach over just disable/enable.

Cheers
Connor

Well I guess constraints and indexes are different beasts, no?
I mean, one wants/doesn't want a constraint for data integrity, an index - for performance.
So, I may well want to drop the constraint because the data I'm inserting/updating will be inconsistent, but I still need the (non-unique) index to help me cleanse it fast.

To Yun: There's a bit of caveat with "using index":

10:16:32 flado@l440>create table t(x integer);

Table created.

Elapsed: 00:00:01.04
10:17:00 flado@l440>set timi off
10:17:06 flado@l440>create index ix_t on t(x);

Index created.

10:17:24 flado@l440>alter table t add constraint pk_t primary key(x);

Table altered.

10:18:08 flado@l440>select index_name from user_indexes
10:18:26 2 where table_name='T';

INDEX_NAME
------------------------------
IX_T

1 row selected.

/* See -- it is using the non-unique index to enforce the PK */

10:18:34 flado@l440>alter table t drop constraint pk_t;

Table altered.

10:18:57 flado@l440>select index_name from user_indexes
10:19:02 2 where table_name='T';

INDEX_NAME
------------------------------
IX_T

1 row selected.

/* Look - the index is still there, even without KEEP INDEX
(which makes my above point somewhat moot :-/ )*/

10:20:15 flado@l440>alter table t add constraint pk_t primary key(x) USING INDEX tablespace tools;
alter table t add constraint pk_t primary key(x) USING INDEX tablespace tools
*
ERROR at line 1:
ORA-01408: such column list already indexed

But if you try to create the index in a specific tablespace or with specific storage parameters, you'd hit an unexpected error, if there already is a non-unique index that could be used to enforce the constraint.

Bottom line is, in your schema creation scripts, constraints should be created before indexes, if you plan on using "using index". On the other hand, you want to create constraints *after* indexes in order to avoid creating redundant indexes -- but you can't be picky as to which tablespace the index that supports your constraint will be in.

Regards,
Flado

ah yes, but if you are using a non-unique index for a unique cons, then the index is NOT dropped anyway when the cons is disabled.

KEEP seems only to be applicable for unique indexes on unique/pk constraints...

So I'm still none the wiser on why Oracle added this facility

Well, I noted that (see above). But there is another thing that PK constraints enforce while unique indexes do not: NOT NULL. Even though I cannot imagine of what help would a B*Tree index be for finding NULLs, an index takes relatively long time to build, so you should have the option of not having to rebuild it when you later enable the constraint (after removing the NULLs of course).

Cheers, Connor!

Flado

In 

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4941517587762#7082216367226

I proposed a use:

"Scenario:
- Partitioned table
- Unique or PK constraint backed by local (or otherwise partitioned) unique 
index
- SQLLDR Direct load, some rows violate constraint, so 1 or more index 
partitions unusable.

At this point you might want to:

- disable or drop constraint KEEP INDEX
- rebuild just those index partitions that are unusable
- enable or add constraint, exceptions into etc

The KEEP INDEX becomes useful because the constraint applies to the whole table, 
whereas we only want to rebuild those index *partitions* that are unusable. 
Without KEEP INDEX the entire index would need be to rebuilt/recreated."

but I also stresed:

"(This hypothesis wholly untested btw)"

Well a while back I did test it...

SQL> create table T
  2   ( p number,
  3     q number,
  4     r number )
  5  partition by range ( p )
  6   ( partition p1 values less than ( 100 ),
  7     partition p2 values less than ( 200 ) ,
  8     partition p3 values less than ( 300 )) ;

Table created.

SQL>
SQL> create unique index TX on T ( p ) local;

Index created.

SQL>
SQL> alter table T add constraint TX primary key ( p ) ;

Table altered.

SQL>
SQL> alter session set skip_unusable_indexes = true;

Session altered.

SQL>
SQL> alter table T disable constraint TX keep index;

Table altered.

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

INDEX_NAME
------------------------------
TX

SQL>
SQL> alter index TX modify partition p1 unusable;

Index altered.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, rownum, rownum from all_Objects
  3  where rownum < 100;
insert /*+ APPEND */ into T
                          *
ERROR at line 1:
ORA-26027: unique index MCDONAC.TX partition P1 initially in unusable state

So insert-append does not work...So how about sqlldr

I created a control file of:

LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(p,q,r)
BEGINDATA
1,1,1
1,1,1
2,2,2
3,3,3
4,4,4
5,5,5
6,6,6
7,7,7
8,8,8
9,9,9
10,10,10

and ran

C:\oracle\ora92\bin>sqlldr userid=mcdonac/**** control=c:\c.ctl direct=true skip_index_maintenance=true

SQL*Loader: Release 9.2.0.5.0 - Production on Fri Jan 28 19:03:46 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Load completed - logical record count 11.

So maybe this is the reason its there ???  Well, lets repeat without using keep index....

SQL> drop table T;

Table dropped.

SQL>
SQL> create table T
  2   ( p number,
  3     q number,
  4     r number )
  5  partition by range ( p )
  6   ( partition p1 values less than ( 100 ),
  7     partition p2 values less than ( 200 ) ,
  8     partition p3 values less than ( 300 )) ;

Table created.

SQL>
SQL> create unique index TX on T ( p ) local;

Index created.

SQL>
SQL> alter table T add constraint TX primary key ( p ) ;

Table altered.

and then

C:\oracle\ora92\bin>sqlldr userid=mcdonac/**** control=c:\c.ctl direct=true skip_index_maintenance=true

SQL*Loader: Release 9.2.0.5.0 - Production on Fri Jan 28 19:09:31 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Load completed - logical record count 11.

Well there you go... we still loaded in there with no problems...The constraint was not used (since we've loaded duplicates).  When we look at the database we see:

SQL> select partition_name, status
  2  from user_ind_partitions
  3  where index_name = 'TX';

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             UNUSABLE
P2                             USABLE
P3                             USABLE

so we achieved the load without disabling the constraint at all...Even more interesting is:

SQL> select status, validated from user_constraints
  2  where constraint_name = 'TX';

STATUS   VALIDATED
-------- -------------
ENABLED  VALIDATED

so according to Oracle, our constraint is just fine, even though its backed by an unusable index partition.  So we can do very cool things like:

SQL> insert into T values (200,200,200);

1 row created.

SQL> insert into T values (200,200,200);
insert into T values (200,200,200)
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.TX) violated

ie, get successful constraint usage as long as we stay away from the problem index partition.

Which brings me back to ... WHY KEEP INDEX !?!?!?!?!?!?!?! 

dug around, turns out it can be useful when doing certain partition operations.

alter table exchange partition with unique/primary keys for example.  consider:


ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    x   int,
  4    y   int,
  5    z   int
  6  )
  7  PARTITION BY RANGE (x)
  8  (
  9    PARTITION part1 VALUES LESS THAN ( 1000000 ),
 10    PARTITION part2 VALUES LESS THAN ( 2000000 ),
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t_new
  2  ( x int, y int, z int )
  3  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert /*+ append */ into t
  2  select     rownum, rownum, rownum
  3    from big_table.big_table
  4   where rownum <= 3000000;
 
3000000 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert /*+ append */ into t_new
  2  select     rownum, rownum, rownum
  3    from big_table.big_table
  4   where rownum < 1000000;
 
999999 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x) using index local;
 
Table altered.
 
ops$tkyte@ORA9IR2> alter table t_new add constraint t_new_pk primary key(x);
 
Table altered.
 
<b>so, we have a loaded up table -- and want to swap it for the partition "part1"</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> alter table t
  2  exchange partition part1
  3  with table t_new
  4  including indexes
  5  without validation
  6  /
 
Table altered.
 
Elapsed: 00:00:00.65
ops$tkyte@ORA9IR2> /
 
Table altered.
 
Elapsed: 00:00:00.68
ops$tkyte@ORA9IR2> /
 
Table altered.
 
Elapsed: 00:00:00.56
ops$tkyte@ORA9IR2> /
 
Table altered.
 
Elapsed: 00:00:00.56

<b>if you were to trace it, you'd see

select /*+ first_rows(1) ordered */ 1
from
 "OPS$TKYTE"."T_NEW" "A" ,"OPS$TKYTE"."T" "B" where "A"."X" = "B"."X" and (
  tbl$or$idx$part$num("OPS$TKYTE"."T",0,0,0 ,"B" ."X" )  >  1 ) and
  tbl$or$idx$part$num("OPS$TKYTE"."T",0,0,0 ,"A" ."X" )  <>  1 and rownum < 2
                                                                                                                                                                            
                                                                                                                                                                            
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.53       0.55       2085       2090          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.53       0.55       2085       2090          0           0

which is just verifying the unique/primary key constraint.  So, we can</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t disable constraint t_pk KEEP INDEX;
 
Table altered.
 
Elapsed: 00:00:00.01
ops$tkyte@ORA9IR2> alter table t_new disable constraint t_NEW_pk KEEP INDEX;
 
Table altered.
 
Elapsed: 00:00:00.08
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t
  2  exchange partition part1
  3  with table t_new
  4  including indexes
  5  without validation
  6  /
 
Table altered.
 
Elapsed: 00:00:00.10
ops$tkyte@ORA9IR2> /
 
Table altered.
 
Elapsed: 00:00:00.06
ops$tkyte@ORA9IR2> /
 
Table altered.
 
Elapsed: 00:00:00.10
ops$tkyte@ORA9IR2> /
 
Table altered.
 
Elapsed: 00:00:00.06
ops$tkyte@ORA9IR2> alter table t enable novalidate constraint t_pk;
 
Table altered.
 
Elapsed: 00:00:00.07
ops$tkyte@ORA9IR2> alter table t_new enable novalidate constraint t_new_pk;
 
Table altered.
 
Elapsed: 00:00:00.04
 

Now we have bizarre scenarios like...

As I had before - I have a duplicate key in partition p1.  So when I try to rebuild the index partition I get:

SQL> alter index TX rebuild partition p1;
alter index TX rebuild partition p1
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Fair enough...I'll just delete them

SQL> delete from T partition (P1) where p = 1;
delete from T partition (P1) where p = 1
*
ERROR at line 1:
ORA-01502: index 'MCDONAC.TX' or partition of such index is in unusable state

OK, that's fair enough too...I'll just skip that index..

SQL> alter session set skip_unusable_indexes = true;

Session altered.

SQL> delete from T partition (P1) where p = 1;
delete from T partition (P1) where p = 1
*
ERROR at line 1:
ORA-01502: index 'MCDONAC.TX' or partition of such index is in unusable state

Hmmmmmmmmmmmm.... 

Connor
Personally, I'd go for:
ALTER TABLE T TRUNCATE PARTITION P1
;-)

In my (sad) view of the world the only things we do to partitions are creates, direct path inserts, truncates and exchanges... fiddling with rows is not on

But Tom's exchange point is good and worth a play.

Hey Tom, I'm confused by the "using index schema.index_name" option when creating a constraint.

<begin 10gR1 docs quote>

using_index_clause

You can specify the using_index_clause only when enabling unique or primary key constraints. You can specify the clauses of the using_index_clause in any order, but you can specify each clause only once.

+ If you specify schema.index, then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle returns an error.

+ If you specify the create_index_statement, then Oracle attempts to create the index and use it to enforce the constraint. If Oracle cannot create the index or cannot use the index to enforce the constraint, then Oracle returns an error.

+ If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:
The index receives the same name as the constraint.
If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

<end docs quote>

So the first bullet says you can pre-create an index, and then direct oracle to use that index when creating the constraint to enforce the constraint.
The third bullet says that if you don't specify one, then oracle creates one for you.

But you demonstrated in the below answer (which was 9iR1 and I duplicated on my test 10gR1 db) that you can pre-create an index, create the constraint (without having a using index clause referencing the index you just created), and oracle will still find and use that index to enforce the constraint for you.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3482146033921#3482274344120 <code>

So my questions are:

1. Is the above third bullet wrong? (or at least incomplete / misleading), or am I the one not understanding?

2. Why even have syntax for the first bulleted option, which seems to have been added around oracle 9i, since oracle will use the index anyway without explicitly specifying it?

Thanks.

the third bullet is wrong, I've corrected this in two other places in the docs so far, can you give me the URL into otn that points to this and I'll get this one too.

Oracle will KIDNAP a good enough index. if you add a primary key on a table T(a), and you have an index on T(a,b,c,d) -- it can kidnap that one and use it.

2) perhaps so that in the event there are multiple candidate indexes, you pick the one you want associated with the constraint:

ops$tkyte@ORA10G> create table t ( a int, b int, c int );
 
Table created.
 
ops$tkyte@ORA10G> create index t_idx1 on t(a,b);
 
Index created.
 
ops$tkyte@ORA10G> create index t_idx2 on t(a,c);
 
Index created.
 
ops$tkyte@ORA10G> alter table t add constraint t_pk primary key(a);
 
Table altered.
 
ops$tkyte@ORA10G> drop index t_idx1;
drop index t_idx1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
 
 
ops$tkyte@ORA10G> alter table t drop constraint t_pk;
 
Table altered.
 
ops$tkyte@ORA10G> alter table t add constraint t_pk primary key(a) using index t_idx2;
 
Table altered.
 
ops$tkyte@ORA10G> drop index t_idx1;
 
Index dropped.
 

Thanks, and the multiple candidate indexes situation you demonstrated makes total sense.

I found the bit I quoted in the docs in the sql reference under the constraint page and the using_index explanation. Follow this link then do a control f for using_index and it is about 2 or three find nexts away:

</code> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/clauses002.htm#CJAGEBIG <code>

(incidently, in case this helps anyone else):

I ran into this issue while working on an 8.1.7.4.1 database, trying to enable index key compression on primary keys of some partitioned tables. This is more difficult than I originally thought, because:

a) the syntax for "using index (create index)" doesn't exist in 8i
b) the syntax for "using index index_name" doesn't exist in 8i
c) you can't do an "alter index partition rebuild compress" in 8i
d) you can't use the "using index index_properties" portion of the add or create constraint clause to specify index key compression in the first place

But luckily, even though the docs don't say so, you can in fact use the index kidnapping technique described above.

Can a Index be created for the view ..also how to see the index created on all tables and views.

Many thanks.

Tom,

It is a surprise how Oracle was able to enforce uniqueness even without a unique index. As per your previous posting I carried out this testing:

1) create table t (a int, b int);
2) create index idx_t on t(a, b);
3) alter table t add primary key (a) using index idx_t;

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

INDEX_NAME                     UNIQUENES
------------------------------ ---------
IDX_T                          NONUNIQUE

And one limitation I have noticed is that the column defined as PK has to be leading columns on the index used:

alter table t drop primary key;
alter table t add primary key (b) using index idx_t;
alter table t add primary key (b) using index idx_t
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

Can you please give me an insight on how Oracle enforces uniqueness without a unique index?

Thanks in advance

Hi Tom,

How to get from data dictionary which action - DROP INDEX or KEEP INDEX - will be applied by default when we disable PK/UK constraint?

Let's consider following test case:

create table t1(
n1 number,
n2 number);

alter table t1 add constraint t1_uk unique (n2) using index (create unique index t1_uk on t1(n2));

create unique index t1_pk on t1(n1);

alter table t1 add constraint t1_pk primary key(n1) using index t1_pk;

alter table t1 disable constraint t1_pk;

alter table t1 disable constraint t1_uk;

select index_name from user_indexes
where table_name = 'T1';

INDEX_NAME
------------------------------
T1_PK


Obviously, if we create index first and then add constraint on it default action will be KEEP INDEX, otherwise - DROP INDEX.

But how to get this information for existing tables through USER_*, ALL_* or DBA_* (or any other) views?

Thanks,
Rustam

- "using index" will create a unique index with the same name as the PK constraint, but when we
disable the constraint, the unique index is also gone.

Exactly the behaviour our systems want, but in the past some index/constraints created index and then constraint, (though the names were always the same).

How can I find which indices were created independantly of the constraint and have been hijacked by the constraint, as opposed to those that were created by the add constraint command?

Tom,

Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E17118-04

Unique Constraints

<quote>
A unique constraint designates a column as a unique key.
A composite unique key designates a combination of columns
as the unique key. When you define a unique constraint inline,
you need only the UNIQUE keyword. When you define a unique
constraint out of line, you must also specify one or more
columns. You must define a composite unique key out of line.
...
...
If you are defining uniqueness for purposes of query performance,
then Oracle recommends that you instead create the unique index
explicitly using a CREATE UNIQUE INDEX statement.
</quote>

Does the "using index" clause in add constraint statement give additonal inforamtion to the CBO?

Does the fact that a column is UNIQUE, by virtue of the unique
index that created under the covers, but is not declared in the
table's constraints or declared inline, change how the CBO does its calculation?

Thanks
sb