I'm mostly confirming my own (disappointed) findings, so this is part question, part product suggestion.
It seems like it would be useful for proxy tables to be able to access their remote table's text indexes, or create their own text index for their remote table, but they can't.
What I did: there is a text column in a table in our data warehouse that is constantly used for lookup, so we added a text index for that column. Statements using "CONTAINS" work just fine on that table.
Another database has a proxy table to that remote table, also to perform lookups. But when I attempt to use a "CONTAINS" statement on the corresponding column of the proxy table, I was surprised that it errored out and returned "Could not execute statement. No matching text index."
Obviously I have no idea what's going on under the covers, but if queries on the proxy table look to the remote table's data to perform regular lookups, it seems like queries on the proxy table that have a CONTAINS clause should be able to use that remote table's text indexes for lookups as well.
It doesn't seem like you can create a text index on a proxy table either, which was the other solution I tried. The documentation says, "You cannot create a text index on views or temporary tables." but doesn't say anything about proxy tables.
I tried creating a text index using the "CREATE TEXT INDEX" command on my proxy table, and while it didn't give me an error, my text index never refreshed even when prompted manually (and left overnight in case speed between remote and proxy tables was the issue).
I also tried creating a text index on a proxy table through Sybase Central, where the proxy table wasn't available when I tried to create a new text index.
Anyway, if this is a lack-of-feature and not me doing something wrong, it would be nice to see it in the future, as those full text indexes have really sped up our application.
asked 09 Jul '10, 00:26
Jen: You've hit a bug and a limitation.
The bug is that when you created a text index on the proxy table "with immediate refresh" (assuming that is what you did?) you should have received an error. The reason is that proxy tables cannot monitor the changes that are being made to the remote table... or put another way, the local database does not get notified when a remote table is changed.
If you created the text index without immediate refresh then you will need to manually refresh the text index in order to see any changes (if this did not work, then this is another bug?)
As for the first issue of getting an error when using the contains predicate on the proxy table, the local database does not (currently) know about the text index on the remote table and therefore it does not know how to handle the predicate. This is a product suggestion that we will consider for a future release.
answered 09 Jul '10, 12:01
One option you may be able to try could be the FORWARD TO statement. It will send the SQL statement you use directly to the remote server where I would think it could take advantage of the text index.
I've seen a little bit of a delay when using FORWARD TO then when just querying the proxy table itself, but this delay may be less than the one you experience without the text index.
answered 21 Jul '10, 23:54