Scribd Logo
Upload

Welcome to Scribd!

  • 36 views

    Inde

    Uploaded by

    Raj
    Dense and sparse indexes can be used to improve query performance. Dense indexes contain an index record for every search key value in the data file, pointing to the record location. This makes searching faster but uses more space. Sparse indexes only contain index records for some search key values, with each record pointing to a block of records. To search, the system finds the largest index record below or equal to the search key and proceeds sequentially through the block. Primary indexes can be dense or sparse, with sparse indexes using less space at the cost of slower searching.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Inde

    Uploaded by

    Raj
    36 views10 pages
    Dense and sparse indexes can be used to improve query performance. Dense indexes contain an index record for every search key value in the data file, pointing to the record location. This makes searching faster but uses more space. Sparse indexes only contain index records for some search key values, with each record pointing to a block of records. To search, the system finds the largest index record below or equal to the search key and proceeds sequentially through the block. Primary indexes can be dense or sparse, with sparse indexes using less space at the cost of slower searching.

    Original Description:

    123

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Dense and sparse indexes can be used to improve query performance. Dense indexes contain an index record for every search key value in the data file, pointing to the record location. This makes searching faster but uses more space. Sparse indexes only contain index records for some search key values, with each record pointing to a block of records. To search, the system finds the largest index record below or equal to the search key and proceeds sequentially through the block. Primary indexes can be dense or sparse, with sparse indexes using less space at the cost of slower searching.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    36 views10 pages

    Inde

    Uploaded by

    Raj
    Dense and sparse indexes can be used to improve query performance. Dense indexes contain an index record for every search key value in the data file, pointing to the record location. This makes searching faster but uses more space. Sparse indexes only contain index records for some search key values, with each record pointing to a block of records. To search, the system finds the largest index record below or equal to the search key and proceeds sequentially through the block. Primary indexes can be dense or sparse, with sparse indexes using less space at the cost of slower searching.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 10

    Indexing

    THE ROLE OF INDEXES

    The utilization of indexes can dramatically reduce the execution time of various
    operations such as select and join.

    Dense Index: In this case, indexing is created for primary key as well as on the columns
    on which we perform transactions.

    That means, user can fire query not only based on primary key column. He can query
    based on any columns in the table according to his requirement.

    But creating index only on primary key will not help in this case. Hence index on all the
    search key columns are stored.

    This method is called dense index.


    Sparse Index: Data-file is ordered by the index search key and only some of the search
    key values have corresponding index records.

    Each index record’s data-file pointer points to the first data-file record with the search
    key value.

    Or

    In order to address the issues of dense indexing, sparse indexing is introduced. In this
    method of indexing, range of index columns store the same data block address. And
    when data is to be retrieved, the block address will be fetched linearly till we get the
    requested data.
    Primary index is classified into two types : Dense Index and Sparse Index.

    Dense index
    •For every search key
    value in the data file,
    there is an index
    record.
    •Index records contain
    search key value and a
    pointer to the actual
    record on the disk.

    •This makes searching


    faster but requires
    more space to store
    index records itself.
    Sparse Index :

    •The index record appears only for a few items in the data file. Each item points to a block
    as shown.
    •To locate a record, we find the index record with the largest search key value less than or
    equal to 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: Primary index is defined on an ordered data file. The data file
    is sorted according to a key field. The key field is generally the primary key of the
    relation.

    •Secondary Index: Secondary index may be generated from a field which is a


    candidate key and has a unique value in every record, or a non-key with duplicate
    values.

    Multi-Level Index: An index structure consisting of 2 or more tiers of records where


    an upper tier’s records point to associated index records of the tier below.

    Clustering Index: Clustering index is defined on an ordered data file. The data file is
    ordered on a non-key field.
    Secondary Index
    References
    Avi Silbershatz, Hank Korth and S. Sudarshan. Database System Concepts, 4th Edition. McGraw-Hill, 2002.

    You might also like