I think this is just a short fall in the implementation.
In the general case func(A) = x is not reversible to one solution, ie many
values of A can produce X. This means that with an index (func(A), Y) just
filtering on A isn’t good enough to say that the rest of the values will be
sorted by Y, they will first be sorted by X.
With a descending index, the optimizer is able to swap out your filter to be
Sys_op_descend(indexed_col) = sys_op_descend(searched value)
But it misses this information when it decides what can be sorted
(presumably so that you can still order by indexed_col desc and it like
that).
If you change your order by to include the descending column (and it can be
descending or ascending in your query) you’d get the nosort.
Thanks,
Andrew
On Mon, 6 Jan 2020 at 05:47, Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
wrote:
Came across an interesting issue today.
the situation is as follows:
create table randomload(roll number, name varchar2(20), mark1 number);
roll is the primary key.
create index mark1_desc_roll_idx on randomload(mark1 desc, roll);
populate data uniformly for mark1, gather statistics
the following statement regardless of what it uses sorting. (sort order by)
select roll from randomload where mark1=11999 order by roll ;
even if we create the index specifically which will not make any sense,
but just in case.
create index mark1_desc_roll_asc_idx on randomload(mark1 desc, roll asc);
select roll from randomload where mark1=11999 order by roll;
we know that the roll column in the index is already sorted in the
ascending order, but still why does oracle perform sorting?
I was wondering whether function based index had to do with any of it.
created the index as follows:
create index mark1_roll_idx on randomload(mark1-1, roll);
gathered statistics, then running the query.
select roll from randomload where mark1=11200 -1 order by roll;
here in this case, sorting is not performed, clearly nothing wrong with
the function based index and how the data is organized in the indexes, but
looks like an issue with the optimizer generating the plan?
all works perfectly fine when the trailing column of the index uses desc
instead of leading column...
Thanks,
Vishnu
