Figure sparse index 36 comparative analysis of dense

Info icon This preview shows pages 39–41. Sign up to view the full content.

View Full Document Right Arrow Icon
Figure: Sparse index.
Image of page 39

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
36 Comparative Analysis of Dense and Sparse Index It is generally faster to locate a record if we have a dense index rather than a sparse index. However, sparse indices have advantages over dense indices in that they require less space and they impose less maintenance overhead for insertions and deletions. A good trade-off There is a trade-off that the system designer must make between access time and space overhead . Although the decision regarding this trade-off depends on the specific application, a good compromise is to have a sparse index with one index entry per block . Why this trade-off is good The dominant cost in processing a database request is the time that it takes to bring a block from disk into main memory. Once we have brought in the block, the time to scan the entire block is negligible. Using this sparse index, we locate the block containing the record that we are seeking. Thus, unless the record is on an overflow block, we minimize block accesses while keeping the size of the index (and thus, our space overhead) as small as possible. Multi-Level Indices The problem with single-level indices Even if we use a sparse index, the index itself may become too large for efficient processing. It is not unreasonable, in practice, to have a file with 100,000 records, with 10 records stored in each block. If we have one index record per block, the index has 10,000 records. Index records are smaller than data records, so let us assume that 100 index records fit on a block. Thus, our index occupies 100 blocks. So, how can we minimize disk access? head2right If there are no overflow blocks in the index, we can use binary search. If there are B blocks, this will read as many as log 2 ( B ) blocks (as many as 7 for our 100 blocks). head2right If index has overflow blocks, then sequential search is typically used, reading all B index blocks. Thus, the process of searching a large index may be costly. Solution to this problem We treat the index just as we would treat any other sequential file, and construct a sparse index on the primary index, as in the figure beside. To locate a record, we first use binary search on the outer index to find the record for the largest search-key value less than or equal to the one that we desire. The pointer points to a block of the inner index. We scan this block until we find the record that has the largest search-key value less than or equal to the one that we desire. The pointer in this record points to the block of the file that contains the record for which we are looking. Using two levels of indexing, it is required to read only one block rather than seven with binary search, if we assume that the outer index is already in main memory. For very large files, additional levels of indexing may be required.
Image of page 40
Image of page 41
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern