|
SqlAnywhere 11.0.1.2405. 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. |
|
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. http://dcx.sybase.com/index.html#1101en/dbreference_en11/forward-to-statement.html 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. FWIW the delay you speak of might be when a new connection must be established to the remote database. That happens when you start a new connection to the local database and then do something with a proxy table (which requires a new connection from the local to remote). The second FORWARD TO might run quicker. |
