Re: Analytical stuff: window sort operation
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: Laimutis.Nedzinskas@xxxxxxxxxxxxx
- Date: Tue, 13 Feb 2007 11:30:51 -0600
Hi Laimutis
In your analytical SQL, no predicates are specified. If you specify
order_id as a predicate, then that predicate can be pushed and access
plan with index usage might be cheaper, leading to CBO choosing index
based access:[ Then again, there are various minor things as to when
predicates can be pushed to an analytical SQL. ] In this SQL, it will be
pushed.
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
)
where order_id =1
/
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 |
2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 15
| | |
| 2 | VIEW | | 100 | 1500 |
2 (0)| 00:00:01 |
| 3 | WINDOW BUFFER | | 100 | 1400 |
2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| TESTA_SORT_ORD_DATE | 100 | 1400 |
2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."ORDER_ID"=1)
filter("D"."ORDER_ID"=1)
Thanks
Riyaj Shamsudeen
Laimutis Nedzinskas wrote:
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
--
//www.freelists.org/webpage/oracle-l
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete the material from any
computer.
Other related posts:
