Analytical stuff: window sort operation

From: Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is>
Date: Tue, 13 Feb 2007 16:33:36 -0000
Message-ID: <5A8896FB2AFC5445A7DCFC5903CCA6B06FECC0@W03856.li01r1d.lais.net>

Given operation

| 1 | WINDOW SORT | | 10000 | 136K| 8 (50)| 00:00:01 |

Is it possible to make Oracle use index for such a sort? Is it actually using it? Block reads statistics sugest it does.

The analytical sql is like this:

first_value (d.deal_id) over ( partition by d.order_id

    order by d.deal_date , d.deal_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) min_deal_id

If I would code such a thing then it would be equivalent to

[
Cursor cr is
select d.order_id, d.deal_id from
testa d
where d.order_id=:b1
order by d.deal_date , d.deal_id ;   

Fetch cr into max_deal
]

It's no issue to have an indexed access for such a cursor.

---

---

The test case is like that:

create table testa as
select rownum id, mod(rownum, 100) order_id, (sysdate-mod(rownum, 100)-mod(rownum, 99)/24/60/60) deal_date, mod(rownum, 97) deal_id from all_objects where rownum<=10000;

--drop index testa_sort_ord_date ;

create index testa_sort_ord_date on testa (order_id, deal_date , deal_id );

REM gather stats...

explain plan for
select sum(min_deal_id) from (
select d.deal_id, d.order_id, d.deal_date , first_value (d.deal_id) over ( partition by d.order_id

    order by d.deal_date , d.deal_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) min_deal_id

                        from testa d

);

select * from table(dbms_xplan.display());

select d.order_id, d.deal_id from
testa d
where d.order_id=0
order by d.deal_date , d.deal_id ;   

Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 13 2007 - 10:33:36 CST