Tom - I figure this will be an easy one for you. Its not really a question, but rather a lesson learned. I encountered a situation that I thought was a discrepancy, but it turned out to be a documented behavior. I'm posting this in case anyone else encounters a similar circumstance.

This is regarding the implicit creation of indexes to support unique key constraints. I learned to be explicit in the definition of the indexes if the application is dependent on them. Relying on implicit index creation for these constraints doesn't necessarily produce the desired outcome.

I have a table with a composite primary key of two columns.

create table mytest (
        c1 integer,
        c2 integer,
        constraint mytest_pk primary key (c1, c2),
        constraint mytest_uk unique (c2, c1)
);

I need to query this table in the following manner:

1) select * from mytest where c1 = :x and c2 between :y and :z;
2) select * from mytest where c2 = :x and c1 between :y and :z;

So, I defined both the primary key and unique key constraints to index c1 and c2 both ways - or so I thought.

I used the following view in this test scenario to peek at the indexes on
table mytest and their structure:

column index_name format a20
column column_name format a20
break on index_name skip 1
create or replace view mytest_indexes as
      select substr(idx.index_name,1,20) index_name,
             substr(col.column_name,1,20) column_name,
             idx.status
      from user_ind_columns col,
           user_indexes idx
      where idx.table_name='MYTEST'
      and   col.table_name=idx.table_name
      and   col.index_name = idx.index_name
      order by idx.index_name,
               col.column_position;

select * from mytest_indexes;

INDEX_NAME           COLUMN_NAME          STATUS
-------------------- -------------------- --------
MYTEST_PK            C1                   VALID
                     C2                   VALID

--
-- I expected to see two indexes as a result:
--    mytest_pk and mytest_uk.
--
-- But, NO. Only mytest_pk exists.
--
-- So I tried changing their definition order.
--
drop table mytest;

create table mytest (
        c1 integer,
        c2 integer,
        -- reversed order of PK and UK definition
        constraint mytest_uk unique (c2, c1),
        constraint mytest_pk primary key (c1, c2)
);

select * from mytest_indexes;

INDEX_NAME           COLUMN_NAME          STATUS
-------------------- -------------------- --------
MYTEST_PK            C1                   VALID
                     C2                   VALID

--
-- No difference. Still no index on the unique constraint.
--
-- So I tried forcing the order with separate alter table statements.
--
drop table mytest;

create table mytest (
        c1 integer,
        c2 integer
);

alter table mytest add (constraint mytest_uk unique (c2, c1));
alter table mytest add (constraint mytest_pk primary key (c1, c2));

select * from mytest_indexes;

INDEX_NAME           COLUMN_NAME          STATUS
-------------------- -------------------- --------
MYTEST_UK            C2                   VALID
                     C1                   VALID

--
-- Now an index exists for UK but not for PK.
--
-- So, I tried a brute-force approach.
--
drop table mytest;

create table mytest (
        c1 integer,
        c2 integer
);


alter table mytest add (constraint mytest_uk unique (c2, c1) using index);
alter table mytest add (constraint mytest_pk primary key (c1, c2) using index);

select * from mytest_indexes;

INDEX_NAME           COLUMN_NAME          STATUS
-------------------- -------------------- --------
MYTEST_UK            C2                   VALID
                     C1                   VALID

--
-- Still no good.
--
-- So, I tried explicit 'create index' commands.
--
drop table mytest;

create table mytest (
        c1 integer,
        c2 integer
);

alter table mytest add (
        constraint mytest_uk unique (c2, c1)
            using index (create unique index mytest_uk on mytest(c2, c1))
);

alter table mytest add (
        constraint mytest_pk primary key (c1, c2)
            using index (create unique index mytest_pk on mytest(c1, c2))
);


select * from mytest_indexes;

INDEX_NAME           COLUMN_NAME          STATUS
-------------------- -------------------- --------
MYTEST_PK            C1                   VALID
                     C2                   VALID

MYTEST_UK            C2                   VALID
                     C1                   VALID

--
-- Voila- Success!
--
-- Now, combining all of this into one DDL statement..
--
drop table mytest;

create table mytest (
        c1 integer,
        c2 integer,
        constraint mytest_uk unique (c2, c1)
           using index (create unique index mytest_uk on mytest(c2, c1)),
        constraint mytest_pk primary key (c1, c2)
           using index (create unique index mytest_pk on mytest(c1, c2))
);

--
-- LESSON LEARNED - Don't rely on implicit index creation by a 'create table'
--     command if those indexes are necessary for selectivity.
--     Be explicit!
--
drop table mytest;
drop view mytest_indexes;