Re: optimizer AND_EQUAL problem

Hi,
Thanks, you have solved my problem.

I have used

Init ora parameter:
b_tree_bitmap_plans = true

it is produced:

• select statement
• sort aggregate
• bitmap conversion count
• bitmap and
• bitmap conversion from rowids
• sort order by index range scan cust_first
• bitmap conversion from rowids
• sort order by index range scan cust_last

for query like: lastname like 'MO%'
and firstname like 'M%'
2 times faster then before 30 sec
for query like lastname like 'MONSTON%' and firstname like 'M%'
2 times slower but steel good (less then 1 sec).

Jonathan Lewis wrote:

> 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:13:36 CST