| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Fri Oct 22 2004 - 08:33:57 CDT
 |
 |