Hi, --- "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx> 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@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@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 -----------------------------------------------------------------