Redirected
retagged by
6,037 views
6 votes
6 votes
A Clustered index

A) is a dense index always.

B) is a sparse index always.

C) can be either dense index or a sparse index,but not both.

D) can be both dense index and sparse index.
retagged by

6 Answers

Best answer
1 votes
1 votes

My Take: Option (C) (A clustered index can be either dense index or a sparse index,but not both.)

(based on: DB book by Silberschatz, Korth and Sudarshan)


Image result for dense index

Image result for dense index

Definition for Dense Index is:

    An index record appears for every search key value in file.

Definition for Clustered Index is:

    Order of data records is the same as the order of index entries.

Hence, above both examples are of clustered and dense indexing.


Note:

Sparse indexing can only be clustered.

Eg:

figure106

(Think on your on why it can't be unclustered.)


selected by
7 votes
7 votes
Clustered indexing can be both dense and sparse. Lets see how

Quick recap: clustered indexing is done on non key attributes and it is sorted according to that attribute. Idea behind clustered indexing is to have one entry per unique value, as once we have pointer to that location we can find other entry for the value as it is sorted.

Case 1: non key attribute has unique value

It will be dense indexing as we have to give every value entry in index table

Case 2: non key attributes isn't unique

In this case we can have just one entry for every unique value in index table.
2 votes
2 votes

A sparse index in databases is a file with pairs of keys and pointers for every block in the data file, while a dense index in databases is a file with pairs of keys and pointers for every record in the data file.

A clustered index, is a index on a non-key field of a table. The records are physically ordered on this no-key field. 

Since it is a non-key field, so there may be multiple records against a single key. Hence, a key and a pointer to a first record of the block (which contain matching records against that key) is stored. 

Thus it is a Spare Index.

I could find one example on internet explaining the same

As can be seen above, clustered index is not containing pointer to every record in file. It is containing record to a block in a file.

On the other hand, dense index, which is unclustered, is holding record to every record in a file.

Also, note, CREATE TABLE... creates clustered index, while CREATE INDEX creates 
unclustered index.

Reference: 

http://jcsites.juniata.edu/faculty/rhodes/dbms/sortdfiles.htm

2 votes
2 votes
clustered index must be sparse....because though it's on non-key field but the data file should be ordered on that non key field, as the key filed of index is ordered in data file also....so of course it will be sparse....as the index pointer will point to the 1st block where the key value resides....

Check The  Elmasri&Navathe book for more details....but Secondary index may b sparse or dense

one important thing.....a data file either has a clustered index or primary index but not both....but a data file may have multiple secondary index with a primary index or clustered index
Answer:

No related questions found