July 21, 2009 at 1:11 am
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
July 21, 2009 at 1:37 am
To get a better control on how query is executed on the remote server, I suggest you use OPENQUERY instead of the four-part name syntax. What you put in the OPENQUERY is sent directly to the linked server, which decides the correct way to handle the query.
Using OPENQUERY will also perform significantly better when the number of rows returned by the remote query is little. Working with four-part names, when the provider can't find a way to filter the remote table with the arguments specified, it copies the whole table to SQLServer in tempdb. With OPENQUERY you just get the rows you need.
Hope this helps
Gianluca
-- Gianluca Sartori
July 21, 2009 at 5:24 am
Firstly, I'm in agreement about relying in OPENQUERY.
Secondly, check query plan on Oracle side.
Run an explain plan for the offending query, it will tell you if Oracle -not SQL Server - would use a specific index or not.
You can always force the use of an index by adding an index hint.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 21, 2009 at 5:48 am
Thanks for your answers.
I tried to use an index hint, but I got the following error message:
Msg 7377, Level 16, State 1, Line 1
Cannot specify an index hint for a remote data source.
any workarround (but please dont say "openquery" ;-))
July 21, 2009 at 5:58 am
simon.ciglia (7/21/2009)
any workarround (but please dont say "openquery" ;-))
What about EXEC AT? 🙂
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply