Re: Analytical stuff: window sort operation

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Tue, 13 Feb 2007 11:30:51 -0600
Message-ID: <45D1F5CB.9080209@jcpenney.com>

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
/

| 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
> --
> http://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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 13 2007 - 11:30:51 CST