indexing and hashing

1. indexing and hashing Azita Keshmiri CS 157B

2. Basic concept • An index for a file in a database system works the same way as the index in text book. • For example if we want to learn about a particular topic, we can search for the topic in the index at the back of the book, find the pages where it occurs, then read the pages to find information we are looking for.

3. Index • The words in the index are in sorted order. Making it easy to find the word we are looking for. • The index is smaller than the book.

4. For example Card catalogs in libraries worked in a similar way. The card is in alphabetic order by authors, one card for each author.

5. Index in database • Database system indices play the same role as book indices or card catalogs in libraries.

6. Example: • To retrieve an account record given the account number, the database system would look up an index to find on which disk block the corresponding record resides, and then fetch the disk, to get the account record. • Keeping a sorted list of account numbers would not work well on very large database with million of accounts.

7. There are two basic kinds of indices • Ordered indices: based on a sorted ordering of the values. • Hash indices: Based on a uniform distribution of value across a range of buckets.

8. There are several techniques for both ordered indexing and hashing. • Each technique must be evaluated on the basic factors: • Access types • Access time • Insertion time • Deletion time • Space overhead

9. Clustering indices • Primary index is an index whose search key also defines the sequential order of the file. A primary index may be parse. • Primary indices are called clustering indices.

10. Search-key • An attribute or set of attributes used to look up records in a file is called a search- key.

11. There are two types of ordered indices • Dense index: An index record appears for every search-key value in the file. • Sparse index: An index record appears for only some of the search-key values.

12. Dense index A – 217 Brighton 750 A-101 Downtown 500 A- 110 Downtown 600 A - 215 Mianus 700 A – 102 Perryridge 400 A – 201 Perryridge 900 A – 218 Perryridge 700 A- 222 Redwood 700 A- 305 Round Hill 350 Brighton Downtown Mianus Perryridge Redwood Round Hill

13. Sparse index Brighton Mianus Redwood A – 217 Brighton 750 A-101 Downtown 500 A- 110 Downtown 600 A - 215 Mianus 700 A – 102 Perryridge 400 A – 201 Perryridge 900 A – 218 Perryridge 700 A- 222 Redwood 700 A- 305 Round Hill 350

14. Index update • Every index must be updated whenever a record is either inserted into or deleted from the file.

15. Multilevel indices • Indices with two or more levels are called multilevel indices. A typical dictionary is an example of a multilevel index in the none database world.

16. Insertion • The system performs a lookup using the search key value that appears in the record to be inserted.

17. Deletion • To delete a record, the system first looks up the record to be deleted. The actions the system takes next (for both insertion and deletion) depends on weather the index is dense or sparse.

18. Secondary indices • Secondary indices must be dense, with an index entry for every search value, and a pointer to every record in the file. A secondary index on a candidate key looks just like a dense primary index, except that the records pointed to by successive value in the index are not sorted sequentially.

19. Secondary index on account file, on noncandidate key balance. A- 101 Downtown 500 A- 217 Brighton 750 A- 110 Downtown 600 A- 215 Mianus 700 A- 102 Perryridge 400 A- 201 Perryridge 900 A- 218 Perryridge 700 A- 222 Redwood 700 A- 305 Round Hill 350 350 400 500 600 700 750 900

20. B+ tree index files • The main advantage of the index-sequential file organization is that performance degrades as the file grows, both for the index lookups and for sequential scans through the data.

21. B+ tree cont. • A B+ tree index takes the form of a balanced tree in which every path from the root of the tree to a leaf of the tree is of the same length. • Each nonleaf node in the tree has between [n/2] and n children, where n is fixed for a particular tree.

22. Structure of a B+ tree • A B+ tree index is a multilevel index; however its structure differs from that of the multilevel index- sequential file. • Node of B+ tree contains up to n-1 search key values K1, K2, ….Kn-1, and n pointers P1, P2,…Pn. • Search key values within a node are kept in sorted order. If i < j, then ki < kj

23. Cont • Consider first the structure of the leaf node For i= 1, 2,…, n-1, pointer Pi points to either a file record with search-key value Ki. Bucket structure is used only if the search key does not form a primary key, and if file is not sorted in the search-key value order.

24. Cont • Consider one leaf node of a B+ tree for the account file, in which we have chosen n to be 3, and the search key is branch-name. Since the account file is ordered by branch-name, the pointers in the leaf node point directly to the file.

25. A leaf node for account B+ tree index (n=3) Brighton Downtown A – 101Downtown 500 A –110 Downtown 600 A – 212Brighton 750 A –110 Downtown 600 A –110 Downtown 600

26. B+ tree for account file (n=3) Perryridge Mianus Redwood Brighton Downtown Mianus Perryridge Redwood Round Hill

27. B+ tree for account file with n = 5 Perryridge Brighton Downtown Mianus Perryridge Redwood Round Hill

28. The use of the pointer • Since there is a linear order on the leaves based on the search-key values that they contain, we use Pn to chain together the leaf nodes in search-key order. • This ordering allows for efficient sequential processing of the file.

29. B+ tree • The nonleaf nodes of the B+ tree form a multilevel (sparse) index on the leaf nodes. The structure of nonleaf nodes is the same as that for leaf nodes, except that all pointers are pointers to tree nodes.

30. Fanout of node • A nonleaf node may hold up to n pointers, and must hold at least [n/2] pointers. • The number of pointers in a node is called the fanout of the node.

31. B+ tree • B in B+ tree stands for “balanced”. This property is a requirement for a B+ tree. B+ trees are all balanced, the length of every path from the root to a leaf node is the same. • It is the balance property of B+ trees that ensures good performance for lookup, insertion, and deletion.

32. Updates on B+ trees • Insertion and deletion are more complicated than look up, since it may be necessary to split a node that becomes too large as the result of an insertion or to coalesce nodes (combine nodes) if a node becomes too small (fewer than [n/2] pointers). • when a node is split or a pair of nodes is combined we must ensure that balance is preserved.

33. Insertion • First we find the leaf node in which the search-key value would appear. • If search-key value already appears in leaf node, add new record to the file. If necessary add to the bucket a pointer to record. • If search-key value doesn’t appear, insert the value in the leaf node, and position it such that search keys are still in order. Then insert the new record in file. If necessary create a new bucket with the appropriate pointer.

34. Deletion • For deletion we find the record to be deleted, and remove it from the file. Remove search-key value from the leaf node if there is no bucket associated with that search-key value or if the bucket becomes empty as a result of deletion.

35. B tree index files • B-tree indices are similar to B+ tree indices. • The primary distinction between the two approaches is that a B-tree eliminates the redundant storage of search-key values. • A B tree allows the same search-key value to appear only once.

36. Look up in B and B+ tree • The number of nodes accessed in a lookup in a B-tree depends on where the search-key is located. • A look up on a B+tree requires a traversal of a path from the root of the tree to some leaf node.

37. Deletion in B and B+ tree • In a B+ tree, the deleted entry always appears in a leaf. • In a B-tree, the deleted entry may appear in nonleaf node. • The proper value must be selected as a replacement from the subtree of the node containing the deleted entry.

38. disadvantage of sequential file organization • One disadvantage of sequential file organization is that we must access an index structure to locate data, or use binary search, and that result in more I/O operations.

39. Hashing • File organization based on the technique of hashing allow us to avoid accessing an index structure. • Hashing also provides a way of constructing indices.

40. Hash file organization • In a hash file organization, we obtain the address of the disk block containing a desired record directly by computing a function on the search-key value of the record.

41. Hash function • Consider K to be set of all search-key values, and let B denote the set of all bucket addresses. A hash function h is a function from K to B.

42. Hash function for branch- name Branch-name h(branch-name) Brighton 0010 1101 1111 1011 0010 1100 0011 0000 Downtown 1010 0011 1010 0000 1100 0110 1001 1111 Mianus 1100 0111 1110 1101 1011 1111 0011 1010 Perryridge 1111 0001 0010 0100 1001 0011 0110 1101 Redwood 0011 0101 1010 0110 1100 1001 1110 1011 Round Hill 1101 1000 0011 1111 1001 1100 0000 0001

43. Cont • To insert a record with search key Ki, we compute h(Ki), which gives the address of the bucket for that record. • Assume there is space in the bucket to store the record. Then the record is stored in that bucket. • To perform a lookup on a search-key value Ki, we compute h(Ki) then search the bucket with that address.

44. Example • Suppose two search-keys, K5 and K7, have the same hash value; that is, h(K5) = h(K7). If we perform a lookup on K5, the bucket h(K5) contains records with search-key value K5 and records with search-key values K7. We have to check the search-key value of every record in the bucket to verify that the record is one that we want.

45. Deletion • If search-key value of the record to be deleted is Ki, we compute h(Ki), then search the corresponding bucket for that record, and delete the record from the bucket.

46. Bucket • The term bucket used for unit of storage that can store one or more records. • A bucket is typically a disk block, but could be chosen to be smaller or larger than a disk block.

47. Hash function • Hash function distributes the stored keys uniformly across all the buckets, so every bucket has the same number of records. • The worse possible hash function maps all search-key values to the same bucket. • Such a function is undesirable because all the records have to be kept in the same bucket. • A lookup has to check every record to find the one desired.

48. Distribution qualities • Distribution is random: when the average case, each bucket will have nearly the same number of values assigned to it, regardless of the actual distribution of search-key values.

49. Cont • Distribution is uniform: when hash function assigns each bucket the same number of search-key values from the set of all possible search-key values.