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 = 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hostetter, Jay M
Sent: Monday, August 09, 2004 10:30 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Igor Neyman
Sent: Friday, August 06, 2004 3:57 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Tuning Query w/database link

> select * from test_view@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@remotenode a,
 project_accounting_costs b
where a.alra_id=3Db.source_id
 and a.alra_id=3D100001;=20

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx





**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 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: