Hemant, It -----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@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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
