|
In a query on a string column with an index what will be the better approach when looking for non empty entries:
|
|
Even if the column is indexed, it would be somewhat astonishing to use an index to satisfy a matching predicate like "string > '' " unless the vast majority of the rows have the empty string. NULL might work better for you, as in SQL Anywhere IS NULL is a sargable predicate. Reimer is correct, length(string) <> 0, or alternatively length(string) > 0, is not a sargable predicate. The predicate string > '' is sargable. Also, the selectivity of length(string) > 0 will be a guess, while the selectivity of string > '' can be estimated by sampling the index. 1
Even if the predicate was sargable, would an index be rejected if a large percentage of the values were non-empty? (i.e., not a very selective predicate). Assuming the predicate was highly selective (e.g., 1% or fewer of the values were non-empty), then could a computed column be used to create a sargable predicate?
(12 May '12, 09:30)
Breck Carter
Replies hidden
So the real story is to store NULL instead of empty strings, methinks...
(12 May '12, 18:07)
Volker Barth
(14 May '12, 09:13)
Glenn Paulley
I guess Breck implies to use "LENGTH(string)" as computed column expression...
(14 May '12, 09:29)
Volker Barth
Yes, using length(string) as the computed column expression will permit sargable predicates involving length().
(14 May '12, 09:49)
Glenn Paulley
|
|
IIRC a where clause containing a function is not sargable, so your other alternative looks better to me. But probably one the Sybase experts will elaborate on that. |
