Fwd: Query with a Remote Table over a DB-Link

Resending
>Date: Wed, 14 Aug 2002 23:42:35 +0800
>To: Oracle-L_at_fatcity.com
>From: Hemant K Chitale <hkchital_at_singnet.com.sg>
>Subject: Query with a Remote Table over a DB-Link
>
>
>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

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_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).