Environment:
SQL 2005 with SP3
Linked Server = Oracle 11g Server
We are using the Oracle 10g Client

If I made a query the duration takes a very long time until i get the results.
In the Oracle Session Manager I saw that the SQL Server wasnt using the
Oracle index. How could I force the SQL Server to use the Oracle indexes?


Our Providers (OraOLEDB.Oracle) settings:
"Allow inprocess" and "Supports 'Like' operator" is activated, the rest is
deactivated.

Our Linked Server Properties:
The follow settings are "True":
"Collation Compatible"
"Data Access"
"Use Remote Collation"


Any help would be appreciated

TSSDatabase (TSSDatabase (AT) discussions (DOT) microsoft.com) writes:
It is my understanding of distributed queries, that the local SQL Server
does not take the indexes of the remote data source in regard. Rather,
SQL Server acts as a client and submits one or more queries to the
linked server. Thus, it is Oracle that for some reason decides not to
use the index for some reason. Although, that could very well be due
to that SQL Server submits the query in such a way, that the index is
not meaningful to use.

Had the remote server been another SQL Server instance, I would have
adviced you to run a Profiler trace on the remote server to see what
queries that are submitted to it. I assume that Oracle provides a
similar tool, but I have no experience of Oracle myself.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx