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.

asked 09 Jul '10, 00:26

Jen's gravatar image

Jen
80235
accept rate: 0%


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.

permanent link

answered 09 Jul '10, 12:01

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

Note: I was wrong about manual refresh text indexes. Thanks to Elmi for pointing out to me that manual refresh text indexes are also unsupportable because the server must be able to track the changes to the column(s) values in order to refresh the text index when requested to do so.

As such, a fix will be made to the s/w to issue an error if a text index is attempted to be created on a proxy table.

(09 Jul '10, 15:44) Mark Culp

love this site-- where else can I get a definitive answer so quickly? :)

(09 Jul '10, 17:10) Jen

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.

permanent link

answered 21 Jul '10, 23:54

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

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.

(22 Jul '10, 09:48) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107
×63
×15

question asked: 09 Jul '10, 00:26

question was seen: 1,407 times

last updated: 21 Jul '10, 23:54