Re: Concatenated Index

"Steve Johnson" <johnst_at_ncs.com> wrote in message news:ufq8ipkavuqn63_at_corp.supernews.com...
> I don't think I asked the question very good.
>
> 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...
>
> "Richard Spee" <rhpspee_at_wxs.nl> wrote in message
> news:adj7dc$i39$1_at_reader05.wxs.nl...
> > log into sqlplus
> > Run the script ora8i/rdbms/admin/utlxplan.sql
> > (creates the plan_table)
> > set autotrace on
> > run your query and .....
> > start tuning
> >
> > "Steve Johnson" <johnst_at_ncs.com> wrote in message
> news:ufq5u6am03kmcb_at_corp.supernews.com...
> > > 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
> > >
> > >
> >
> >
>
>

The 2nd and 3rd columns will NOT be used. You can verify this by looking at the cardinality for each step provided with
set autotrace on explain stat
The cardinality will match the cardinality of the leading column.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address