Re: Concatenated Index

Steve,

If the 2nd and 3rd column in the index aren't used, then an additional table access TABLE ACCESS (BY INDEX ROWID) OF TABLEA
will be needed to retrieve the values of the 2nd and 3rd column. So, to be sure all columns are used, check your execution plan. Make sure your tables and indexes are analyzed. Try calculating column statistics on column a.

"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
> > >
> > >
> >
> >
>
>
Received on Tue Jun 04 2002 - 15:45:29 CDT