| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query with a Remote Table over a DB-Link
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
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_at_oe h
WHERE c.customer_id = h.customer_id
AND h.order_number = :b1
GROUP BY c.customer_name;
Plan
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:
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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: john.kanagaraj_at_hds.com 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-LReceived on Thu Aug 15 2002 - 03:43:25 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |