I get the page which is 

Just curious...how does Oracle enforce a PK constraint using a non-unique index? 

create table t (i int);
create index idx on t(i);
alter table t add primary key(i);

bgi> insert into t values (1);

1 row created.

SQL> /
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS_C0076675) violated

How does Oracle know that 1 is already in there? By looking it up in the index, right? 

This sounds so trivial i.e. when there is at least 1 entry in the index for that data, throw a ORA-00001 to enforce the PK.

But wasnt it until very recently that Oracle seemed to <b>require</b> a unique index to enforce a PK constraint?

Thanks 

"create the constraint "deferrable" and it'll use non-unique indexes. index the columns and THEN add the constraints and the constraint will "kidnap" the index (non-unique index)"

I have a table with a PK constraint which automatically created the UNIQUE index. How can I modify this to use a non-unique index?

Not sure I understand your suggestion above, how can I create the constraint deferrable, it is already created.

I have the following

SQL> create table t (i int primary key);

Table created.

SQL> select INDEX_NAME,TABLE_NAME, UNIQUENESS from user_indexes where table_name='T';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
SYS_C0076676                   T                              UNIQUE

Now, how can I modify the index to be non-unique and still keep the PK constraint?

Thanks

 

SQL> create table t (i int primary key);

Table created.

SQL> set lines 200                     
SQL> select INDEX_NAME,TABLE_NAME, UNIQUENESS from user_indexes where table_name='T';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
SYS_C0076677                   T                              UNIQUE

SQL> alter table t disable primary key;

Table altered.

SQL> create index idx on t(i);

Index created.

SQL> alter table t enable primary key;

Table altered.

SQL> select INDEX_NAME,TABLE_NAME, UNIQUENESS from user_indexes where table_name='T';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
IDX                            T                              NONUNIQUE

SQL> insert into t values (1);

1 row created.

SQL> /
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS_C0076677) violated

Ok this works fine, I didnt get your comment about deferrable? How does deferrable come into the picture here? The PK constraint doesnt <b>have</b> to be deferrable to use a non-unique index, does it?

Thanks 

I have a related question regarding unusable indexes:

I've built some bitmap indexes on a materialized view which is completely refreshed on a regular basis. Before refreshing the MV, I mark the indexes as unusable. The refresh ignores this information and rebuild's the indexes anyway. According to the Data Warehousing Guide, refreshing a materialized view will all of it's indexes. But it also states that you can mark indexes as unuable prior to a refresh. 

Example (version 9.2.0.1.0):

SQL> create materialized view test_mv
  2  build deferred
  3  using no index
  4  refresh force on demand
  5  as select * from emp;

Materialized view created.

SQL> create bitmap index test_mv_bx on test_mv(job)
  2  nologging nocompress;

Index created.

SQL> alter index test_mv_bx unusable;

Index altered.

SQL> select status from user_indexes where index_name='TEST_MV_BX';

STATUS
--------
UNUSABLE

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> exec dbms_mview.refresh('test_mv','c')

PL/SQL procedure successfully completed.

SQL> select status from user_indexes where index_name='TEST_MV_BX';

STATUS
--------
VALID

The index is rebuilt during the refresh, even though it is UNUSABLE.
Is this expected behavior? Is there any way to supress rebuilding the indexes until after the refresh? While this example does not show it (due to the small amount of data), rebuilding the bitmap index during the refresh creates a much larger index than if it is rebuilt after the refresh is complete. It also causes the refresh to take a longer amount of time.

I realize that one option is to drop the indexes before the refresh and then recreate them, but I would prefer not having to do this (keeping the index definitions in the data dictionary and simply rebuilding those that are unusable seems much more elegant).

Thank you in advance for your input. 

In 9i Release 2 
when I do
SQL> 
SQL> 
SQL> show parameter skip_unusable_indexes ;
SQL> alter session set skip_unusable_indexes=true;

Session altered.

Why does show parameter dont work? it shows nothing in this case.

How can we see the current value of session only parameters