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.

asked 21 May '14, 15:38

trexco's gravatar image

trexco
336111423
accept rate: 0%


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 :)

permanent link

answered 21 May '14, 21:46

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

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?)

permanent link

answered 22 May '14, 03:48

Volker%20Barth's gravatar image

Volker Barth
30.6k305455662
accept rate: 32%

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
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:

×15

question asked: 21 May '14, 15:38

question was seen: 500 times

last updated: 22 May '14, 03:54