RE: DB Links

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_at_ppoone.com] 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
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 23 2004 - 08:47:54 CDT