Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning Query w/database link

RE: Tuning Query w/database link

From: Hostetter, Jay M <JHostetter_at_decommunications.com>
Date: Tue, 10 Aug 2004 09:14:22 -0400
Message-ID: <D67EB7CEECD4334F9C85759227553BBC1CABC3@CL-EXCHANGE1.dande.com>


=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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Monday, August 09, 2004 12:00 PM
To: oracle-l_at_freelists.org
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_at_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_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 Tue Aug 10 2004 - 08:10:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US