RE: SORT ORDER BY elimination
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Oct 2004 17:02:52 -0400
Check me if I'm wrong, but I *think* that even if Oracle uses the index in
sorted order, if it *ALSO* uses a hash, then the old "it's already in order
from the index" shortcut is not possible and they have to sort. So I
*believe* it is needed not only to use the index but also use no hashes to
avoid the sort. I think that is part of the nature of hashes, right?
Probably that is what you already meant and my understanding of what you
wrote was suboptimal.
A client of mine recently noticed that some group by expressions that
formerly implicitly guaranteed a sorted result set could now be grouped very
nicely with a hash plan -- but that the result set, while correct in
content, was no longer sorted. (Off ran the developers to add sorts to all
the required reports. What I didn't check which I'm now fretting over in
wonder, is whether the addition of the order by simply caused the plan to
revert, or whether it now sorts the whole result set at the end.)
Regards,
Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Friday, October 22, 2004 4:07 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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
) v1,
etc.
sales
where
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
----- Original Message -----
From: "Edgar Chupit" <chupit@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, October 22, 2004 2:37 PM
Subject: 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
