Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SORT ORDER BY elimination

SORT ORDER BY elimination

From: Edgar Chupit <chupit_at_gmail.com>
Date: Fri, 22 Oct 2004 16:37:57 +0300
Message-ID: <a8f0771c0410220637238462bd@mail.gmail.com>


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
Received on Fri Oct 22 2004 - 08:33:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US