Re: Concatenated Index

I can see the explain plan fine and have verified that it's using the index as I want.

My question was will the 2nd and 3rd columns of the index be used or just the leading edge. The leading edge is the most restrictive but it has a like clause on it. I don't know if the like clause will stop the rest of the index from being used.

Thanks...

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CFD1AB2.4ECD8662_at_exesolutions.com...
> Steve Johnson wrote:
>
> > If I have a concatenated index on tablea columns (a, b, c) and a query:
> >
> > SELECT count(*)
> > FROM tablea,
> > tableb
> > WHERE tablea.a like 'AB%'
> > AND tablea.b=tableb.b
> > AND tablea.c=tableb.c
> >
> > Will columns b and c of the index on tablea be used in the query? It
uses
> > the index like I wanted but I don't get the speed I think I should which
> > leads me to believe that it doesn't.
> >
> > Any help would be appreciated.
> >
> > Thanks...
> > Steve
>
> EXPLAIN PLAN
> SET statement_id = 'abc'
> FOR
> SELECT count(*)
> FROM tablea,
> tableb
> WHERE tablea.a like 'AB%'
> AND tablea.b=tableb.b
> AND tablea.c=tableb.cl;
>
> Then ...
>
> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options ||' ' ||
> object_name || ' ' ||
> DECODE(id,0,'Cost = '||position) QUERY_OUTPUT
> FROM plan_table
> START WITH id = 0
> AND statement_id = 'abc'
> CONNECT BY PRIOR id = parent_id
> AND statement_id = 'abc';
>
> This will take less time than posting the question to usenet.
>
> Daniel Morgan
>
Received on Tue Jun 04 2002 - 15:15:40 CDT