30_Indexes-1.pdf - CITS1402 Relational Database Management...

This preview shows page 1 - 7 out of 24 pages.

The preview shows page 5 - 7 out of 24 pages.
CITS1402Relational Database Management SystemsVideo 30 — IndexesGordon RoyleDepartment of Mathematics & Statistics
A common situationConsider alarge tablerelatingnamestonumbers.nameid'Aardvark'45813012'Aarons'21342214'Aakon'12308082............'Zzypo'12313243Suppose that a frequent task is to find theidnumber associatedwith a name.
ROWIDSQLite maintains a special “secret column”ROWIDthat containsthe real row number.rowidnameid1'Aardvark'458130122'Aarons'213422143'Aakon'12308082..................1073741824'Zzypo'12313243This table has 230= 1073741824 rows (about a billion).
Finding the dataSuppose we are trying to find the row for the name'Snodgrass'.If we can find out whichrow numberhasname = 'Snodgrass',then the rest of the data can immediately be read off the disk.How hard is this to do - in particular, how manyprobesdo we needto locate the correct row?
Finding the rowWithout any further information, the DBMS has to perform alinear searchin which each row is examined in turn.For a query such asSELECT*FROM<table>WHEREname ='Snodgrass';the DBMS has to examineevery oneof the 230rows.If the database knows, or is told,namecolumn is sorted inalphabetical order, then it can do abinary search
Binary SearchThis search proceeds by identifying a range of rows that mustcontain'Snodgrass'and then repeatedly halving that range byprobing the middle row.This takes at most 30 probes to locate the row.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 24 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
One
Professor
N/A
Tags
Snodgrass

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture