Resending >Date: Wed, 14 Aug 2002 23:42:35 +0800 >To: [EMAIL PROTECTED] >From: Hemant K Chitale <[EMAIL PROTECTED]> >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@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: [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).
