Re: Unnesting Subqueries in an Outline
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: luke.davies@xxxxxxxxxxx
- Date: Thu, 23 Aug 2007 23:43:31 +0200
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
