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