Index variations
Download
1 / 10

Index Variations - PowerPoint PPT Presentation


  • 136 Views
  • Uploaded on

Index Variations. Indexed Files - Part Three Analyzing the Options. Second Level Index in Multiple Files. Must have space to allow each 1:1 Index File to grow. Main index points to multiple secondary index files, which are each one cluster in size (e.g. 8KB). Sparse Second Level Index.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Index Variations' - calvin-briggs


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Index variations

Index Variations

Indexed Files - Part Three

Analyzing the Options


Second level index in multiple files
Second Level Index in Multiple Files

Must have space to allow each

1:1 Index File to grow.

Main index points to multiple secondary index files, which are each one cluster in size (e.g. 8KB).


Sparse second level index
Sparse Second Level Index

  • Q: Assuming 2-Levels, does the second level need to be 1:1?

  • A: It depends

    • if datafile is sorted, then no

    • if datafile is unsorted, then yes


Multiple indexes for multiple keys
Multiple Indexes for Multiple Keys

  • If our key is Customer Name, then we cannot sort by Customer ID

    • please note that names are terrible keys, because names are not unique


Multiple indexes for multiple views
Multiple Indexes for Multiple "Views"

  • Suppose there are different types of users, with different levels of access.

    • Implemented in Linux via "set effective user id".


Three level index

Data File

Level Three

Three Level Index

Level Two

Level One


When to use 3 levels
When to Use 3 Levels

  • When the number of records in the datafile is very large.

  • Assume each index record for levels Two and Three take 28 bytes each

    • key = product ID = 20 bytes

    • RRN = long int = 8 bytes

  • BestMax Index section for internal sorting = 315 records

    • 8KB cluster / 28B = 315.01

  • If N = 1,000,000 data records

    • then level three contains 1,000,000 records

    • and level two contains 3175 index records

      • 1,000,000 / 315 = 3174.6

    • and level one contains 11 index records


Timing analysis 1
Timing Analysis 1

  • Given a data file:

    • 100,000 records

    • no index

    • sorted

  • How much time does a searchtake on average?

  • Using a binary file search

    • log2N reads of datafile records

    • log2100,000 = 16.6

    • = 17 reads of datafile records


Timing analysis 2
Timing Analysis 2

  • Given an Indexed File with

    • 100,000 records

    • two index levels

      • top level index size = 1,000 elements

  • How much time does a search take on average?

  • Level One

    • linear search time = N / 2

    • 1000 / 2 = 500 memory compares

  • Level Two

    • read the portion into memory = 1 read of 100 index records

    • binary search that portion = Log2 100 = 7 memory compares

  • Data File

    • 1 datafile record read


To index or not to index
To index, or not to index...

  • No Index File

    • 17 reads of datafile records

  • Two Levels of Index

    • 507 memory compares

    • 1 read of 100 index records

    • 1 datafile record read


ad