I had always understood that a query which joins a remote table
to a local table would pull the entire remote table over and then
do the join locally.
However, the example in the Oracle9i Database Performance
Tuning Guide and Reference (9.2), Chapter 2 Optimizer Operations
"How the CBO evaluates remote operations"
[the URL is http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96533/opt_ops.htm#1004878]
seems to show that the REMOTE operation actually does a query
with a filter against the remote table before pulling data across :
How the CBO Evaluates Remote Operations
The remote operation indicates that there is a table from another database being accessed through a database link. Example 2-10 has a remote driving table:
Example 2-10 How the CBO Evaluates a Query with a Remote Driving Table
SELECT c.customer_name, count(*)
FROM ra_customers c, so_headers_all@oe h
WHERE c.customer_id = h.customer_id
AND h.order_number = :b1
GROUP BY c.customer_name;
Plan
--------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
NESTED LOOPS
REMOTE
TABLE ACCESS BY INDEX ROWID RA_CUSTOMERS
INDEX UNIQUE SCAN RA_CUSTOMERS_U1
Remote Database Query Obtained from the Library Cache
SELECT "ORDER_NUMBER","CUSTOMER_ID"
FROM "SO_HEADERS_ALL" "H"
WHERE "ORDER_NUMBER"=:"SYS_B_0";
The next example on how the CBO evaluates a query with
a Local Driving table is similar -- it passes a WHERE clause
to filter the Remote Table.
The example even goes on to show how a Hint could be
applied to drive the query on the Remote Table.
Is this (that a WHERE clause is applied to the
Remote table and that the full Remote table is not
copied over) true ? Has this been the behaviour since 8i ?
What about the Rule-Based Optimizer ? Would it behave
the same way ?
Now using Eudora Email. Try it !
My home page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).