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 ?
Hemant K Chitale
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: hkchital@singnet.com.sg
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: ListGuru@fatcity.com (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).
Received on Wed Aug 14 2002 - 11:53:48 CDT
