| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning Query w/database link
Did you try forced rownum projection?
select * from
project_accounting_costs b,
(select rownum, a.* from test_view_at_remotenode a where a.alra_id between
:low_bound and :high_bound)
where b.source_id = 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
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hostetter, Jay M
Sent: Monday, August 09, 2004 10:30 AM
To: oracle-l_at_freelists.org
Subject: RE: Tuning Query w/database link
I had the id number mistyped - when I use a source_id that actually exists, the predicate is pushed to the remote server. If the record doesn't exist, the explain plan on the remote server doesn't show the predicate. However, as I said, this is a simplified version of my actual query. The actual query isn't just joining one "source id" - I used that for testing. So if I continue with my test query and add a range of source_ids (e.g. b.source_id >=3D1000001 and b.source_id <=3D 1009999), Oracle doesn't send the predicate to the remote server. The optimizer seems to think that it's better to a FTS on the view in the remote database. I have tried the in-line views and hints as suggested, but I'm still not having much luck. I might just tell the user to live with this one. Thanks for the replies!
Jay
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman
Sent: Friday, August 06, 2004 3:57 PM
To: oracle-l_at_freelists.org
Subject: RE: Tuning Query w/database link
> select * from test_view_at_remotenode a, project_accounting_costs b where
> a.alra_id=3Db.source_id and b.source_id=3D100001;
since a.alra_id=3Db.source_id, could you change your query to:
a.alra_id=3D100001 (instead of b.source_id=3D100001), so:
select * from test_view_at_remotenode a,
project_accounting_costs b
where a.alra_id=3Db.source_id
and a.alra_id=3D100001;=20
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
**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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Aug 09 2004 - 10:56:12 CDT
 |
 |