Re: Access and Filter Predicate on same execution plan line

I'm not sure your description is exactly what you intended, but if I base a description on a speciic case in the Markus Windan artticle would your assertion be:

*If you have a two-column index created to address inequality predicates of the form "colA > {constant1} and colB > {constant2}" then the column with the larger number of distinct values should be the first column in the index. (Or other range-based predicates, and assuming you have a good argument for not creating two separate indexes).*

Regards
Jonathan Lewis

On Thu, 19 Aug 2021 at 17:16, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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
>
>
>

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