Avoiding sorting for min/max aggregate functions


Is it possible to avoid sorting (SORT AGGREGATE) in the following case:

explain plan for
Select max(a) from t
where sign(b-500)=1 and sign(c-7)=1
/
select * from table(dbms_xplan.display());

All columns are not null and numbers. Functional index is created and analyzed.

The statement

explain plan for
Select * from (
    Select a from t
        where sign(b-500)=1 and sign(c-7)=1
    Order by
        sign(b-500),sign(c-7), a desc
) where rownum<=1
/
select * from table(dbms_xplan.display());

works fine (just an index scan(INDEX RANGE SCAN), no sorting operations)

---------------------------------------------

Test DDL:

create table
t
(a number not null, b number not null,  c number not null
);

insert into t  select mod(object_id+1245,1001), mod(object_id+4545,1111), 
mod(object_id+774545,13) from all_objects where rownum<=10000;
commit;

Create index TESTIDX on T
(sign(b-500)
,sign(c-7)
,a DESC)
compute statistics
/
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l

Other related posts: