Re: SORT ORDER BY elimination

From: Edgar Chupit <chupit_at_gmail.com>
Date: Fri, 22 Oct 2004 21:28:05 +0300
Message-ID: <a8f0771c041022112812bc47f1@mail.gmail.com>

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_at_trivadis.com> wrote:
> SGVsbG8gRWRnYXIsDQogDQp5b3UgbWlnaHQgKG9yIGJldHRlciBtaWdodCBub3QpIHdhbnQgdG8g
> dHJ5IHRoZSBOTF9TSiBoaW50IGluc2lkZSBlYWNoIHN1YnF1ZXJ5LiBUaGlzIHdvcmtzLCBidXQg
>

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

Received on Fri Oct 22 2004 - 13:23:55 CDT