Sybase
Essay Preview: Sybase
Report this essay
Table of contents
Types of indexes
Clustered Index (CI)
The data in this case is physically stored in the order of the index. The leaf level of the index is the same as the data pages. There can be only one CI on a table as the data can physically be sorted in only one order. The select is extremely efficient with CI. The CI is extremely efficient in the following cases:
a) where fname like “Ram%”
b) where author_id between 1 and 7
c) where Price > 345.34
d) group by author_id
e) order by author_name
Non-clustered index (NI)
The data in this case is not stored in the order an
index is stored. The leaf-level of the index contains the various index keys and a pointer to the row as rowID (page no. + row offset). There can be 249 NI on a table. The NI should be used when
a) The number of rows returned is small.
b) When where clause limits the number of rows (usually
= operator)
c) When the query can be covered.
Selection of indexes by Optimizer
SEARCH ARGUMENTS (SARG)
These are the expressions on the RHS of the where clause. They act as a kind of (dis)incentive to the optimizer to use the index on the column. Some search arguments are:
where author_id = 13
where fname like “Ram%”
where Price > 2347.32
Some expressions that are not valid SARGs are:
Invalid
Valid
Price*1.5 = 1000
Price = 1000/1.5
Qty + 10 = 200
Qty = 200 – 10
fname + + lname
fname = John
= “John Gray”
and lname = Gray
Substring(1,3,fname) = KIR
Name like “KIR%”
isnull(lname,”N”) = “N”
lname is null
The index might not be used in the case of following SARGs:
1) No start point for the index.
where lname like “%abc”
2) Non-matching data-types
In SQL server, null and not null are held differently.
Char null is same as varchar. So when char null and char not null is compared, the optimizer has to implicitly convert the data type, which it does not at the planning time.
In both the above cases, distribution statistics are
not used.
INDEX SELECTION
The optimizer first looks at the query if the columns
contained in the where clause match with the columns specified in any of the index. If yes, then it proceeds further.
The optimizer then looks if the where clause contains
any SARG. If there is a valid SARG, the optimizer then looks for the distribution statistics of the index as:
select distribution from sysindexes
where id = object_id(Authors)
If there are no statistics for the index, the optimizer defaults to a fixed percentage of rows depending upon the operator in the where clause:
Equality
Closed Interval
Open Interval
The index distribution statistics will not be used when the
optimizer does not know the value of the RHS in a where clause as in the following:
where Price = 2000*12,
where author_id