Re: select via dblink does not use index

Yechiel Adar wrote:
>
> I did a select like:
>
> select name from local_table , remote_table
> where local_table.account = remote_table.account.
>
> Remote table is a view with dblink.
>
> I select about 100 records out of about 1M records at the remote db.
>
> I found out that oracle does full table scan at the remote site.
>
> I will welcome ideas how to make oracle use the index on the remote side.
>
> Yechiel Adar
> Mehish

Yechiel,

   Queries across database links are amongst the worse I know. For one thing, Oracle isn't always sure about what type of database stands on the other site - may not be Oracle.

   When you can apply 'locally' (i.e. within one database) selective enough criteria creating a view is generally a good idea. You also have hints to tell Oracle which is the driving site. I am not sure that such a hint might incite it to 'push' your local data to the remote site to do the join, then 'pull' back the resulting set; I haven't played around enough with them. If it doesn't work, the safest, although utterly inelegant, solution would be to create a database link to your local database on the remote one, to create (on this remote database) a view doing the join and then query remotely the view from your local database. Told you it was ugly ...

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).