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: [EMAIL PROTECTED] 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: [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).
