Comparison of I/O Costs

Comparison of I/O Costs

Heap File:

• A  heap  file  has  good  storage  efficiency  and  supports  fast  scanning  and insertion of records.
• However, it is slow for searches and deletions.

Sorted File:

• A sorted file also offers good storage efficiency.
• But insertion and deletion of records is slow.
• Searches are faster than in heap files.
• It is worth noting that, in a real DBMS, a file is almost never kept fully sorted.

Clustered File:

• A clustered file offers all the advantages of a sorted file and supports inserts and deletes efficiently. (There is a space overhead for these benefits, relative to a sorted file, but the trade-off is well worth it.)
• Searches are even  faster than  in sorted  files, although a sorted  file can be  faster when a large number of records are retrieved sequentially, because of blocked I/O efficiencies.

Unclustered tree and Hash indexes:

• Unclustered tree and hash indexes offer fast searches, insertion, and deletion, but scans and range searches with many matches are slow.
• Hash  indexes  are  a  little  faster  on  equality  searches,  but  they  do  not  support  range searches.

(a) Scan  (b) Equality  (c ) Range  (d) Insert  (e) Delete