CLASSIFICATION OF INDEX
CLUSTERED
VS NON-CLUSTERED INDEXES
CLUSTERED
Clustered: is where the table data is physically
stored in the order of the keys on the index.
For all pages-locked tables, rows are stored in key
order on pages, and pages are linked in key order.
For data-only-locked tables, indexes are used to
direct the storage of data on rows and pages, but strict key ordering is not
maintained.
NON-CLUSTERED
Non-clustered Indexes
have a structure separate from the data rows. A non-clustered Index contains
the non-clustered index key values and each key value entry has a pointer to
the data row that contains the key value.
The pointer from an
Index row in a non-clustered Index to a data row is called a row locator. The
structure of the row locator depends on whether the data pages are stored in a
heap or a clustered table. For a heap, a row locator is a pointer to the row.
For a clustered table, the row locator is the clustered index key.
You can add non-key
columns to the leaf level of the non-clustered index to by-pass existing index
key limits, 900 bytes and 16 key columns, and execute fully covered, indexed,
queries for more information.
Clustered and non-clustered tree indexes are
illustrated below
Thus, clustered indexes are relatively expensive to
maintain when the file is updated.
Another
reason clustered index is expensive to maintain is that data entries may have
to be moved across pages, and if records are identified by a combination of
page id and slot, as is often the case, all places in the database that point
to a moved record (typically, entries in other indexes for the same collection
of records) must also be updated to point to the new location; these additional
updates can be very time consuming.
DENSE INDEX VS SPARSE DENSE
DENSE INDEX
A dense index in
databases is a file with pairs of keys and pointers for every record in the
data file. Every key in this file is associated with a particular pointer to a
record in the sorted data file. In clustered indices with duplicate keys, the
dense index points to the first record with that key.
An index record appears
for every search key value in file. This record contains search key value and a
pointer to the actual record.
SPARSE INDEX
Sparse Index
A sparse index in
databases is a file with pairs of keys and pointers for every block in the data
file. Every key in this file is associated with a particular pointer to the
block in the sorted data file. In clustered indices with duplicate keys, the
sparse index points to the lowest search key in each block. In Index, are
created only for some of the records.
To locate a record, we
find the index record with largest the search key value we are looking for.
We start at that record
pointed to by the index record, and proceed along the pointers in the file
(that is, sequentially) until we find the desired record.
Primary index: The primary index contains the key
fields of the table and a pointer to the non-key fields of the table. The
primary index is created automatically when the table is created in the database.
When you create the file, you must specify two things about the primary index:
1.
The name of the primary index
2.
The primary index specification
The name has a maximum
length of SFS_MAXJ=IELD_NAME_SIZE.
The primary index specification
defines the fields upon which the index is based. It also specifies l whether
the values in the index must be unique
Secondary
Index: used to index field that are neither ordering
fields nor key fields (there is no assurance that the file is organized on key
field or primary key field). One index entry for every tuple in the data file
(dense index) contains the value of the indexed attribute and pointer to the
block /record.
As
an example of how secondary indexes might be used, consider a database
containing a list of students at a college, each of whom has a unique student
ID number. A typical database would use the student ID number as the key;
however, one might also reasonably want to be able to look us students by last
name. To do this, one would construct a secondary index in which the secondary
index in which the secondary key was this last name.
INDEXES
USING COMPOSITE SEARCH KEYS
The search key
for an index can contain several fields; such keys are called Composite search
keys or concatenated keys. As an example, consider a collection of employee
records, with fields name, age, and sal, stored in sorted order by name. The
Diagram below illustrates the difference between a composite index with key
age, sal, a composite index with key sal, age, an index with key age, and an
index with key sal.
A range query is one in which not all fields in the search key are bound to constants. For example, we can ask to retrieve all data entries with age= 20; this query implies that any value is acceptable for the sal fields. As another example of a range query, we can be ask to retrieve all data entries with age<30 and sal >40.
Practice
question.
1. Explain indexing
2. Discuss different
types of indexes