Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Why is this simple query taking forever?

Why is this simple query taking forever?

From: <amerar_at_iwc.net>
Date: 28 Feb 2007 13:54:05 -0800
Message-ID: <1172699645.838438.214270@t69g2000cwt.googlegroups.com>


Hi,

Actually this query may not be so simple. But, in short, it is a query which has 2 inline functions. The inline functions themselves have inline functions. When each query is run by itself, it is rather fast. However, if you run the top level query, it takes over 45 minutes.

I've pasted the queries below, starting with the top level query, it's explain plan, and a timing of how long it took. If the lower level queries run quickly, qhy is the top level query not running quickly???

Any assistance would be helpful. Thanks in advance.

SELECT p.SUBPRODUCT_ID, ns.NEWSLETTER_NAME, ns.FREQUENCY, ns.FORMAT,

       last_subscr_code (1068733494, p.SUBPRODUCT_ID) last_code,
       last_subscr_status_tmp(1068733494, p.SUBPRODUCT_ID) last_type
FROM customer_order co, order_line ol, .product p, newsletter_subscription ns
WHERE co.ORDER_ID = ol.ORDER_ID
  AND ol.PRODUCT_ID = p.PRODUCT_ID
  AND p.CODE = ns.CODE
  AND co.CUSTOMER_ID = 1068733494
  AND ns.newsletter_id = 208
GROUP BY ns.NEWSLETTER_NAME, p.SUBPRODUCT_ID, ns.FREQUENCY, ns.FORMAT;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=1 Bytes=82)    1 0 SORT (GROUP BY) (Cost=84 Card=1 Bytes=82)

   2    1     NESTED LOOPS (Cost=57 Card=1 Bytes=82)
   3    2       NESTED LOOPS (Cost=48 Card=9 Bytes=351)
   4    3         NESTED LOOPS (Cost=39 Card=9 Bytes=198)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF

'CUSTOMER_ORDER' (Cost=12 Card=9 Bytes=108)
6 5 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON- UNIQUE) (Cost=3 Card=9) 7 4 INDEX (RANGE SCAN) OF 'ORDER_PRODUCT_IDX' (UNIQUE) (Cost=3 Card=431090 Bytes=4310900) 8 3 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=1 Card=29640 Bytes=503880) 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C005663' (UNIQUE) 10 2 TABLE ACCESS (BY INDEX ROWID) OF

'NEWSLETTER_SUBSCRIPTION' (Cost=1 Card=435 Bytes=18705)

LAST_SUBSCR_CODE


   SELECT ns.CODE

	 FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p,
	      newsletter_subscription ns
    WHERE co.ORDER_ID = ol.ORDER_ID
	  AND ol.PRODUCT_ID = p.PRODUCT_ID
	  AND p.CODE = ns.CODE
	  AND ns.NEWSLETTER_ID = 208
	  AND co.CUSTOMER_ID = 1068733494
	  AND co.ORDER_DATE = last_subscr_date(1068733494, 208);

CODE



864

Elapsed: 00:00:21.77

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=53)    1 0 NESTED LOOPS (Cost=17 Card=1 Bytes=53)

   2    1     NESTED LOOPS (Cost=16 Card=1 Bytes=45)
   3    2       NESTED LOOPS (Cost=15 Card=1 Bytes=30)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF

'CUSTOMER_ORDER' (Cost=12 Card=1 Bytes=20)
5 4 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON- UNIQUE) (Cost=3 Card=1) 6 3 INDEX (RANGE SCAN) OF 'ORDER_PRODUCT_IDX' (UNIQUE) (Cost=3 Card=431090 Bytes=4310900) 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=1 Card=29640 Bytes=444600) 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C005663' (UNIQUE) 9 1 TABLE ACCESS (BY INDEX ROWID) OF
'NEWSLETTER_SUBSCRIPTION' (Cost=1 Card=4 Bytes=32)
10 9 INDEX (UNIQUE SCAN) OF 'PK_NEWS_SUBCR_CODE' (UNIQUE)

LAST_SUBSCR_DATE


   SELECT MAX(co.ORDER_DATE)
     FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p     WHERE co.ORDER_ID = ol.ORDER_ID

      AND ol.PRODUCT_ID = p.PRODUCT_ID
      AND p.SUBPRODUCT_ID = 208
      AND co.CUSTOMER_ID = 1068733494;

MAX(CO.OR



11-DEC-06 Elapsed: 00:00:00.06

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=1 Bytes=37)    1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (Cost=45 Card=1 Bytes=37)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF

'CUSTOMER_ORDER' (Cost=12 Card=9 Bytes=180)
4 3 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON- UNIQUE) (Cost=3 Card=9) 5 2 NESTED LOOPS (Cost=32 Card=224 Bytes=3808) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=2 Card=5 Bytes=35) 7 6 INDEX (RANGE SCAN) OF 'PROD_SUBPRODUCT_IDX' (NON- UNIQUE) (Cost=1 Card=5) 8 5 TABLE ACCESS (BY INDEX ROWID) OF
'ORDER_LINE' (Cost=6 Card=431090 Bytes=4310900)
9 8 INDEX (RANGE SCAN) OF 'OL_PRODUCT_IDX' (NON-
UNIQUE) (Cost=2 Card=431090)

LAST_SUBSCR_STATUS_TMP


    SELECT co.STATUS

	  FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p
     WHERE co.ORDER_ID = ol.ORDER_ID
	   AND ol.PRODUCT_ID = p.PRODUCT_ID
	   AND co.CUSTOMER_ID = 1068733494
	   AND p.SUBPRODUCT_ID = 208
	   AND co.ORDER_DATE = last_subscr_date_tmp(1068733494, 208);

STATUS



Active

Elapsed: 00:00:03.75

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=47)    1 0 NESTED LOOPS (Cost=16 Card=1 Bytes=47)

   2    1     NESTED LOOPS (Cost=15 Card=1 Bytes=40)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF

'CUSTOMER_ORDER' (Cost=12 Card=1 Bytes=30)
4 3 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON- UNIQUE) (Cost=3 Card=1) 5 2 INDEX (RANGE SCAN) OF 'ORDER_PRODUCT_IDX' (UNIQUE) (Cost=3 Card=431090 Bytes=4310900) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=1 Card=5 Bytes=35) 7 6 INDEX (UNIQUE SCAN) OF 'SYS_C005663' (UNIQUE)

LAST_SUBSCR_DATE_TMP


      SELECT MAX(co.ORDER_DATE)
     FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p
    WHERE co.ORDER_ID = ol.ORDER_ID
      AND ol.PRODUCT_ID = p.PRODUCT_ID
      AND p.SUBPRODUCT_ID = 208
      AND co.CUSTOMER_ID = 1068733494
      and co.status = 'Active';

MAX(CO.OR



11-DEC-06 Elapsed: 00:00:00.02 Received on Wed Feb 28 2007 - 15:54:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US