RE: DB Links
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <klange@xxxxxxxxxx>, "Oracle-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 09:52:13 -0400
Kevin,
The answer is "it depends". Here's from my personal experience, and =
we've tons of db links and queries running across them.
If the optimizer believes, from your query, that it will receive one =
and only one row from the remote database then it will create a query =
for the remote database and pass it along, thereby utilizing the remote =
indexes. If on the other hand it believes that 2 or more rows from the =
remote database will be returned it basically formats a "select =
<column1>, <column2>, etc... from <remote table>" statement to the =
remote database, store the results of that locally in a temp table (no =
not a global temp, but a regular old temp table) and resolve the entire =
query locally. Needless to say the local temp table is NOT indexes.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Kevin Lange [mailto:klange@xxxxxxxxxx]
Sent: Wednesday, September 22, 2004 5:44 PM
To: Oracle-L (E-mail)
Subject: DB Links
Does anyone happen to have a reasonable explaination on what happens to =
a
query when you try to access tables accross db_links ?? Does it still =
use
the indexes on the remote machine ?? Does it bring all the data locally
into temp tables ??
Any info will do. Book names .... Actual explainations ... directions =
to
web sites ... Anything.
We have a procedure, a fairly complex procedure, that takes about 13 =
hours
to run when it is ran against tables that exist accross a DB link. But,
when we copy all the tables locally, it runs in about an hour. We would
like to try and find a way to fix this. But first, I need to know =
exactly
what is happening when the cursor access those linked tables.
Any help would be appreciated.
Kevin
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
