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
--
http://www.freelists.org/webpage/oracle-l 


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

Other related posts: