Re: SORT ORDER BY elimination
- From: Edgar Chupit <chupit@xxxxxxxxx>
- To: "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Oct 2004 21:28:05 +0300
Decoded message text from Peter Welker follows:
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
On Fri, 22 Oct 2004 20:08:22 +0200, Peter Welker
<peter.welker@xxxxxxxxxxxx> wrote:
> SGVsbG8gRWRnYXIsDQogDQp5b3UgbWlnaHQgKG9yIGJldHRlciBtaWdodCBub3QpIHdhbnQgdG8g
> dHJ5IHRoZSBOTF9TSiBoaW50IGluc2lkZSBlYWNoIHN1YnF1ZXJ5LiBUaGlzIHdvcmtzLCBidXQg
>
---
Edgar
--
//www.freelists.org/webpage/oracle-l
Other related posts:
