Re: Tuning Query w/database link

From: Michael Thomas <mhthomas_at_yahoo.com>
Date: Fri, 6 Aug 2004 15:16:31 -0700 (PDT)
Message-ID: <20040806221631.3475.qmail@web50606.mail.yahoo.com>

Hi,

• "Hostetter, Jay M" <JHostetter_at_decommunications.com> wrote:
  > I am trying to help a developer tune a query that
  > uses a database link.
  > For testing purposes, I've broken the query down to
  > the simple query
  > shown below. TEST_VIEW is a remote view that joins
  > three tables, which
  > results in approximately 1 million rows. The query
  > that Oracle passes
  > to the remote database never has a predicate - it is
  > always 'select *
  > from test_view'. I have tried various hints, but I
  ...

Have you tried subqueries and the PUSH_SUBQ (or its converse) hint? The idea being to either get/push the least data across the link from either side. E.g.

One simple example (push a few local rows) is:

SELECT /* PUSH_SUBQ */ r.col1, r.col2, r.col3 FROM tableA_at_remote_link r
WHERE (r.col1, r.col2) in (
  SELECT l.col4, l.col5
  FROM local_tableB l
  WHERE l.col6 = 'ABC' )
;

(PS: Watch out for possible NULLs)

I have used this technique many times with success.

Regards,

Mike Thomas                 

---

Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail

---

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 Fri Aug 06 2004 - 17:12:20 CDT