Re: optimizer AND_EQUAL problem

Thanks, bur this does not speed up query. Because of it is steel using 1 single index for each table c1 and c2.

Thanks
Leon

Hans-Peter Sloot wrote:

> Would
>
> select
> count(*)
> from CUSTOMER c1, CUSTOMER c2
> where c1.LASTNAME like 'J%'
> and c2.FIRSTNAME like 'M%'
> and c1.primary_key||'x' = c2.primary_key||'x'
>
> be an option?
>
> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in article
> <917777373.10462.0.nnrp-09.9e984b29_at_news.demon.co.uk>...
> >
> > You might investigate Oracle's option for converting
> > btree to bitmap on the fly.
> >
> > init.oa parameter something like
> > v_btree_bitmap_conversion = true
> > on version 7.3
> >
> > This allows Oracle to generate a plan like:
> >
> > access table by rowid
> > convert bitmap to rowid
> > BITMAP AND
> > concatenate bitmaps (allowing for your
> 'like'
> > convert btree index 1 rowids to bitmap
> > concatenate bitmaps
> > convert btree index 2 rowids to bitmap
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: www.jlcomp.demon.co.uk
> >
> >
> >
> > Leon Rzhemovskiy wrote in message <36B390FE.BF185F41_at_home.com>...
> > >Thanks
> > >I am agree with you. If I put "=" everything OK.
> > >
> > >
> > >I have tried to start database in a CHOSE and RULE Mode.
> > >I have tried to run query with existing statistics for customer table
> > >and without.
> > >Does not help.
> > >
> > >I hope it is exists way to use 2 indexes for query like "like 'XXX%'"
> > >With using 1 index it is painfully slow.
> > >Need help.
> > >
> > >Thanks
> > >Leon
> > >
> > >"John P. Higgins" wrote:
> > >
> > >> I believe the AND-EQUAL method can only be used when you ask for an
> > >> exact match on each of the keys.
> > >>
> > >> Leon Rzhemovskiy wrote:
> > >>
> > >> > I have table "customer". (200 000 MB)
> > >> > 2 single indexes (last_name and first_name)
> > >> >
> > >> > Select statement:
> > >> >
> > >> > select /*+ AND_EQUAL (customer cust_last cust_first) */
> > >> > count(*)
> > >> > from CUSTOMER
> > >> > where LASTNAME like 'J%'
> > >> > and FIRSTNAME like 'M%'
> > >> >
> > >> > Result:
> > >> >
> > >> > Explain statement
> > >> > - select statement
> > >> > - sort aggregate
> > >> > - table access by index rowid customer
> > >> > Index range scan cust_first
> > >> >
> > >> > HOW to MAKE IT LIKE:
> > >> >
> > >> > - select statement
> > >> > - sort aggregate
> > >> > - and-equal
> > >> > index range scan cust_first
> > >> > index range scan cust_last
> > >> >
> > >> > Thanks
> > >> > Leon
> > >> > lrzhemov_at_home.com
> > >
> >
> >
> >
Received on Wed Feb 03 1999 - 22:16:12 CST