Re: optimizer AND_EQUAL problem

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 Mon Feb 01 1999 - 07:11:03 CST