Oracle Composite Index Performace

Question

My sample oracle query structure is this:

SELECT <LIST_OF_COLUMNS> FROM <TABLE_NAME>
WHERE COLUMN_01 = <SOMETHING> AND COLUMN_02 = <SOMETHING> AND COLUMN_03 = <SOMETHING>

The table has over 1 Million records. I have indexed COLUMN_01, COLUMN_02 and COLUMN_03 separately. The above query is working fine and provide results as expected.

If I make COLUMN_01, COLUMN_02 and COLUMN_03 (all columns in WHERE clause) as composite index without changing existing indexes, will it improve performance? If so, is there an order for composite index columns?

If I use OR instead of AND like this query, will it improve performance?

SELECT <LIST_OF_COLUMNS> FROM <TABLE_NAME>
WHERE COLUMN_01 = <SOMETHING> OR COLUMN_02 = <SOMETHING> OR COLUMN_03 = <SOMETHING>

Answer 1

If I make COLUMN_01, COLUMN_02 and COLUMN_03 (all columns in WHERE clause) as composite index without changing existing indexes, will it improve performance?

Probably. One index which satisfies all WHERE criteria serves as a complete access path and hence is more effective than a single column index access path. The optimizer chooses one index, so it will index read all the rows matching (say) COLUMN_02 criterion and filter those rows using the other columns' criteria.

The price you pay for this improvement in performance is the overhead of maintaining an additional index. So you should consider whether you need all three single column indexes (for other queries).

is there an order for composite index columns?

Yes. Put them in ascending order of distinct values. The leading index column should be the least discriminating column. Having a unique key as the leading column is probably a disaster, although there are edge cases, so be sure to benchmark.

If I use OR instead of AND like this query, will it improve performance?

You're going to be returning more rows, which in itself is more work. It is also hard to use indexes in such a situation, so most likely you're facing a Full Table Scan. But why not try it and see what happens?

Answer 2

1. If you create a composite index based on the three columns AND you never query the table using only one column in the WHERE clause, you won't need the single column indexes. Otherwise, it depends on what columns participate in a query. And this case should be well analyzed and tested.

2. Columns order in a composite index does matter. Columns should be ordered by uniqueness where the least distinct column goes first. It helps trim down the number of rows matched the query predicate and thus speed up performance.
   It also should be noticed that Oracle can use a composite index with queries that do not contain all the index columns in their predicates.
   

For example:

create index idx1 on table_name (col1, col2, col3);

/*In this query Oracle can use index idx1 as a standard one-column index, 
  because col1 is the first column in the index*/
select * from table_name where col1 = 'some_value';

/*Here Oracle can still use the composite index, 
  but in this case it will use INDEX SKIP SCAN (assuming col1 equals ANY value),
  which reduces query performance comparing to an ordinary index*/
select * from table_name where col2 = 'some_value1' and col3 = 'some_value2';

3. OR or AND operators do not really matter here. What is more important is the number of rows which match the given predicate.

Answer 3

If I make COLUMN_01, COLUMN_02 and COLUMN_03 (all columns in WHERE clause) as composite index without changing existing indexes, will it improve performance?

For this query: likely. For INSERT/UPDATE/DELETE: the performance will deteriorate.

So you'll need to measure and see whether improvement in some queries justifies the deterioration in others.

If so, is there an order for composite index columns?

Not for this query. If you prefix-compress the index, you may choose the order that compresses the best, otherwise it shouldn't matter much.

However, there may be other queries that use only some of the indexed columns, in which case you'd want to make sure the columns that are actually used are at the leading edge of the index.

If I use OR instead of AND like this query, will it improve performance?

No. Separate indexes (that you already have) are what is needed in this case.