Re: Access and Filter Predicate on same execution plan line

Okay - index_join or index_combine. Your reference to 7.3 suggested and_equal, (or emulating it since it was only available for single column indexes).

Even so, your two-column index with most selective first isn't a case of "most selective first"; it still comes down to the best compromise to reduce the number of indexes while satisfying an important class of query in the least worst way which, in your case, happened to put the most selective column first.

Here's an interesting thought, though - I'll have to add it to https://jonathanlewis.wordpress.com/2013/01/03/skip-scan-2/

select * from t1
where col1 > {constant1}
and col2 > {constant2}
;

can execute using an index skip scan using an index (col1, col2)

I haven't tested this back to a real 11.2.0.1 where it might not work - so far I've only tested on 19.11.0.0, but the blog note highlights the change at 11.2.0.2

Regards
Jonathan Lewis

On Wed, 18 Aug 2021 at 23:19, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> I am referring to making an intersection of two indexes, like this:
>
> https://logicalread.com/oracle-11g-index-merge-mc02/
>
>
> https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-merge-performance
>
> One index is still faster than two. The problem is that one index somewhat
> restricts the use.
>
> Regards
> On 8/18/21 5:46 PM, Jonathan Lewis wrote:
>
>
> What operation (or set of operations) are you calling an "index merge"?
>
>
> Regards
> Jonathan Lewis
>
>
>
>
> On Wed, 18 Aug 2021 at 19:40, Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>> Well, the reason for that was to avoid index merge, which was rather
>> expensive at that time. People were trying to kill two birds with one
>> stone or solve two problems with a single index. I am not sure when did
>> index merge get cheaper, I think it was in version 7, but I may be
>> wrong. For the record, I don't kill birds. I let my cat do that. I named
>> my cat "Tiger", after Bruce Scott's cat and he lives up to the name.
>>
>> Regards
>>
>> On 8/18/21 7:46 AM, Jonathan Lewis wrote:
>> >
>> > It' s not an unwritten rule, it's a frequently written piece of
>> ignorance.
>> > It was very close to true for Oracle version 5. But very few people
>> > stopped to rethink their optinion when Oracle 6 came out./
>> >
>> > Regards
>> > Jonathan Lewis
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>> https://dbwhisperer.wordpress.com
>>
>> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

--
http://www.freelists.org/webpage/oracle-l