Hemant, It looks like even 7.3 was able to treat a remote query differently - cut and paste from 7.3 Tuning guide below. Although it does not specifically state this, the 'fragmenting query' seems to indicate this....
FWIW! John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** Optimizing Distributed Statements The optimizer chooses execution plans for SQL statements that access data on remote databases in much the same way it chooses executions for statements that access only local data: * If all the tables accessed by a SQL statement are collocated on the same remote database, Oracle sends the SQL statement to that remote database. The remote Oracle instance executes the statement and sends only the results back to the local database. * If a SQL statement accesses tables that are located on different databases, Oracle decomposes the statement into individual fragments, each of which accesses tables on a single database. Oracle then sends each fragment to the database it accesses. The remote Oracle instance for each of these databases executes its fragment and returns the results to the local database, where the local Oracle instance may perform any additional processing the statement requires. When choosing the execution plan for a distributed statement, the optimizer considers the available indexes on remote databases just as it does indexes on the local database. If the statement uses the cost-based approach, the optimizer also considers statistics on remote databases. Furthermore, the optimizer considers the location of data when estimating the cost of accessing it. For example, a full scan of a remote table has a greater estimated cost than a full scan of an identical local table. -----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 -- 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).
