Using Index on View on Linked Server table

Question

Using Index on View on Linked Server table

sakumar

Old Hand

Points: 307
More actions
July 8, 2004 at 8:29 am

#102101

Hi Gurus,
I created a linked server to Oracle database (817) and created a SQL view on one table (select * from table_name). When I try to use view and give the where clause (on the column, where Oracle table has index), it does not use the index on Oracle Side. This table is huge and from Oracle, it is causing performance issues. In Oracle, I can see that running query has no where clause. May be SQL is trying to bring everything to SQL and then find and present the values.
Now when I use the "openquery", using the same select, Oracle is using the index and brings results very fast.
Is there any way, view can use index ? That will be great help.

Thanks, Sanjay
sakumar@oxhp.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Unfortunately, I don't think that is possible.

The problem is the the SQL Server is requesting data from Oracle FROM SQL and SQL has no way of knowing or handling the Oracle indexes.

OPENQUERY on the other hand says to Oracle run this and return the data I want...

Good Luck

Good Hunting!

AJ Ahrens

webmaster@kritter.net

dhansensqlservercentral SSC Enthusiast Points: 135 More actions · Answer 2

Using OPENQUERY will first return all the data from Oracle to SQL Server. What you could try doing, if you are running 2000, is create a user defined function. Inside the function, do not use openquery, but instead you the naming convention

select * from [linked server].[object owner].[object name] where parm1 = @parm1.

dbuchan SSCrazy Points: 2328 More actions · Answer 3

I think you'll find that Openquery passes the command to an OLE DB Provider which is then processed at the target server - so if you place a where clause the rowset returned is likely to be reduced. I don't know if oracle will use it's own indexes but this seems a logical conclusion.

I'd ask my Oracle DBA's to prove this for me but they are in panic mode at the moment..... and I'm not going near their databases !

This was found in BOL:

The ways to generate a rowset from an OLE DB provider are:

Reference an object in the data source that the provider can expose as a tabular rowset. All providers support this capability.
Send the provider a command that the provider can process and expose the results of the command as a rowset. This capability requires that the provider support the OLE DB Command object and all of its mandatory interfaces.

When a provider supports the Command object, these Transact-SQL functions can be used to send it commands (called pass-through queries):

OPENQUERY sends a command string to an OLE DB data source using a linked server name.
OPENROWSET and OPENDATASOURCE support sending a command string to an OLE DB data source. The resulting rowset can be referenced using an ad hoc name.

The OLE DB specification does not define a single command language to be used by all OLE DB providers. OLE DB providers are allowed to support any command language that is related to the data they expose. OLE DB providers that expose the data in relational databases generally support the SQL language. Other types of providers, such as those exposing the data in an e-mail file or network directory, generally support a different language.

DB

The systems fine with no users loggged in. Can we keep it that way ?br>

sakumar Old Hand Points: 307 More actions · Answer 4

Thanks to all.

Here is what I want to achieve and may be you can provide me a better way: We are buying a SQL product which will using some tables. Now we have that same data in Oracle databases and that database is our primary database. I want to drop the SQL table and create view with same name so that it can access Oracle Data (read only). That will be transperant to SQL.

Any suggestions ???

Thanks, Sanjay

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 5

Index the SQL view and see what happens. When you index a view, SQL Server creates virtual tables that contain a copy of the data in the base tables. When you update the base tables, the view's tables get automatically updated.

I don't know if it will work on tables linked to Oracle.

Indexed views only work on Enterprise and Developer Edition.

Also, try putting the operquery in your view with a where clause in the operquery.

When in doubt - test, test, test!

Wayne