Hashing and Indexing John Ortiz Lecture 18 Hashing

Hashing and Indexing John Ortiz Lecture 18 Hashing and Indexing

Overview ® How to retrieve data records with a key value? æSequential search (O(N)) æBinary Search (O(log 2 N)) æHashing æConventional (simple) indexing æB+ tree indexing æMore advanced hashing & indexing Lecture 18 Hashing and Indexing 2

Hashing key h(key) <key> . . . Buckets (typically 1 disk block) ® A hash function maps a key value to a bucket address where the record can be found ® Good for queries with condition A=v ® Typical complexity O(1) Lecture 18 Hashing and Indexing 3

Hash Table ® What are stored in buckets? æOption 1: Actual records (hash table is the file) æOption 2: Key values and pointers to records key h(key) key 1 Hash table record Data file EShould we sort keys in buckets? Lecture 18 Hashing and Indexing 4

Hash Functions ® Perfect hash functions should evenly distribute key values to buckets (very difficult to come by). Good hash functions do a random distribution. ® Ex: Key = ‘x 1 x 2 … xn’ n byte character string and table has b buckets, h(key) = (x 1 + x 2 + … xn) mod b ® Many other choices (see Knuth Vol. 3) ® May have to handle collision (bucket overflow). Typically with chaining of overflow blocks ® With K mod M, 0 <= result <= M-1 Lecture 18 Hashing and Indexing 5

Loading Factor ® Loading Factor = # keys loaded / total # keys that fit ® Try to keep loading factor between 50% & 80% æIf < 50%, wasting space æIf > 80%, overflows significant depends on how good hash function is & on # keys per bucket Lecture 18 Hashing and Indexing 6

Indexing Data File Index File Find rec. w/ key 60 10 20 30 40 50 60 70 80 90 100 110 120 Lecture 18 50 20 30 60 10 80 70 40 90 100 Hashing and Indexing 7

Terms ® Data file: contains blocks of data records ® Index file: contains blocks of index entries ® Index entry: <index key, address> æIndex key: not necessarily a key of a relation æAddress: for record with a key value (may be block or record address) ® Search key: Index value used for a search Lecture 18 Hashing and Indexing 8

Types of Indexes ® What type of index key are used? æPrimary indexes æClustering indexes æSecondary indexes æMultilevel indexes æDynamic Indexes, B-Trees, B+-Trees ® Does every record has an index entry? æDense index, sparse index ® Consideration: How does it handle Updates? æInsertions? Deletions? Lecture 18 Hashing and Indexing 9

Primary Index ® Index key is the primary key ® Sparse: one entry per data block ® Data file sorted on index key ® Can be B+ tree as well Lecture 18 Index 10 30 50 70 90 110 130 150 170 190 210 230 Hashing and Indexing Data file 10 20 30 40 50 60 70 80 90 10

Primary Index ® Example 1, p. 159 ® Ordered file, r = 30, 000 records ® Block Size B = 1024 bytes/block ® Unspanned ® Record Length R = 100 bytes/record ® Bfr = floor( B/R ) records/block ® # blocks = ? ? ? (use units to determine formula!) ® Binary search yields ~log 2 B accesses ® Compare to search with primary index, see p. 159 Lecture 18 Hashing and Indexing 11

Clustering Index ® Index key is not a key of relation (may have duplicate values) ® Sparse: one entry per distinct value ® Data file sorted on index key ® Can be B+ tree Lecture 18 Index Data File 10 20 30 40 10 10 45 56 59 61 20 30 Hashing and Indexing 10 20 30 30 40 45 12

Secondary Index ® Index key: can be key or non-key ® Pointers point to record (not block) ® Dense: one entry per record (sparse at higher levels) ® Data file not sorted on index key! ® Can be B+ tree Lecture 18 Hashing and Indexing 13

Secondary Index 10 50 90. . . sparse high level Lecture 18 Data file 10 20 30 40 30 50 50 60 70. . . 80 40 Hashing and Indexing 20 70 10 90 60 14

Secondary Index for Non-Key ® Option 1: Dense index Index file Problem: excess overhead! • disk space 10 10 10 20 20 30 40 40. . . Lecture 18 Hashing and Indexing Data file 20 10 20 40 10 40 30 40 15

Secondary Index for Non-Key ® Option 2: Reserve multiple pointers Index file Problem: variable size records in index! Lecture 18 10 Data file 20 10 20 20 40 30 40 Hashing and Indexing 10 40 30 40 16

Secondary Index for Non-Key ® Option 3: Use pointer buckets Index file 20 10 10 20 30 40 20 40 10 40 50 60. . . Buckets Lecture 18 Data file Hashing and Indexing 10 40 30 40 17

Advantage of Pointer Buckets ® Assume following indexes on EMP(name, dept, floor, . . . ) æName: primary æDept: secondary æFloor: secondary ® Find employees in Toy dept on 2 nd floor æFind pointers for Toy dept æFind pointers for 2 nd floor æIntersect these sets of pointers æRetrieve records Lecture 18 Hashing and Indexing 18

Simple Multiple level Index level 2 Index level 1 10 90 170 250 10 30 50 70 330 410 490 570 90 110 130 150 170 190 210 230 Lecture 18 Hashing and Indexing Data file 10 20 30 40 50 60 70 80 90 100 19

B+ Tree Index ® The index file is organized as a B+ tree æHeight-balanced æNodes are blocks of index keys and pointers æOrder P: Max # of pointers fits in a node æNodes are at least 50% full æSupport efficient updates Lecture 18 Hashing and Indexing 21

B+ Tree Index Example ®P = 4 180 200 120 130 156 179 120 150 180 100 101 110 30 30 35 3 5 11 Index file Root Point to data records/blocks Lecture 18 Hashing and Indexing 22

Internal Nodes p 0 a 1 … ai pi ai+1 key < a 1 … … ak p k ai <= key < ai+1 … key >= ak ® The root must have k 2 pointers ® Others must have k P/2 pointers, where P is the order of the B+ tree ® Must have k keys and k+1 pointers ® Keys are sorted Lecture 18 Hashing and Indexing 23

Leaf Nodes a 1 p 1 … ai p i … ak p to data records to next Leaf node ® All external nodes are at the same level. ® Must have k P/2 keys, unless it is the only node in the tree. ® Keys are sorted ® Has a (block pointer) to next leaf node (other pointers can be block or record pointers) Lecture 18 Hashing and Indexing 24

An Example ® File: Employees(SSN, Name, Dept, Age, Phone) ® Attributes sizes in bytes: SSN (9), Name (25), Dept (4), Age (4), Phone (10). ® Block (page) size = 1024 bytes. ® # of records: 30, 000, packed unspanned. ® What is the file size in pages? æTuple Size = 9+25+4+4+10 = 52 bytes æ bf. Employee = 1024 / 52 = 19 record/page (block) æ b. Employee = 30, 000 / 19 = 1, 579 pages (blocks) Lecture 18 Hashing and Indexing 25

Example: B+ Tree Primary Index ® Pointer size = 4 bytes ® Nodes are 70% full ® How big is a B+ tree primary index on SSN? æOrder: P = (1024 + 9) / (9+4) = 79 æAverage order = 79*. 7 =56 pointers æ# pointers of internal nodes = 56 æ# index entries in leaf node = 55 æ# index entries = 1579 (one per page) æ# leaf nodes = 1579 / 55 = 29 æ# nodes next level = 29 / 56 = 1 Lecture 18 Hashing and Indexing 26

Example: B+ Tree Primary Index ® Total # of levels = 2 ® Total # of index nodes (pages) = 30 ® To answer the query: select * from Employees where SSN=123456789; æ# of page I/Os = 3 (2 index pages + 1 data page). Lecture 18 Hashing and Indexing 27

Example: B+ Tree Secondary Index ® B+ tree secondary index on Dept ® # of distinct values = 1000 ® Assume a dense index. æ# of index entries = 30000 ® Size of index entry = 8 bytes (4 -byte Dept + 4 -byte pointer) ® Order: P = (1024+4) / (4+4) = 128 ® Assume nodes are 70% full æ internal node has 90 pointers æ leaf node has 89 keys. Lecture 18 Hashing and Indexing 28

Example: B+ Tree Secondary Index ® # of leaf nodes = 30, 000 / 89 = 338 ® # of nodes at 2 nd level = 338 / 90 = 4 ® # of nodes at 3 rd level = 4 / 90 = 1 ® Total # of levels = 3 ® Total # of pages = 343 ® # records per distinct value = 30 (each on a different page) ® To find all employees of “Dept = x”, # of page I/O = 33 (3 pages of index + 30 pages of data) Lecture 18 Hashing and Indexing 29

Indexes in SQL ® Create a secondary index create index Salary_Index on Employees(Salary); ® Create an index on a key create unique index SSN_Index on Employees(SSN); ® Drop an index drop index Salary_Index; Lecture 18 Hashing and Indexing 30

Summary ® Hashing is every efficient, but is effective only when search condition is equality ® Indexing is effective for range selection as well as equality selection ® Simple indexing is good for small files ® ISAM is good if update is infrequent ® B+ tree is a dynamic structure. æInserts/deletes leave B+ tree heightbalanced; O(log. P N) cost. æTypically has 3 or 4 levels for large files Lecture 18 Hashing and Indexing 31

Summary (Contd. ) æAlmost always better than maintaining a sorted file (no sorting, no global moving) æTypically, 67%-70% full on average æUsually preferable to ISAM, modulo locking considerations; adjusts to growth gracefully. ® Oracle automatically creates index for primary key attribute(s) and unique attribute(s) ® It is not possible to specify different types of index or hashing using SQL ® Many other types of indexes … Lecture 18 Hashing and Indexing 32

Look Ahead ® Next topic: Query Processing and Optimization ® Read textbook: æChapter 18 Lecture 18 Hashing and Indexing 33