Re: CBO and index usage

This is a compositie index. As soon as there is no predicate on the leading column, cust#, the index will not be used. You should find out, which criteria are used most often, and make sure the most used column is leading in the index. I'm not sure whether it will be useful create extra indexes. After all, the guideline has always been:
as soon as you select more than 10 percent of a table , you'd better use a full table scan,
it will cost less i/o. That said, in the current setup only two extra indexes, one on sales order and one on order date look useful.

Hth,

Sybrand Bakker, Oracle DBA

usmrall <srallapally_at_netscape.net> wrote in message news:7pn45b$4ho$1_at_nnrp1.deja.com...
> Hello,
> I have an index comprising of the following columns
> cust#
> sales order
> line item
> order date
> and the table has been analyzed . The adhoc query application
> can issue a query on any combination of these columns (and in any order)
> when i issue a like query i.e 'where sales order like '<some value>%''
> and run an explain plan on it, the results indicate a full table scan.
> why doesn't oracle use the index ? it already has an indexed set of
> values to choose from.
> Also, does this mean that i have to create an index for each combination
> of predicates ?
> please help
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Sun Aug 22 1999 - 00:05:05 CDT