Indexing Cost Model for Data Access Data should

Indexing

Cost Model for Data Access Data should be stored such that it can be accessed fast q Evaluation of Access Methods based on measuring the number of page I/O’s q I disk access in general more costly than CPU costs I CPU costs considered to be negligible in comparison with I/O I I Analysis ignores gains of pre-fetching blocks of pages; thus, even I/O cost is only approximated. Average-case analysis; based on several simplistic assumptions. Good enough to show the overall trends! 421: Database Systems - Index Structures 2

Typical Operations q Scan over all records I SELECT * FROM Employee q Equality Search I SELECT * FROM Employee WHERE eid = 100 q Range Search I SELECT * FROM Employee WHERE age > 30 and age <= 50 q Insert I INSERT INTO Employee VALUES (23, ‘lilly’, 37) q Delete I DELETE FROM Employee WHERE eid = 100 I DELETE FROM Employee WHERE age >30 AND age <= 50 q Update I Delete+insert 421: Database Systems - Index Structures 3

Alternative File Organizations Various alternative file organizations exist; each is ideal for some operations, not so good in others: q q Heap files: I Linked, unordered list of all pages of the file (e. g. , per relation) I Suitable when typical access is a file scan retrieving all records. I Costs for equality search (read on avg. half the pages) and range search (read all pages) is high. I Cost for insert low (insert anywhere) I Cost for delete/update is cost of executing WHERE clause Sorted Files: I Records are ordered according to one or more attributes of the relation I Outperforms heap files for equality and range queries on the ordering attribute (find first qualifying page with binary search in log 2(number-of-pages) I Also good for ordered output I High insert and delete/update costs 421: Database Systems - Index Structures 4

Indexes Even sorted file only support queries on sorted attributes. q In order to speed up selections on any collection of attributes, we can build an index for a relation over this collection. q I Additional information that helps finding specific tuples faster I We call the collection of attributes over which the index is I I built the search key attributes for the index. Any subset of the attributes of a relation can be the search key for an index on the relation. Search key is not the same as primary key / key candidate (minimal set of attributes that uniquely identify a record in a relation). 421: Database Systems - Index Structures 5

B+ Tree: The Most Widely Used Index q Each node/leaf represents one page q Leafs contain data entries (denoted as k*) I Since the page is the transfer unit to disk I For now, assume each data entry represents one tuple. The data entry consists of two parts l Value of the search key l Record identifier (rid = (page-id, slot)) q Root and inner nodes have auxiliary index entries Index for Sailors On attribute sid Root 13 2* 3* 5* 7* 14* 16* 421: Database Systems - Index Structures 17 24 19* 20* 22* 30 24* 27* 29* 33* 34* 38* 39* 6

B+ Tree (contd. ) q q q keep tree height-balanced. I Each path from root to tree has the same height F = fanout = number of children for each node (~ number of index entries stored in node) N = # leaf pages Insert/delete at log F N cost; Minimum 50% occupancy (except for root). I Each node contains d <= m <= 2 d entries. I The parameter d is called the order of the tree. Supports equality and range-searches efficiently. Index Entries 421: Database Systems - Index Structures Data Entries 7

Example B+ Tree q q q Example tree has height 2 Assume “Select * from Emp where eid = 5” Search begins at root, and key comparisons direct it to a leaf Search for 5*, 15*, all data entries >= 24*. . . Good for equality search AND range queries Root 13 2* 3* 5* 7* 14* 16* 421: Database Systems - Index Structures 17 24 19* 20* 22* 30 24* 27* 29* 33* 34* 38* 39* 8

Inserting a Data Entry q Find correct leaf L. q Put data entry onto L. If L has enough space, done! I Else, must split L (into L and a new node L 2) l Redistribute entries evenly, copy up middle key. l Insert index entry pointing to L 2 into parent of L. I q This can happen recursively I To split index node, redistribute entries evenly, but push up middle key. (Contrast with leaf splits. ) q Splits “grow” tree; root split increases height. I Tree growth: gets wider or one level taller at top. 421: Database Systems - Index Structures 9

Inserting 8* into Example B+ Tree Insert into Leaf with leaf split 13 3* 2* 17 5* 5 30 24 7* 2* 3* 13 17 5* 7* 24 30 8* Insert into internal node with node split Assume that inner pages Can only contain 4 index entries 13 17 24 30 421: Database Systems - Index Structures 17 5 13 24 30 10

Example: After Inserting 8* Root 17 5 2* 3* 24 13 5* 7* 8* 14* 16* 19* 20* 22* 30 24* 27* 29* 33* 34* 38* 39* Notice that root was split, leading to increase in height. In this example, we can avoid split by redistributing entries; however, this is usually not done in practice. 421: Database Systems - Index Structures 11

Data Entry k* in Index An index contains a collection of data entries, and supports efficient retrieval of all data entries k* with a given key value k. q Three alternatives: q I (1) Data tuple with key value k (direct indexing) I (2) <k, rid of data record with search key value k> (indirect indexing) I (3) <k, list of rids of data records with search key k> (indirect indexing) q Choice of alternative for data entries is orthogonal to the indexing technique (B-tree, hashing etc. ) 421: Database Systems - Index Structures 12

Alternatives for Data Entries q Alternative 1: (direct indexing) I I If this is used, index structure is a file organization for data records (like sorted files). At most one direct index on a given collection of data records. (Otherwise, data records duplicated, leading to redundant storage and potential inconsistency. ) If data records very large, # of pages containing data entries is high. Implies size of auxiliary information in the index is also large, typically. NOTE: FOR THE REST OF THIS COURSE WE WILL NOT CONSIDER DIRECT INDEXING ANYMORE Index entries Data entries = data records 421: Database Systems - Index Structures 13

Alternatives for Data Entries (Contd. ) q Alternatives 2 and 3: (indirect indexing) I Data entries typically much smaller than data records. So, better than direct indexing with large data records, especially if search keys are small. I If more than one index is required on a given file, at most one direct index; rest must use indirect indexing I Alternative 3 more compact than Alternative 2, but leads to variable sized data entries even if search keys are of fixed length. Index entries Data entries 421: Database Systems - Index Structures Data records 14

Index Classification q Primary vs. secondary: If search key contains primary key, then called primary index. I q Unique index: Search key contains a candidate key. Clustered vs. unclustered: If order of data records is the same as, or `close to’, order of data entries, then called clustered index. I I I Clustered index can be of alternatives 1, 2 and 3 ! A file can be clustered on at most one search key. Cost of retrieving data records through index varies greatly based on whether index is clustered or not! 421: Database Systems - Index Structures 15

Clustered vs. Unclustered Index q Assume data itself (the real tuples) is stored in a Heap file. I I To build clustered index, first sort the Heap file (with some free space on each page for future inserts). Overflow pages may be needed for inserts. (Thus, order of data records is `close to’, but not identical to, the sort order. ) CLUSTERED Index entries UNCLUSTERED Data entries (Data file) Data Records 421: Database Systems - Index Structures Data Records 16

B+-tree cost example I Relation R(A, B, C, D, E, F) I A and B are int (each 6 Bytes), C-F is char[40] (160 Bytes) l Size of tuple: 172 Bytes I 200, 000 tuples I Each data page has 4 K and is around 80% full l 200, 000*172/(0. 8*4000) = 10750 pages I Values of B are within [0; 19999] uniform distribution I Non-clustered B-tree for attribute B, alternative (2) I An index page has 4 K and intermediate pages are filled between 50% - 100% I The size of an rid = 10 Bytes I The size of a pointer in intermediate pages: 8 Bytes I Index entry in root and intermediate pages: size(key)+size(pointer) = 6 Bytes + 8 Bytes = 14 Bytes 421: Database Systems - Index Structures 17

Size of B+tree I The average number of rids per data entry l Number of tuples / different values (if uniform) (Example 200, 000/20, 000 = 10) I The average length per data entry: l Key value + #rids * size of rid (Example: 6 + 10*10 = 106) I The average number of data entries per leaf page: l Fill-rate * page-size / length of data entry l Example: 0. 75*4000 / 106 = 28 entries per page I The estimated number of leaf pages: l Number of entries = number of different values / #entries per page l Example 20000 / 28 = 715 I Number of entries intermediate page: l Fill-rate * page-size /length of index entry l Min fill-rate: 0. 5, max fill rate: 1 l Example: 0. 5 * 4000 / 14 = 143 entries ; 1* 4000/14 = 285 entries I Height is 3: the root has between three and four children l Three children: each child has around 715/3 = 238 entries l Four children: each child has around 715/4 = 179 entries 421: Database Systems - Index Structures 18

B+ Trees in Practice q Typical order d of inner nodes: 100 (I. e. , an inner node has between 100 and 200 index entries) I Typical fill-factor: 67%. I average fanout = 133 Leaf nodes have often less entries since data entries larger (rids) q Typical capacities (order of inner nodes 100, leaf with 100 rids): q I I I q Height 4: 1334 = 312, 900, 721 records Height 3: 1333 = 2, 352, 637 records Height 2: 1332 = 17, 680 records Can often hold top levels in buffer pool: I I I Level 1 (root) = 1 page = 4 Kbytes Level 2 = 133 pages = 0. 5 Mbyte Level 3 = 17, 689 pages = 70 MBytes 421: Database Systems - Index Structures 19

Index in DB 2 q Simple I Create index ind 1 on Sailors(sid); I drop index ind 1; q Index also good for referential integrity (uniqueness) I Create unique index ind 1 on Sailors(name) q Additional attributes I Create unique index ind 1 on Sailors(sid) include (name) I Index only on sid I Data entry contains key value (sid) + name + rid I SELECT name FROM Sailors WHERE sid = 100 l q Can be answered without accessing Sailors relation! Clustered index I Create index ind 1 on Sailors(sid) cluster 421: Database Systems - Index Structures 20

Index in DB 2 q Index on multiple attributes: I Create index ind 1 on Sailors(Age, Rating); I Order is important: l Here data entries are first ordered by age l Sailors with the same age are then ordered by rating I Supports: l SELECT * FROM Sailors WHERE age = 20; l SELECT * FROM Sailors WHERE age = 20 AND rating < 5; I Does not support l SELECT * FROM Sailors WHERE rating < 5; 421: Database Systems - Index Structures 21

421: Database Systems - Index Structures 22

Summary for B+-trees q Tree-structured indexes are ideal for rangesearches, also good for equality searches. High fanout (F) means depth rarely more than 3 or 4. I Almost always better than maintaining a sorted file. I q Can have several indices on same tables (over different attributes) q Most widely used index in database management systems because. One of the most optimized components of a DBMS. 421: Database Systems - Index Structures 23

File Organizations q Hashed Files: . I File is a collection of buckets. Bucket = primary page plus zero or more overflow pages. I Hashing function h: h(r) = bucket in which record r belongs. h looks at only some of the fields of r, called the search fields. I Best for equality search (only one page access and maybe access to overflow page) I No advantage for range queries I Fast insert I Cost on delete depends on cost for WHERE clause 421: Database Systems - Index Structures 24

More comments on B+-trees q q q Corresponding delete operations exist that might merge subtrees B+-trees for predicate locking Locking B+-trees I I To allow concurrent access to the B-tree, internal locking protocol used (non 2 PL -- in case of abort: logical undo!!!) Special Index Locks used to implement “predicatelocking” 421: Database Systems - Index Structures 25