Oracle-L: RE: Query with a Remote Table over a DB-Link

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Thu, 15 Aug 2002 00:38:21 -0800
Message-ID: <F001.004B5AE6.20020815003821@fatcity.com>

Hemant,

-----Original Message-----
Sent: Wednesday, August 14, 2002 9:54 AM To: Multiple recipients of list ORACLE-L

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_at_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 <http://hkchital.tripod.com/>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:
Hemant K Chitale INET: hkchital_at_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_at_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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: John Kanagaraj
INET: john.kanagaraj_at_hds.com

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_at_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 Thu Aug 15 2002 - 03:38:21 CDT