What is index architecture?

In database,index architectures can be classified as clustered or non-clustered.

Non-clustered:

A non-clustered index is structured in a way that doesn't correspond to the order of the actual data records. It resembles the words index at the back of a book. For this reason, it will perform worse than clustered indexes on ranged searches where the result set is large, since each result could cost an additional I/O-operation to get the actual data record. One can have any number of these kinds of indexes, since all that is required is the space to store the index itself -- one does not copy the actual data to create a new index.

Clustered:

Clustering re-orders the data block in the same order as the index, hence it is also an operation on the data storage blocks as well as on the index. A phone book ordered by last name resembles a clustered index in it structure and purpose. Exact operation of database systems vary, but because storing the row data more than once is never done in practice (since it is usually too expensive), the row data can only be stored in one order physically. Therefore, only one clustered index can be created on a given database table. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected. Since the physical records are in this sort order on disk the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, while others intermix the two different types of data blocks within the same physical file(s). Databases that use the latter scheme may be said to store the actual data in the leaf node of the index, whereas, in fact there is still a distinction between the index and the data block, and the data blocks can be traversed without using the index by way of a link list that links the data blocks in order.

Readers also visit these:

Related Questions: