Re: Access and Filter Predicate on same execution plan line

Amit,
Imagine you have an index (n5, n3000) on a table where n5 has 5 distinct values and n3000 has 3000 distinct values. If you walk through the index leaf blocks in order you might find index entries as follows: (n5, n3000, rowd)

         0          2 AAATgSAANAAAAESAAs
         0          3 AAATgSAANAAAADhAA0
..
         0       2999 AAATgSAANAAAADhAAv
         0       3000 AAATgSAANAAAACyAA3
         1          0 AAATgSAANAAAACyAA4
         1          1 AAATgSAANAAAACEAAA
..
         1       2999 AAATgSAANAAAAESAAo
         1       3000 AAATgSAANAAAADhAAw
         2          0 AAATgSAANAAAADhAAx
         2          1 AAATgSAANAAAACyAA5
..
         2       2997 AAATgSAANAAAACyAA0
         2       3000 AAATgSAANAAAAESAAp
         3          0 AAATgSAANAAAAESAAq
         3          1 AAATgSAANAAAADhAAy
..
         3       2997 AAATgSAANAAAADhAAt
         3       2998 AAATgSAANAAAACyAA1
         4          1 AAATgSAANAAAAESAAr
         4          2 AAATgSAANAAAADhAAz
..
         4       2998 AAATgSAANAAAADhAAu
         4       2999 AAATgSAANAAAACyAA2

The ... represent hundreds of consecutive index entries.

All that the bold text is saying is that (in effect) Oracle treats this index as if it were 5 separate indexes, and does 5 consecutive index ranges scans - one for the "0" index section, one for the "1" index section, and so on.

In practice, of course, that's not quite the way it works because Oracle can't be certain in advance that there really are 5 "subindexes", and it doesn't know where the '0' subindex ends and the '1' subindex begins, so it has to follow an algorithm to find "the nextchangeover" leaf block.

Regards
Jonathan Lewis

On Tue, 17 Aug 2021 at 21:06, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Thank you for your inputs. I read the Oracle note but *"**In this case a
> composite index is split logically into smaller subindexes. The number of
> logical subindexes depends on the cardinality of the initial column." *couldn't
> understand this bold line.
>
> Best Regards,
> AMIT SAROHA
>
>
>

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