Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Why is this simple query taking forever?
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_typeFROM customer_order co, order_line ol, .product p, newsletter_subscription ns
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
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
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
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) OFUNIQUE) (Cost=2 Card=431090)
'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-
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
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