Re: Unnesting Subqueries in an Outline

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 23 Aug 2007 23:43:31 +0200
Message-ID: <46CDFF83.4070201@roughsea.com>

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

Received on Thu Aug 23 2007 - 16:43:31 CDT