Sql – Oracle: Single multicolumn index or two single column indexes

indexingoracle11gperformancesql

I have table 

create table1(
  column1 number(10,
  column2 number(10),
  column3 number(10)
);

column1 is Primary Key
column2 and column3 is Foreign key

I have created unique constraint on 2 columns

alter table table1 
        add constraint table1_contr1 unique(column1,column2) 
      using index tablespace tbs1;

when I went to create index on both columns as

create index table1_idx1 on table1(column1,coulmn2);

ERROR at line 1:
ORA-01408: such column list already indexed

So Oracle already created index when I create unique constraint. But if I create index separately it is accepting those

create index table1_idx1 on table1(column1);
create index table2_idx2 on table2(column2);

Now my question is, after having unique constraint on both columns do I still need to worry about creating an index on each column? Will omitting the single column indexes have an impact on performance while querying the table?

It's on oracle 11R2.

Best Solution

It depends...

It is quite unlikely that an index on just column1 will be beneficial if you already have a composite index on column1, column2. Since column1 is the leading index, queries against the table that have only column1 as a predicate will be able to use the composite index. If you are frequently running queries that need to do a full scan of the index and the presence of column2 substantially increases the size of the index, it is possible that an index on just column1 would be more efficient since the full index scan would need to do less I/O. But that is a pretty unusual situation.

An index on just column2 may be beneficial if some of your queries against the table specify predicates on just column2. If there are relatively few distinct values of column1, it is possible that Oracle could do an index skip scan using the composite index to satisfy queries that only specify column2 as a predicate. But a skip scan is likely to be much less efficient than a range scan so it is reasonably likely that an index on just column2 would benefit those queries. If there are a large number of distinct values for column1, the skip scan would be even less efficient and an index on just column2 would be more beneficial. Of course, if you never query the table using column2 without also specifying a predicate on column1, you wouldn't need an index on just column2.

Related Question