Re: 2 overlapping indexes

nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote in message news:<3c515eb4.4843184_at_news-vip.optusnet.com.au>...
> cj doodled thusly:
>
> >I have a table with column A, B, C, D and E. The data is unique on A,
> >B and C so I create a unique index on that.
>
> And that's it. No more columns needed!
>
> >Now I would also like to
> >create a non-unique index on A, B, C and D.
>
> wth4? If you use A,B,C in your predicates, you are already retrieving
> one single row (unique index). why add D? "to be sure-to be sure"?
>
>
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam

Since Oracle 8 it has been possible to support a PK constraint using a non-unique index therefore if you drop the PK on A,B,C and then re-add the PK constraint Oracle will use the existing non-unique index on A, B, C, and D to enforce the PK.

The only valid reason I know to create an index like this is when the underlying table is very large and all the data to be selected in a critical process consists of the indexed columns and the query is done on the leading column(s) only. Oracle can solve the query using an index range scan or a fast full index scan and never touch the table.

UT1> desc marktest

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 FLD1                                               VARCHAR2(10)
 FLD2                                               NUMBER
 FLD3                                               DATE
 FLD4                                               VARCHAR2(10)

UT1> create index marktest_idx1 on marktest(fld1, fld2); Index created.

UT1> alter table marktest add constraint marktest_pk primary key (fld1);
Table altered.

 1* select index_name, uniqueness
 from dba_indexes where table_name = 'MARKTEST' UT1> /

INDEX_NAME                     UNIQUENES
------------------------------ ---------
MARKTEST_IDX1                  NONUNIQUE

  1* insert into marktest values ('one',1,sysdate,'one') UT1> /

1 row created.

UT1> /
insert into marktest values ('one',1,sysdate,'one') *
ERROR at line 1:
ORA-00001: unique constraint (MPOWEL01.MARKTEST_PK) violated

HTH
-- Mark D Powell -- Received on Fri Jan 25 2002 - 14:24:14 CST