Re: SORT ORDER BY elimination (earth-speak version)

From: Peter Welker <peter.welker_at_trivadis.com>
Date: Fri, 22 Oct 2004 20:25:10 +0200 (CEST)
Message-ID: <41311.192.168.126.126.1098469510.squirrel@secure.trivadis.com>

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

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

Received on Fri Oct 22 2004 - 13:20:58 CDT