Re: Unnesting Subqueries in an Outline

Luke,
Before erring on the dark side, outlines and all, since you can obviously modify your query, what about: 

SELECT f.fund_code,
           fp.bid_price
FROM   (select fund_code,
                      company_flag,
                      bid_price,
                      row_number() over (partition by fund_code,
company_flag order by unp_number desc) rn 
            from fund_prices
            where unit_code = 'B'
                 and mfp_status = 'C'
                 and company_flag = 'ISA') fp,
            funds f
WHERE  f.fund_code  = fp.fund_code
AND f.company_flag = fp.company_flag AND fp.rn = 1
Beginning to be late here and I'm getting tired so I don't guarantee it's 100% correct, but you see the idea. If keys etc. are what I think they are, it's likely to be faster even than your best version so far. Not sure you need to join with funds, by the way, since you can get the fund_code from fund_prices, unless there are additional columns that are returned and which you have omitted, or you have to check for existence of the code in the funds table - but you should have a FK for that, shouldn't you? 

HTH


Stephane Faroult

Luke Davies wrote:




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.

Cheers
Luke

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






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

Other related posts: