Oracle Pass Through Query

[Mark]

Hello there. My ongoing saga of SQL Server to Oracle continues. I have the
databases chatting away happily BUT I need to find a better way to query
the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
head off and then kindly provide me with a better solution:

select * from openquery(IFSandGSPS,
'select description from inventory_part_tab') where Description like '
%module%'

thanks

Mark

[Chris Hylton]

nothing lame that I see about...what do you think is lame about it or are
you just not sure of your solution...I would always do exactly what you've
done here with an 'openquery' statement running it as passthru to let Oracle
handle the request and send back the results.

I think what you have is fine and can't think of any other way to do it other
than using a direct query instead of a pass-thru (which I don't recommend).
If you have performance issues, it's due to description not being indexed,
you could index that field, but I'm not sure that's the best solution...I'd
look at another way to get to that data (if there is a way) through another
key field...but, given your '%modules%', and the simplicity of the query,
I think that's about all you can do.

Chris

"Mark" <mtaylor@globalsolar.com> wrote:
>
>Hello there. My ongoing saga of SQL Server to Oracle continues. I have
the
>databases chatting away happily BUT I need to find a better way to query
>the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
>Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
>head off and then kindly provide me with a better solution:
>
>select * from openquery(IFSandGSPS,
>'select description from inventory_part_tab') where Description like '
>%module%'
>
>thanks
>
>Mark

[mark]

Chris, thanks for the feedback. I found some other ways to zero in on the
parts I want and those fields are easily indexed. Thanks for the vote of
confidence. I'm only just getting up to snuff on distributed queries. I
didn't find this solution is a book, i.e., pulling everything from oracle
and then applying the selection criteria. I just sort of stumbled upon this
by playing around with the Query Analyzer. Cool! I can think!

Mark

"Chris Hylton" <cchylton@hotmail.com> wrote:
>
>nothing lame that I see about...what do you think is lame about it or are
>you just not sure of your solution...I would always do exactly what you've
>done here with an 'openquery' statement running it as passthru to let Oracle
>handle the request and send back the results.
>
>I think what you have is fine and can't think of any other way to do it
other
>than using a direct query instead of a pass-thru (which I don't recommend).
> If you have performance issues, it's due to description not being indexed,
>you could index that field, but I'm not sure that's the best solution...I'd
>look at another way to get to that data (if there is a way) through another
>key field...but, given your '%modules%', and the simplicity of the query,
>I think that's about all you can do.
>
>Chris
>
>"Mark" <mtaylor@globalsolar.com> wrote:
>>
>>Hello there. My ongoing saga of SQL Server to Oracle continues. I have
>the
>>databases chatting away happily BUT I need to find a better way to query
>>the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
>>Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
>>head off and then kindly provide me with a better solution:
>>
>>select * from openquery(IFSandGSPS,
>>'select description from inventory_part_tab') where Description like '
>>%module%'
>>
>>thanks
>>
>>Mark
>

[Chris Hylton]

Great...glad you found a better query...

If you play around with linked servers in SQL Server much, you'll find that
SQL Server (actually it's the data providers fault) isn't too bright when
it comes to using indexes on the linked platform (in your case Oracele).
The OPENQUERY statement resolves this issue by doing pass-thru and I pretty
much don't use direct queries unless linking to Access or SQL Server, I use
OPENQUERY all the time (especially for Oracle).

Chris

"mark" <mtaylor@globalsolar.com> wrote:
>
>Chris, thanks for the feedback. I found some other ways to zero in on the
>parts I want and those fields are easily indexed. Thanks for the vote of
>confidence. I'm only just getting up to snuff on distributed queries. I
>didn't find this solution is a book, i.e., pulling everything from oracle
>and then applying the selection criteria. I just sort of stumbled upon
this
>by playing around with the Query Analyzer. Cool! I can think!
>
>Mark
>
>"Chris Hylton" <cchylton@hotmail.com> wrote:
>>
>>nothing lame that I see about...what do you think is lame about it or are
>>you just not sure of your solution...I would always do exactly what you've
>>done here with an 'openquery' statement running it as passthru to let Oracle
>>handle the request and send back the results.
>>
>>I think what you have is fine and can't think of any other way to do it
>other
>>than using a direct query instead of a pass-thru (which I don't recommend).
>> If you have performance issues, it's due to description not being indexed,
>>you could index that field, but I'm not sure that's the best solution...I'd
>>look at another way to get to that data (if there is a way) through another
>>key field...but, given your '%modules%', and the simplicity of the query,
>>I think that's about all you can do.
>>
>>Chris
>>
>>"Mark" <mtaylor@globalsolar.com> wrote:
>>>
>>>Hello there. My ongoing saga of SQL Server to Oracle continues. I have
>>the
>>>databases chatting away happily BUT I need to find a better way to query
>>>the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
>>>Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
>>>head off and then kindly provide me with a better solution:
>>>
>>>select * from openquery(IFSandGSPS,
>>>'select description from inventory_part_tab') where Description like '
>>>%module%'
>>>
>>>thanks
>>>
>>>Mark
>>
>