|
Hello all, I have a question about text indexes. I have two tables a header and detail table. Tables are conected by a column "objectid" varchar(20). The values in this column are in the format of 'AAA#A#######' a mixture of underscore, char, and numbers. There is an index on the header table, and a clustered index on the detail table for these columns. The question has come up whether the performance of a query that joins on these columns could be improved by making the indexs a full text index. I am thinking no it would not, my understanding of text indexes is to find given pieces of information within a document, not to match up values in a master detail relationship. However I am open to it if my understanding is incorrect. Thanks as always for your input. |
|
AFAIK text indexes are not used for join operations. Be careful of your choice for clustered index on the child table; are the rows physically inserted in key value order over time, across all rows in the table (not just rows under individual parents)? It's always best to use SQL to show us the schema, rather than English :) |
|
In addition to Breck's points: FWIW, if the connection between both tables is declared via a FOREIGN KEY relationship (I hope so), then SQL Anywhere will build one according physical index and two logical indexes on the according columns automatically, as documented here. A PRIMARY KEY or FOREIGN KEY specification can be declared with CLUSTERED in that context. Are the indexes you mention such automatically generated ones or are they addional ones declared with CREATE INDEX? When using character data for indexes it's usually preferrable to increase the selectivity of the according values - if all values would start with the same "prefix" "AAA" they might be less dinstiguishable within an index if that does only store parts of the values. (Then property "FullCompare" might give a clue.) Note: I'm currently not aware if and how SQL Anywhere will hash character data for index entries (whereas it's documented for Ultralite here), so that's wild guessing. I think I have read about that in older versions... (Breck, do you remember?)
@Breck: Well, probably I've read that in your book on ASA 9 - nice coincidence:)
(22 May '14, 03:54)
Volker Barth
|
