9

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.

jyapx
  • 2,009
  • 2
  • 15
  • 18
user2824874
  • 199
  • 2
  • 5
  • 13
  • 1
    When you say `column2` and `column3` are foreign key, do you mean that the combination of `column2, column3` is a foreign key to a single parent table with a composite primary key? Or that each of `column2` and `column3` are foreign keys to separate parent tables with single-column primary keys? If `column1` is already the primary key, why are you creating a unique constraint on the combination of `column1` and `column2`. `column1` is already unique so the combination of it and anything else will be unique. Did you mean that you're creating a unique constraint on `col2, col3`? – Justin Cave Oct 25 '13 at 21:48
  • I am sorry, typo error. I am creating unique constraint on (col2, col3). – user2824874 Oct 28 '13 at 14:51

2 Answers2

11

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
-1

Indexes are structures that improve performance on retrieval of information, that is whenever you use a SELECT clause, as it will look for the key of the index and return the value or values linked to that key. However indexes will increase the cost of update and insert operations as it needs to update the structure to keep it efficient for retrieval of information.

Creating indexes for single columns will not improve your read performance, however it will drop your write and update performance AFAIK.

Cristobal De Leon
  • 89
  • 9
  • 1
    "Creating indexes for single columns will not improve your read performance" may apply to some cases, but is not true in general and might not be true for the situation in the question (e.g., if there are frequent queries filtering by column 2 alone). – divieira Aug 27 '21 at 19:59