Oracle: single index with multiple columns or two indexes with one column

Question

I have a table

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

column1 is the primary key column2 and column3 is the column3 key

I created a unique 2 column constraint

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

when i went to create an index for 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 the index when I created the unique constraint. But if I create the index separately, it takes those

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

Now my question is: after having a unique restriction on both columns, do I still need to worry about creating an index for each column? Will skipping indexes from a single column affect performance when querying a table?

This is on the oracle 11R2.

Answer 1

It depends...

It is highly unlikely that only column1 will be useful if you already have a composite index on column1, column2 . Since column1 is the leading index, table queries that only have column1 as the predicate can use the composite index. If you often run queries that require a full index scan, and having column2 significantly increases the size of the index, it is possible that only column1 will be more efficient, since a full index scan will require less I / O. But this is a rather unusual situation.

An index of only column2 can be useful if some of your table queries point predicates to column2 only. If there are relatively slightly different values ​​for column1 , it is possible that Oracle can scan the index using a composite index to satisfy queries that specify only column2 as a predicate. But an error scan is likely to be much less efficient than a range scan, so it is likely that an index only for column2 will benefit these queries. If column1 has a large number of different values, skipping skips will be even less efficient, and column2 only column2 will be more useful. Of course, if you never query a table using column2 without specifying a predicate on column1 , you don't need an index only for column2 .

Answer 2

Indexes are structures that improve performance when retrieving information, that is, whenever you use a SELECT clause, because it will look for the index key and return the value or values ​​associated with that key. However, indexes will increase the cost of update and insert operations, because you need to update the structure so that it is efficient for retrieving information.

Creating indexes for individual columns will not improve read performance, but it will decrease the write and update performance of AFAIK.