Re: Indices

In article <055c9c20.bcb6cb5e_at_usw-ex0105-035.remarq.com>,   craibuc <craibucNOcrSPAM_at_hotmail.com.invalid> wrote:
> If I have a multi-column index on a table, and I only use a
> subset of the index's columns in a query, will the index be
> used? What are the rules?
>

I covered this in my reply to your table question post, but Oracle can generally use an index only when the leading column or columns are referenced in the statement where clause. In other words if you have a four column index, the index can be used if you reference the first column defined in the index, the first two columns, first three columns, or all four columns. The index will normally not be used if you reference the second and third columns but not the first in the where clause.

I use the terms, generally and normally, above because certain conditions such as the presence of the group by clause affects Oracle's ability to use indexes. In the where clause a function on an indexed column will normally disable the use of the index on that column. With ver 8.1 it is possible to create function indexes which breaks this rule. If every column being selected is in one index then Oracle can sometimes read the index and not access the table to solve the query. These rules or exceptions to the general rules require some reading or serious tuning experience to learn.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.