Avoiding sorting for min/max aggregate functions
- From: Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Oct 2007 16:14:23 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Avoiding sorting for min/max aggregate functions
