SORT ORDER BY elimination

Dear List,

I'am researching different types of SORT ORDER BY step elimination.
This particular problem bothers me for a few days, but I still can't
find a correct way to eliminate SORT ORDER BY step.

Suppose I have a large table that is always queried by primary key and
I want to receive results in PK orders first query does exactly the
same as second query, but first query in my test does 7 LIOs, but
second 14 LIOs + sort. 

Is there a way to eliminate SORT ORDER BY step from the second query?
Thanks in advance.

/* QUERY #1 */

select time, shop_id, product_id
from sales
where time between trunc(sysdate) and sysdate
 and shop_id in (40,20,30)
 and product_id in (140,40,70,130)
order by time, shop_id, product_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          7          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          7          0          17

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 63

Rows     Row Source Operation
-------  ---------------------------------------------------
   17  FILTER  (cr=7 pr=0 pw=0 time=258 us)
   17   INDEX RANGE SCAN SALES_PK (cr=7 pr=0 pw=0 time=196
us)(object id 227313)

/* QUERY #2 */

with q1 as ( select 40 from dual union all select 20 from dual union
all select 30 from dual ),
   q2 as ( select 140 from dual union all select 40 from dual union
all select 70 from dual union all select 130 from dual )
select time, shop_id, product_id
from sales
where time between trunc(sysdate) and sysdate
 and shop_id in ( select * from q1 )
 and product_id in ( select * from q2 )
order by time, shop_id, product_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0         14          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.03          0         14          0          17

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 63

Rows     Row Source Operation
-------  ---------------------------------------------------
   17  SORT ORDER BY (cr=14 pr=0 pw=0 time=9220 us)
   17   HASH JOIN SEMI (cr=14 pr=0 pw=0 time=8779 us)
   39    NESTED LOOPS  (cr=14 pr=0 pw=0 time=2825 us)
    3     VIEW  (cr=0 pr=0 pw=0 time=634 us)
    3      SORT UNIQUE (cr=0 pr=0 pw=0 time=608 us)
    3       VIEW  (cr=0 pr=0 pw=0 time=99 us)
    3        UNION-ALL  (cr=0 pr=0 pw=0 time=83 us)
    1         FAST DUAL  (cr=0 pr=0 pw=0 time=9 us)
    1         FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)
    1         FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)
   39     INDEX RANGE SCAN OBJ#(227313) (cr=14 pr=0 pw=0 time=1897
us)(object id 227313)
    4    VIEW  (cr=0 pr=0 pw=0 time=194 us)
    4     VIEW  (cr=0 pr=0 pw=0 time=179 us)
    4      UNION-ALL  (cr=0 pr=0 pw=0 time=163 us)
    1       FILTER  (cr=0 pr=0 pw=0 time=33 us)
    1        FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)
    1       FILTER  (cr=0 pr=0 pw=0 time=23 us)
    1        FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)
    1       FILTER  (cr=0 pr=0 pw=0 time=21 us)
    1        FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)
    1       FILTER  (cr=0 pr=0 pw=0 time=19 us)
    1        FAST DUAL  (cr=0 pr=0 pw=0 time=4 us)

Here is my test case:

/*
drop table sales;
*/
create table sales( time date not null, product_id number not null,
shop_id number not null, sales number(*,3) not null,
                  constraint sales_pk primary key (time,shop_id,product_id)
);

insert into sales
select sysdate-(1/24/6)*(rownum/7), mod(rownum, 150), mod(rownum, 50),
trunc(dbms_random.value*10000,3)
from all_objects
/

insert into sales
select time-100,product_id,shop_id,sales from sales
/

begin
dbms_stats.gather_table_stats(ownname => ora_login_user, tabname =>
'SALES', estimate_percent => 100, method_opt => 'FOR ALL INDEXED
COLUMNS SIZE AUTO', cascade => TRUE );
end;
/

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

Other related posts: