| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SORT ORDER BY elimination
You'll notice that the subqueries have been unnested, and one has caused a hash-semi join to appear - in part because Oracle cannot detect the uniqueness that we can see in the views - and has almost inevitably forced a sort to become necessary.
If you want to play around with different ways of writing the query to get the index back in play, try manually unnesting by writing the subqueries as in-line views with DISTINCT and joins (see Perf Tuning 101 by Gaja et. al.)
select
from
(select /*+ no_merge (optional) */ distinct id from (
select 120 id from dual
union all
select 130 from dual et.c
sales.shop_id = v1.id
etc.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th
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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 22 2004 - 15:02:45 CDT
 |
 |