Re: optimizer AND_EQUAL problem

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 Sun Jan 31 1999 - 04:08:23 CST