Re: Unnesting Subqueries in an Outline

From: Luke Davies <luke.davies_at_hansard.com>
Date: Thu, 23 Aug 2007 12:55:28 +0100
Message-id: <46CD75B0.7080508@hansard.com>

Here are the traces

First the one without the hint

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=160281 Card=11 Bytes=737) 1 0 FILTER

   2    1     SORT (GROUP BY) (Cost=160281 Card=11 Bytes=737)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'FUND_PRICES (Cost=1 
Card=61 Bytes=1220)
   4    3         NESTED LOOPS (Cost=66 Card=7463438 Bytes=500050346)
   5    4           NESTED LOOPS (Cost=65 Card=121440 Bytes=5707680)
   6    5             TABLE ACCESS (FULL) OF 'FUNDS' (Cost=3 Card=247 
Bytes=4940)
   7    5             TABLE ACCESS (BY INDEX ROWID) OF 'FUND_PRICES' 
(Cost=1 Card=492 Bytes=13284)
   8    7               INDEX (RANGE SCAN) OF 'FP_F_FK_I' (NON-UNIQUE)
   9    4           INDEX (RANGE SCAN) OF 'FP_F_FK_I' (NON-UNIQUE)

Then the one with the NO_UNNEST hint

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=1 Bytes=33) 1 0 NESTED LOOPS (Cost=65 Card=1 Bytes=33)

   2    1     TABLE ACCESS (FULL) OF 'FUNDS' (Cost=3 Card=247 Bytes=3211)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'FUND_PRICES' (Cost=1 
Card=1 Bytes=20)
   4    3       INDEX (UNIQUE SCAN) OF 'FP_PK' (UNIQUE)
   5    4         SORT (AGGREGATE)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'FUND_PRICES' 
(Cost=1 Card=61 Bytes=1220)
   7    6             INDEX (RANGE SCAN) OF 'FP_F_FK_I' (NON-UNIQUE)

(Cost=1 Card=1475)

Cheers
Luke

Alvaro Jose Fernandez wrote:
> Luke,
>
> Please, put an or AUTOTRACE traceonly EXPLAIN ) plan, with and without
> NO_UNNEST, just to check.
>
> regards,
>
> alvaro.
>

The contents of this message and any attachments are confidential and are intended for the use of the persons to whom it is addressed. If you are not the intended recipient, you should not copy, forward, use or alter the message in any way, nor disclose its contents to any other person. Please notify the sender immediately and delete the e-mail from your system. The sender is not responsible for any alterations that may have occurred without authorisation. Any files attached to this email will have been checked by us with virus detection software before transmission. You should carry out your own virus checks before opening any attachments, as we do not accept any liability for loss or damage which may be caused by viruses.

For information regarding company registration please visit the contact page at www.hansard.com

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

Received on Thu Aug 23 2007 - 06:55:28 CDT