CODEWITHSUNDEEP
sqlperformanceindexingsap-ase
user2198683
user2198683

Reputation:

Sybase ASE 15 : Creating an index on multiple columns which values are inequally discriminating

I have a table mytable of 5 million records and a query that looks like 

select *
from mytable
where column1 = 'value1' and column2 = 'value2' and column3 = 'value3'

So I thought about creating an index based on the 3 columns but my problem is that I have no best column to put in the first position of the index because there is no column that is really discrimating compared to the others.

Therefore I would like to build something similar to the hash tables with a hash code based on these 3 columns. I tried a function-based index based on the concatenation of those 3 columns but it's taking so long to create that I never got it created and I believe it's the wrong way to achieve what I want. What is the correct way to achieve this ?

Upvotes: 0

Views: 1728

Answers (2)

markp-fuso
markp-fuso

Reputation: 34514

ASE's indexes are generally stored as b-trees, and while there's some hashing 'magic' that takes place during index searching, there's still a bit of traversal/searching required; if the first column of an index is not very selective then you can see some degradation in index search performance when compared to an index where the more selective column(s) is listed first; the difference in performance is really going to depend on the selectivity of the column(s) in question and the sheer size of the index (ie, number of index levels and pages that have to be read/processed).

If you're running ASE 15.0.3+, and you're running ASE on linux, you may want to take a look at virtually-hashed tables. In a nutshell ... ASE stores the PK index as a hash instead of the normal b-tree, with the net result being that index search times are reduced. There are quite a few requirements/restrictions on virtually-hashed tables so I suggest you take a look at your Transact-SQL User's Guide for more details.

Obviously (?) there's a good bit more to table/index design than can be discussed here; certainly not something that can be addressed by looking at a single, generic query. ("Duh, Mark!" ?)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Just create an index with three columns:

create idx_mytable_col1_col2_col3 on mytable(col1, col2, col3)

You have equality comparisons. The order of the columns in the index does not matter in this case.

Let the database do the work for you.

Upvotes: 1

Related Questions