1 / 49

B-trees - Hashing

B-trees - Hashing. Review: B-trees and B+-trees. Multilevel, disk-aware, balanced index methods primary or secondary dense or sparse supports selection and range queries B+-trees: most common indexing structure in databases all actual values stored on leaf-nodes. Optimality:

chul
Download Presentation

B-trees - Hashing

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. B-trees - Hashing

  2. Review: B-trees and B+-trees • Multilevel, disk-aware, balanced index methods • primary or secondary • dense or sparse • supports selection and range queries • B+-trees: most common indexing structure in databases • all actual values stored on leaf-nodes. • Optimality: • space O(N/B), updates O(log B (N/B)), queries O(log B (N/B)+K/B) • (B is the fan out of a node)

  3. B+Tree Example Order= 4 Root 100 120 150 180 30 3 5 11 120 130 180 200 100 101 110 150 156 179 30 35

  4. n=4 Full node min. node Non-leaf Leaf 120 150 180 30 3 5 11 30 35

  5. B+tree rules tree of order n (1) All leaves at same lowest level (balanced tree) (2) Pointers in leaves point to records except for “sequence pointer” (3) Number of pointers/keys for B+tree Max Max Min Min ptrs keys ptrs keys Non-leaf (non-root) n n-1 n/2 n/2- 1 Leaf (non-root) n n-1 (n-1)/2 (n-1)/2 Root n n-1 2 1

  6. Insert into B+tree (a) simple case • space available in leaf (b) leaf overflow (c) non-leaf overflow (d) new root

  7. 32 n=4 (a) Insert key = 32 100 30 3 5 11 30 31

  8. 7 3 5 7 n=4 (a) Insert key = 7 100 30 3 5 11 30 31

  9. 160 180 160 179 n=4 (c) Insert key = 160 100 120 150 180 180 200 150 156 179

  10. 30 new root 40 40 45 n=4 (d) New root, insert 45 10 20 30 1 2 3 10 12 20 25 30 32 40

  11. Deletion from B+tree (a) Simple case - no example (b) Coalesce with neighbor (sibling) (c) Re-distribute keys (d) Cases (b) or (c) at non-leaf

  12. 40 n=5 (b) Coalesce with sibling • Delete 50 10 40 100 10 20 30 40 50

  13. 35 35 n=5 (c) Redistribute keys • Delete 50 10 40 100 10 20 30 35 40 50

  14. new root 40 25 30 (d) Non-leaf coalesce • Delete 37 n=5 25 10 20 30 40 25 26 30 37 1 3 10 14 20 22 40 45

  15. Selection Queries B+-tree is perfect, but.... to answer a selection query (ssn=10) needs to traverse a full path. In practice, 3-4 block accesses (depending on the height of the tree, buffering) Any better approach? Yes! Hashing • static hashing • dynamic hashing

  16. Hashing • Hash-based indexes are best for equalityselections. Cannot support range searches. • Static and dynamic hashing techniques exist; trade-offs similar to ISAM vs. B+ trees.

  17. Static Hashing • # primary pages fixed, allocated sequentially, never de-allocated; overflow pages if needed. • h(k) MOD N= bucket to which data entry withkey k belongs. (N = # of buckets) 0 h(key) mod N 1 key h N-1 Primary bucket pages Overflow pages

  18. Static Hashing (Contd.) • Buckets contain data entries. • Hash fn works on search key field of record r. Use its value MOD N to distribute values over range 0 ... N-1. • h(key) = (a * key + b) usually works well. • a and b are constants; lots known about how to tune h. • Long overflow chainscan develop and degrade performance. • extendable and LinearHashing: Dynamic techniques to fix this problem.

  19. extendable Hashing • Situation: Bucket (primary page) becomes full. Why not re-organize file by doubling # of buckets? • Reading and writing all pages is expensive! • Idea: Use directory of pointers to buckets, double # of buckets by doubling the directory, splitting just the bucket that overflowed! • Directory much smaller than file, so doubling it is much cheaper. Only one page of data entries is split. Nooverflowpage! • Trick lies in how hash function is adjusted!

  20. Example • Directory is array of size 4. • Bucket for record r has entry with index = `global depth’ least significant bits of h(r); • If h(r) = 5 = binary 101, it is in bucket pointed to by 01. • If h(r) = 7 = binary 111, it is in bucket pointed to by 11. 2 LOCAL DEPTH Bucket A 16* 4* 12* 32* GLOBAL DEPTH 2 1 Bucket B 00 5* 1* 7* 13* 01 2 10 Bucket C 10* 11 • we denote r by h(r). DIRECTORY

  21. Handling Inserts • Find bucket where record belongs. • If there’s room, put it there. • Else, if bucket is full, splitit: • increment local depth of original page • allocate new page with new local depth • re-distribute records from original page. • add entry for the new page to the directory

  22. 2 16* 4* 12* 32* 2 Bucket D Example: Insert 21, then 19, 15 • 21 = 10101 • 19 = 10011 • 15 = 01111 LOCAL DEPTH Bucket A GLOBAL DEPTH 2 2 1 Bucket B 00 5* 1* 7* 13* 21* 01 2 10 Bucket C 10* 11 DIRECTORY 19* 15* 7* DATA PAGES

  23. 3 3 LOCAL DEPTH 16* 32* 32* 16* GLOBAL DEPTH 3 2 2 16* 4* 12* 32* 1* 5* 21* 13* 000 Bucket B 001 2 010 10* 011 100 2 101 15* 7* 19* Bucket D 110 111 3 3 Bucket A2 4* 12* 20* 12* 20* Bucket A2 4* (`split image' of Bucket A) (`split image' Insert h(r)=20 (Causes Doubling) LOCAL DEPTH Bucket A GLOBAL DEPTH 2 2 Bucket B 5* 21* 13* 1* 00 01 2 10 Bucket C 10* 11 2 Bucket D 15* 7* 19* of Bucket A)

  24. Points to Note • 20 = binary 10100. Last 2 bits (00) tell us r belongs in either A or A2. Last 3 bits needed to tell which. • Global depth of directory:Max # of bits needed to tell which bucket an entry belongs to. • Local depth of a bucket: # of bits used to determine if an entry belongs to this bucket. • When does bucket split cause directory doubling? • Before insert, local depth of bucket = global depth. Insert causes local depth to become > global depth; directory is doubled by copying it overand `fixing’ pointer to split image page.

  25. 000 000 100 001 010 010 00 110 011 01 100 001 10 101 101 11 110 011 111 111 Directory Doubling • Why use least significant bits in directory? • Allows for doubling via copying! 6 = 110 6 = 110 3 3 2 2 00 1 1 6* 10 0 0 6* 6* 01 1 1 6* 11 6* 6* vs. Most Significant Least Significant

  26. Comments on extendable Hashing • If directory fits in memory, equality search answered with one disk access; else two. • 100MB file, 100 bytes/rec, 4K pages contains 1,000,000 records (as data entries) and 25,000 directory elements; chances are high that directory will fit in memory. • Directory grows in spurts, and, if the distribution of hash values is skewed, directory can grow large. • Multiple entries with same hash value cause problems! • Delete: If removal of data entry makes bucket empty, can be merged with `split image’. If each directory element points to same bucket as its split image, can halve directory.

  27. Extendable Hashing vs. Other Schemes • Benefits of extendable hashing: • Hash performance does not degrade with growth of file • Minimal space overhead • Disadvantages of extendable hashing • Extra level of indirection to find desired record • Bucket address table may itself become very big (larger than memory) • Cannot allocate very large contiguous areas on disk either • Solution: B+-tree structure to locate desired record in bucket address table • Changing size of bucket address table is an expensive operation • Linear hashing is an alternative mechanism • Allows incremental growth of its directory (equivalent to bucket address table) • At the cost of more bucket overflows

  28. Comparison of Ordered Indexing and Hashing • Cost of periodic re-organization • Relative frequency of insertions and deletions • Is it desirable to optimize average access time at the expense of worst-case access time? • Expected type of queries: • Hashing is generally better at retrieving records having a specified value of the key. • If range queries are common, ordered indices are to be preferred • In practice: • PostgreSQL supports hash indices, but discourages use due to poor performance • Oracle supports B+trees, static hash organization, but not hash indices • SQLServer supports only B+-trees

  29. Bitmap Indices • Bitmap indices are a special type of index designed for efficient querying on multiple keys • Very effective on attributes that take on a relatively small number of distinct values • E.g. gender, country, state, … • E.g. income-level (income broken up into a small number of levels such as 0-9999, 10000-19999, 20000-50000, 50000- infinity) • A bitmap is simply an array of bits • For each gender, we associate a bitmap, where each bit represents whether or not the corresponding record has that gender.

  30. Bitmap Indices (Cont.) • In its simplest form a bitmap index on an attribute has a bitmap for each value of the attribute • Bitmap has as many bits as records • In a bitmap for value v, the bit for a record is 1 if the record has the value v for the attribute, and is 0 otherwise

  31. Bitmap Indices (Cont.) • Bitmap indices are useful for queries on multiple attributes • not particularly useful for single attribute queries • Queries are answered using bitmap operations • Intersection (and) • Union (or) • Complementation (not) • Each operation takes two bitmaps of the same size and applies the operation on corresponding bits to get the result bitmap • E.g. 100110 AND 110011 = 100010 100110 OR 110011 = 110111 NOT 100110 = 011001 • Males with income level L1: • And’ing of Males bitmap with Income Level L1 bitmap • 10010 AND 10100 = 10000 • Can then retrieve required tuples. • Counting number of matching tuples is even faster

  32. Bitmap Indices (Cont.) • Bitmap indices generally very small compared with relation size • E.g. if record is 100 bytes, space for a single bitmap is 1/800 of space used by relation. • If number of distinct attribute values is 8, bitmap is only 1% of relation size • Deletion needs to be handled properly • Existence bitmap to note if there is a valid record at a record location • Needed for complementation • not(A=v): (NOT bitmap-A-v) AND ExistenceBitmap • Should keep bitmaps for all values, even null value • To correctly handle SQL null semantics for NOT(A=v): • intersect above result with (NOT bitmap-A-Null)

  33. Efficient Implementation of Bitmap Operations • Bitmaps are packed into words; a single word and (a basic CPU instruction) computes and of 32 or 64 bits at once • E.g. 1-million-bit maps can be and-ed with just 31,250 instruction • Counting number of 1s can be done fast by a trick: • Use each byte to index into a precomputed array of 256 elements each storing the count of 1s in the binary representation • Can use pairs of bytes to speed up further at a higher memory cost • Add up the retrieved counts • Bitmaps can be used instead of Tuple-ID lists at leaf levels of B+-trees, for values that have a large number of matching records • Worthwhile if > 1/64 of the records have that value, assuming a tuple-id is 64 bits • Above technique merges benefits of bitmap and B+-tree indices

  34. Index Definition in SQL • Create a B-tree index (default in most databases) create index <index-name> on <relation-name> (<attribute-list>) -- create index b-index on branch(branch_name) -- create index ba-index on branch(branch_name, account) -- concatenated index -- create index fa-index on branch(func(balance, amount)) – function index • Use create unique index to indirectly specify and enforce the condition that the search key is a candidate key. • Hash indexes: not supported by every database (but implicitly in joins,…) • PostgresSQL has it but discourages due to performance • Create a bitmap index create bitmap index <index-name> on <relation-name> (<attribute-list>) • For attributes with few distinct values • Mainly for decision-support(query) and not OLTP (do not support updates efficiently) • To drop any index drop index <index-name>

  35. End of Chapter

  36. Partitioned Hashing • Hash values are split into segments that depend on each attribute of the search-key. (A1, A2, . . . , An) for n attribute search-key • Example: n = 2, for customer, search-key being (customer-street, customer-city) search-key value hash value(Main, Harrison) 101 111 (Main, Brooklyn) 101 001 (Park, Palo Alto) 010 010 (Spring, Brooklyn) 001 001 (Alma, Palo Alto) 110 010 • To answer equality query on single attribute, need to look up multiple buckets. Similar in effect to grid files.

  37. Grid Files • Structure used to speed the processing of general multiple search-key queries involving one or more comparison operators. • The grid file has a single grid array and one linear scale for each search-key attribute. The grid array has number of dimensions equal to number of search-key attributes. • Multiple cells of grid array can point to same bucket • To find the bucket for a search-key value, locate the row and column of its cell using the linear scales and follow pointer

  38. Example Grid File for account

  39. Queries on a Grid File • A grid file on two attributes A and B can handle queries of all following forms with reasonable efficiency • (a1 A  a2) • (b1  B  b2) • (a1 A  a2  b1  B  b2),. • E.g., to answer (a1 A  a2  b1  B  b2), use linear scales to find corresponding candidate grid array cells, and look up all the buckets pointed to from those cells.

  40. Grid Files (Cont.) • During insertion, if a bucket becomes full, new bucket can be created if more than one cell points to it. • Idea similar to extendable hashing, but on multiple dimensions • If only one cell points to it, either an overflow bucket must be created or the grid size must be increased • Linear scales must be chosen to uniformly distribute records across cells. • Otherwise there will be too many overflow buckets. • Periodic re-organization to increase grid size will help. • But reorganization can be very expensive. • Space overhead of grid array can be high. • R-trees (Chapter 23) are an alternative

  41. Linear Hashing • A dynamic hashing scheme that handles the problem of long overflow chains without using a directory. • Directory avoided in LH by using temporary overflow pages, and choosing the bucket to split in a round-robin fashion. • When any bucket overflows split the bucket that is currently pointed to by the “Next” pointer and then increment that pointer to the next bucket.

  42. Linear Hashing – The Main Idea • Use a family of hash functions h0, h1, h2, ... • hi(key) = h(key) mod(2iN) • N = initial # buckets • h is some hash function • hi+1 doubles the range of hi (similar to directory doubling)

  43. Linear Hashing (Contd.) • Algorithm proceeds in `rounds’. Current round number is “Level”. • There are NLevel (= N * 2Level) buckets at the beginning of a round • Buckets 0 to Next-1 have been split; Next to NLevelhavenot been split yet this round. • Round ends when allinitial buckets have been split (i.e. Next = NLevel). • To start next round: Level++; Next = 0;

  44. LH Search Algorithm • To find bucket for data entry r, findhLevel(r): • If hLevel(r) >= Next (i.e., hLevel(r) is a bucket that hasn’t been involved in a split this round)then r belongs in that bucket for sure. • Else, r could belong to bucket hLevel(r)or bucket hLevel(r) + NLevelmust apply hLevel+1(r) to find out.

  45. 32* 44* 36* 9* 5* 25* 30* 10* 14* 18* 31* 35* 7* 11* Example: Search 44 (11100), 9 (01001) Level=0, Next=0, N=4 h h 0 1 000 00 001 01 10 010 011 11 PRIMARY (This info is for illustration only!) PAGES

  46. h OVERFLOW h PRIMARY 0 1 PAGES PAGES 32* 000 00 9* 5* 25* 001 01 30* 10* 14* 18* 10 010 (This info is for illustration only!) 31* 35* 7* 11* 43* 011 11 100 44* 36* 00 Example: Search 44 (11100), 9 (01001) Level=0, Next = 1, N=4

  47. Linear Hashing - Insert • Find appropriate bucket • If bucket to insert into is full: • Add overflow page and insert data entry. • Split Nextbucket and increment Next. • Note: This is likely NOT the bucket being inserted to!!! • to split a bucket, create a new bucket and use hLevel+1 to re-distribute entries. • Since buckets are split round-robin, long overflow chains don’t develop!

  48. 32* 44* 36* 9* 5* 25* 30* 10* 14* 18* 31* 35* 7* 11* Example: Insert 43 (101011) Level=0, N=4 h h Next=0 0 1 000 00 Level=0 Next=1 001 01 h OVERFLOW h PRIMARY 10 010 0 1 PAGES PAGES 32* 000 00 011 11 9* 5* 25* 001 01 PRIMARY (This info is for illustration only!) PAGES 30* 10* 14* 18* 10 010 (This info is for illustration only!) 31* 35* 7* 11* 43* 011 11 100 44* 36* 00

  49. PRIMARY OVERFLOW h h PAGES 0 1 PAGES Next=0 00 000 32* 001 01 9* 25* 10 010 50* 10* 18* 66* 34* 011 11 35* 11* 43* 100 00 44* 36* 101 11 5* 29* 37* 14* 22* 30* 110 10 31* 7* 11 111 Example: End of a Round Level=1, Next = 0 Insert 50 (110010) Level=0, Next = 3 PRIMARY OVERFLOW PAGES h PAGES h 1 0 32* 000 00 9* 25* 001 01 66* 10 18* 10* 34* 010 Next=3 43* 11* 7* 31* 35* 011 11 44* 36* 100 00 5* 37* 29* 101 01 14* 30* 22* 110 10

More Related