1 / 42

Access Paths

Access Paths. Chapter 20. Types of Associative Access Paths. Primary index: Given the primary key value, find the tuple. Secondary index: Given the value of a non-unique attribute, find all qualified tuples.

tawana
Download Presentation

Access Paths

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. Access Paths Chapter 20

  2. Types of Associative Access Paths • Primary index: Given the primary key value, find the tuple. • Secondary index: Given the value of a non-unique attribute, find all qualified tuples. • Range index: Given the value range of some attribute, find all tuples within that range. • Structure index: Given some tuple, find all structurally related tuples (CODASYL sets, object hierarchies, etc.) ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  3. Two Important Techniques Two basic techniques dominate in modern DBMSs: • Hashing: Use a fixed transformation algorithm to convert the attribute value into a database address. • Tree search:A dynamical search structure is built that guides the search for a given attribute value to the proper database location. Hashing supports primary indices only. Tree search is more versatile. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  4. The Access Gap • Accessing a tuple in buffer costs ca. 2000 instr. Accessing it on disk takes 25 ms for I/O-related activities. On a 20 MIPS machine, this translates into 500,000 instructions. • Therefore, one can spend many instructions on an algorithm that saves one I/O on the average. • For access paths, the dominant cost measure is the number of different pages that need to be accessed during the search. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  5. Hashing ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  6. Folding vs. Hashing • Folding is used to turn an attribute value of arbitrary length and arbitrary data type in to an unsigned integer the maximum length of which is determined by the instruction set. • Hashing is used to transform the result of folding into the address of a page that (probably) holds the tuple with the specified key value. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  7. Requirement A good hash function H has to map primary key values, which are very unevenly distributed over a large value range, into a tuple address space that is much smaller (proportional to the number of existing tuples), such that the resulting addresses are evenly distributed over the address range. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  8. Parameters of the Hash Function • K: This is the (folded) key value. It varies between 0 and 2**32 - 1. • B: Number of pages to be allocated for the file. Depends on the number of tuples expected. • H: Hash function that performs a mapping of: (0, 2**32-1) -> (0, B-1). ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  9. Consequences of the Approach • Contiguous allocation: All B pages must allocated in physical contiguity, because the relative addresses vary between 0 and B-1. • Fixed size: The file size must be determined upon creation time, because changing the size (i.e. changing B) means changing the hash function. This in turn requires a complete reorganization. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  10. Requirements for a Hash Function • Hash-based allocation assumes that it is possible to estimate the number of tuples T that the relation will have, and that this estimate is not drastically exceeded. • If a block has length B, and a tuple has an average length of L bytes, then we need at least S = é T / (ëB/Lû) ù blocks to store the T tuples. • The required number of blocks (S) is allocated before the first tuple is stored. It is a good idea to allocate some more blocks (S’ > S) to allow for unexpected growth. • Then a hash function H is defined, which takes in the value of the primary key k of the relation and converts it into a number between 1 and S’; this is the block number where the tuple is to be stored. If K is the set of possible values for the primary key we have: H: K ® {1, 2, …, S’} • The set of potential values for the primary key attribute will be much larger than the number of blocks allocated (think, e.g., of ISBNs for the books relation). So the hash function is a compacting function. For each primary key value, there is exactly one block it is mapped to. Many different primary key values are mapped to the same block (n:1 relationship). ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  11. Properties of Hash Functions • A hash function must be easy to compute and must not require access to any blocks in the database. • It must be able to mapa generally very large set of potential primary key values (remember: primary keys can be constructed by concatenating several attributes of a relation) into a comparatively small set of block numbers in which the tuples will be stored. • It must be able to take in primary key values of different data types (integer, binary, decimal, character, etc.) and map them to the set of integers between 1 and S’ with equal efficiency. • The formula for estimating S based on the number of tuples, average tuple length and block length implicitly assumes that all blocks are equally filled, i.e. that the same number of tuples is mapped to each block. This is the most difficult requirement, because the primary key values in general are not equally distributed over their value range: Some parts of the value range are used, others are not used at all, keys are generated by some regular mechanism, etc. • To achieve this “hashing” property, different methods exist: table look-up, base conversion, folding, encryption, division by prime numbers, etc. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  12. A Popular Hash Function • A hash function that most database systems use as a default (if the user does not specify one) is defined as: H(k) := k mod d + 1 • This requires that k is a positive integer. If the primary key attribute of the relation does not have the data type integer - it could, for example, be a name - then it has to be turned into an integer first. The usual way of doing this is to “fold” the binary representation of the key value such that its length does not exceed 32 bits. Then these 32 bits are interpreted as an integer; if it is negative, it is multiplied by -1 one. Details of folding are omitted here. • For H to be a good hash function, d must be a large enough prime number; this is explained by detailed number theoretic analyses. We also must make sure that the number of blocks allocated is about 25% larger than the minimum requirement. • Summing it up: We first compute S. Then we compute S’ = 1.25*S. Then we compute d = next_higher_prime (S’). H(k) will then determine the block for each tuple based on the primary key value. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  13. Average Number of Overflow Pages ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  14. Hashing for Non-Unique Attributes Let V denote the number of different attribute values. Then we can distinguish three cases: • V ~ T: The attribute is “almost” unique; a good hash function should work in that case. • V > B: There are more values than buckets. Can be made work, but some buckets may get much higher utilization than others. • V < B: This is the case where hashing cannot be used. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  15. Overflow Handling ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  16. Hashing: Summary • For unique attributes, hashing can yield one-access retrieval. • It is critical to find a good hash function to reduce collisions. • If the original estimate of the file size is wrong, reorganization is inevitable. • Synchronization at the page level is done using standard crabbing techniques. • Hashing does not support range queries. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  17. B-Trees B-Trees consist of two types of nodes: Leaf nodes: The contain the data, i.e. the tuples or pointers to the tuples (TIDs). Index nodes: Index nodes contain reference keys to direct the search towards the leaves. The data structure looks like this: struct { char * KeyValue; PAGEID PointerToNextNode; } index_node_structure[ ]; ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  18. Rules for Index Nodes • Key values are in sorted order: K0 £ K1 £ ... £Ki £ ... £ Kf (f is max. capacity of a node). • For any two adjacent key values Ki, Ki+1 the pointer Pi points to a node covering all values in the interval (Ki, Ki+1]. • If a search for value v arrives at an index node, the next node to be visited is pointed to by Pi such that Ki £ v < Ki+1. • K0 is an arbitrary “low value” (smaller than anything else in that node). ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  19. Properties of a B-Tree • Parameter f is called the fan-out of the tree. • The number of nodes visited from the root to a leaf is called the height of the tree. • A B-tree is always perfectly balanced, i.e. the height is the same for all leaves. • Storage utilization is at least 50% for all nodes except for the root. • Average storage utilization is close to 70%. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  20. A Simple B-Tree ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  21. Some Observations • B-trees also work for non-unique attributes; implementational optimizations will be discussed later on. • The reference keys in the index nodes can be different from all “real” key values in the leaves; they only have to guide the search correctly. • The key values at the leaf level are sorted in ascending order; this supports range queries. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  22. Inserting Into a B-Tree ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  23. Growing a B-Tree • If the insert leaf is full, allocate a new node, distribute the values (the new one sorted in place) evenly across the old leaf and the new node, move the lowest key value of the new node up to the index node. • If that index node is full, split it in the same way. • If the root has to be split: Allocate two new nodes, distribute the key values evenly over them, put the reference key in the root. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  24. Deleting Tuples From a B-Tree • To maintain the space utilization guarantees, a leaf that becomes under-utilized (< 50%) would have to be merged with its neighbours. • This is a very costly operation; in particular, synchronization at the page level is very complicated. • Therefore, most systems let nodes become empty and discard them when that happens. • Analyses show that this does not deteriorate the overall B-tree performance. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  25. Non-Unique Attributes ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  26. The Basic Formula of B-Tree-Performance With the N: number of tuples, C: average number of entries in a leaf, F: average number of entries in an index node, the height H of a B-tree is H = 1 + élog (é N/Cù)ù F ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  27. Some Performance Figures ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  28. Tuples in the Leaves? Assuming a tuple is x times longer than a TID, we get the following estimate: 1 + logF (N/(x•C)) + 1.1 £ 1 + logF (N/C). This transforms into 1.1 £ logF x When this holds, moving the tuples out of the leaves improves performance. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  29. Key Compression: Suffix Compression ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  30. Synchronization on B-Trees: What Is the Problem? • B-Trees are fully redundant structures, which can be reconstructed from the tuples; therefore, no synchronization should be required at all. • However, some queries operate on the index only. This requires all operations on B-trees to be serializable with the operations on the tuples. • Standard two-phase locking with the nodes as the objects is not feasible for performance reasons. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  31. This is an example of the crabbing technique Protecting Tree Traversal 1. semaphore on Q Node Q at level i search path 2. follow search path 3. semaphore on R Node R at level i+1 4. release sem. on Q ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  32. B-Trees and Value Locks ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  33. Making Lock Names To implement value locking, we need to build lock names according to the following rule: LockN := {TableName, IndexName, KeyValue}. KeyValue in turn is a composite: KeyValue := {AttributeValue, TupleIdentifier}. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  34. Key Range Locking an B-Trees Simple retrieval: k = c Get a semaphore on leaf page; get S-lock on key range defined by largest existing value c1 with c1 £ c; hold lock until commit. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  35. Key Range Locking an B-Trees Range retrieval: c1 < k < c2 Get s semaphore on first leaf page; get S-lock on key defined by largest existing value c3 with c3 < c1; proceed sequentially along leaf level; request key range S-lock for each new attribute value up to and including c2; do careful crabbing across leaf pages; hold S-lock until commit. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  36. Key Range Locking an B-Trees Insert: [c, ki] Get X-semaphore on leaf page; find largest existing value c1 with c1 < c; request instant IX-lock on c1 ; request long X-lock on c. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  37. Key Range Locking an B-Trees Delete: [c, kd] Get X-semaphore on leaf page; find largest existing value c1 with c1 < c; request long IX-lock on c; else request long X-lock on c and c1. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  38. B-Tree Recovery ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  39. B-Tree Recovery Based on Physiological Logging • Cover all B-tree operations with semaphores on all affected pages. • For each logical update a log record with the logical UNDO operation must be moved to the log • While the update operation is being performed, physical REDO log records are written. • After all REDO records are safely in the log, the exclusive semaphores can be released. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  40. The Two Phases of B-Tree-Recovery Phase1: Go forward through the log up to its current end, applying all REDO records to the tree. Phase2: Go backward to the Begin of transaction record of the oldest incomplete transaction, executing the UNDO operations on the tree for all losers along the way. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  41. Other Access Path Methods:Extendible Hashing ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

  42. New Techniques Grid files: Symmetric multi-dimensional point access. Can become very unbalanced depending on correlation in the data. R-Trees: Symmetric multi-dimensional access. Can deteriorate depending on insertion strategy. hb-Tree: Symmetric multi-dimensional access. Can turn into a DAG depending on deletion order. ãJim Gray, Andreas Reuter Transaction Processing - Concepts and Techniques WICS August 2 - 6, 1999

More Related