composite index- Options

[michal.jablonski]

I have a table with (for example) column A, B. Table is often selected with clauses:

CODE

WHERE A=:1

and

CODE

WHERE A=:1 AND B=:2

. If I create composite index on coulmn A, B It will be used in both cases. If I create another index on column A, will first select could have less cost or cost will be the same?

[burleson]

Hi Michael,

>> If I create another index on column A, will first select could have less cost or cost will be the same?

Yes, but it's VERY SMALL and not worth the overhead of keeping a second index.

Good Question!

In general, multi-column indexes have less overhead, ONLY WHEN the SQL workload uses both columns. You can run queries to see how many columns are used:

http://www.remote-dba.net/t_index_types_frequency.htm

- When using a multi-column index, you want to put the most restrictive column value first (the column with the highest unique values) because this will trim-down the result set.

- Because Oracle can only access one index, your job is to examine your historical SQL workload and build a single composite index that satisfies the majority of the SQL queries.

- The Oracle optimizer may try to make single column indexes behave as-if they were a single composite index. Prior to 10g, this could be done with the "and_equal" hint.

Beware that indexes have overhead

>> If I create another index on column A, will first select could have less cost or cost will be the same?

Very slightly faster, but NOT worth the index overhead!

FYI, you can monitor the usage of columns within composite indexes:

http://www.rampant-books.com/t_oracle_coun...x_usage_sql.htm

If you want to learn more, I have a book on the subject here:

http://www.rampant-books.com/book_1001_oracle_sql_tuning.htm

Good Luck!