It seems that I have been labouring under a misapprehension for some years now ! So it would seem as if each remote database handles it's own portion of the query and returns only the result set to the initiating database.
However, I cannot understand the part that says "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. ....." How does the local optimizer know about 1) Remote Indexes 2) Statistics on Remote Tables/Indexes Hemant At 12:43 AM 15-08-02 -0800, you wrote: >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). 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).
