Re: SORT ORDER BY elimination (earth-speak version)
- From: "Peter Welker" <peter.welker@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 22 Oct 2004 20:25:10 +0200 (CEST)
Hello Edgar,
you might (or better might not) want to try the NL_SJ hint inside each
subquery. This works, but is deprecated in 10g and it's slow (for your
example) since the subqueries are scanned for each sales record picked
from SALE_PK and there is no index for the subqueries.
Not sure if this matches you real-life problem behind the example, but it
looks a little like a reporting issue I got some time ago:
We required a flexible list of "dimensions" that are manually selected and
connected with large a "fact" table (only DWH speak - it actually was
OLTP). The query is not allowed to change or might become large (no
in-list) and the query results are too long for sorting, we used temporary
tables like this (converted into your example):
SQL> create global temporary table tmp_shops (SHOP_ID NUMBER NOT NULL) on
commit delete rows;
Table created.
SQL> create unique index i_tmp_shops on tmp_shops(shop_id);
Index created.
SQL>
SQL> create global temporary table tmp_prods (PRODUCT_ID NUMBER NOT NULL)
on commit delete rows;
Table created.
SQL> create unique index i_tmp_prods on tmp_prods(product_id);
Index created.
SQL>
SQL> insert into tmp_shops
2 select 40 shop_id from dual union all select 20 shop_id from dual
union all select 30 shop_id from dual;
3 rows created.
SQL>
SQL> insert into tmp_prods
2 select 140 product_id from dual union all select 40 product_id from
dual union all select 70 product_id from dual
union all select 130 product_id from dual;
4 rows created.
now try this (correlated subquery with unique index):
SQL> set autotrace on explain
SQL> select time, shop_id, product_id
2 from sales sal
3 where time between trunc(sysdate) and sysdate
4 and shop_id in ( select * from tmp_shops s where sal.shop_id =
s.shop_id)
5 and product_id in ( select * from tmp_prods p where sal.product_id
= p.product_id)
6 order by time, shop_id, product_id;
TIME SHOP_ID PRODUCT_ID
--------- ---------- ----------
22-OCT-04 40 140
22-OCT-04 30 130
22-OCT-04 20 70
22-OCT-04 40 40
22-OCT-04 40 140
22-OCT-04 30 130
22-OCT-04 20 70
22-OCT-04 40 40
22-OCT-04 40 140
22-OCT-04 30 130
22-OCT-04 20 70
22-OCT-04 40 40
22-OCT-04 40 140
22-OCT-04 30 130
22-OCT-04 20 70
22-OCT-04 40 40
22-OCT-04 40 140
22-OCT-04 30 130
22-OCT-04 20 70
22-OCT-04 40 40
20 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=372 Bytes=15252)
1 0 FILTER
2 1 NESTED LOOPS (Cost=4 Card=372 Bytes=15252)
3 2 NESTED LOOPS (Cost=3 Card=114 Bytes=3192)
4 3 INDEX (RANGE SCAN) OF 'SALES_PK' (UNIQUE) (Cost=3
Card=314 Bytes=4710)
5 3 INDEX (UNIQUE SCAN) OF 'I_TMP_PRODS' (UNIQUE)
6 2 INDEX (UNIQUE SCAN) OF 'I_TMP_SHOPS' (UNIQUE)
Of course there is an enormous overhead for filling the tmp-tables, so
this approach is only valid for situations where the subquery selections
manually takes place once for a query (or set of queries).
Regards & have a nice weekend
Peter
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: SORT ORDER BY elimination (earth-speak version)
