Re: Access and Filter Predicate on same execution plan line

Actually, I beg to differ. As everything in Oracle, it depends on how do you plan to use the multi-column index. If you plan on using the index for the inequality predicates on one column, than that column should better come first.

On 8/19/21 6:37 AM, Jonathan Lewis wrote:
>
> 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/
> <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
>
>
>
>
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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