RE: Tuning Query w/database link
- From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Aug 2004 09:14:22 -0400
=20
>On the approach: I'm confused why you would want Oracle to have to
figure out=20
>the transitivity to possibly push, and for any remote query component I
>recommend explicitly giving Oracle as much filtering information as=20
>possible on the remote components.
You're right. After fiddling with hints both remotely (within the view)
and locally, I think I'm going to tell the developer that he needs to
provide more filtering criteria.
Thanks for the responses.
Jay
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
Sent: Monday, August 09, 2004 12:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Tuning Query w/database link
Did you try forced rownum projection?
select * from
project_accounting_costs b,
(select rownum, a.* from test_view@remotenode a where a.alra_id
between :low_bound and :high_bound)
where b.source_id =3D a.alra_id;
might do the trick. I left out the between on b.source_id. If the above
has the desire result, then you might add the redundant between back in.
If it doesn't screw up the remote push, it *might* favor a *possibly
good* hash_join in place of a possibly also good nested loop. You'd have
to turn on the Wolfgang event to really see what the optimizer is doing
in making the choice.
On the approach: I'm confused why you would want Oracle to have to
figure out the transitivity to possibly push, and for any remote query
component I recommend explicitly giving Oracle as much filtering
information as possible on the remote components.
Now, on actual data transmission: If you really need the generic view, I
suppose you have to stick with a.*, but I'm not sure Oracle pushes
enough details about actual queries so that unneeded columns are not
transmitted.
If you can do so without death by clutter, and if often some pretty long
columns' data is not actually required for a significant number of
actual queries, you might want to enumerate some actual views. As for
the extraneous rownum, if it actually makes this work you can probably
suppress it with column noprint in sqlplus and "I'm not sure what" for
other tools as far as actually transmitting it.
If you do the column subsetting, you might want to report back on your
success or lack thereof at finding a difference in bandwidth actually
consumed.
good luck!
mwf
-
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the =
use of the individual or entity to which they are addressed and may contain=
information that is privileged, proprietary and confidential. If you are n=
ot the intended recipient, you may not use, copy or disclose to anyone the =
message or any information contained in the message. If you have received t=
his communication in error, please notify the sender and delete this e-mail=
message. The contents do not represent the opinion of D&E except to the ex=
tent that it relates to their official business.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
