Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unnesting Subqueries in an Outline

Re: Unnesting Subqueries in an Outline

From: Luke Davies <>
Date: Thu, 23 Aug 2007 15:49:58 +0100
Message-id: <>

> As far as I know, outlines aren't able to apply hints to correlated
> subqueries as they aren't assigned an alias.

This is what we are finding
> That is, your NO_UNNEST hints needs to go inside the subquery itself and
> the Outline system can't do this.
> It may be possible to use other hints in the parent level to achieve the
> same result. If so, you can then use the outline manipulation process (See
> Metalink 92202.1 for the 8i example) to get your original statement to use
> that outline.
> I haven't tested them but do the following hints work in your case?
> SELECT /*+ full(f) leading(f) use_nl(fp) push_subq */
> f.fund_code
> , fp.bid_price
> FROM fund_prices fp, funds f

We have tried these and several others, The only hint that works is /*+ RULE */ and that doesn't seem to help us. We have also tried manipulating the OL$HINTS tables, the most hopeful thing we tried was deleting all the hints except the RULE one, all to no avail.

Thanks to all for your efforts.


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

Received on Thu Aug 23 2007 - 09:49:58 CDT

Original text of this message