1 / 15

Quick Review of Apr 17 material

Quick Review of Apr 17 material. Multiple-Key Access There are good and bad ways to run queries on multiple single keys Indices on Multiple Attributes Combining two keys into a single concatenated attribute Grid files crude array with one dimension and linear scale for each attribute

rona
Download Presentation

Quick Review of Apr 17 material

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. Quick Review of Apr 17 material • Multiple-Key Access • There are good and bad ways to run queries on multiple single keys • Indices on Multiple Attributes • Combining two keys into a single concatenated attribute • Grid files • crude array with one dimension and linear scale for each attribute • more than one cell may point to a given bucket of values • array grid may be dynamically resized during use • Other alternatives are spatial databases: R-tree, quad-trees, k-d tree • Bitmap Indices • linear array of bits: bit j is set if tuple j has the attribute that this bitmap tracks (e.g., “Moonroof”: bit j is 1 if record j is a car with moonroof) • Queries are answered by combining several bitmaps using and, or, not

  2. Today • HW #4: due Thursday April 24 (next class) • Questions: 12.11, 12.12, 12.13, 12.16 • No HW for next week • Today: • Start Chapter 13: Query Processing

  3. Query Processing • SQL is good for humans, but not as an internal (machine) representation of how to calculate a result • Processing an SQL (or other) query requires these steps: • parsing and translation • turning the query into a useful internal representation in the extended relational algebra • optimization • manipulating the relational algebra query into the most efficient form (one that gets results the fastest) • evaluation • actually computing the results of the query

  4. Query Processing Diagram

  5. Query Processing Steps 1. parsing and translation • details of parsing are covered in other places (texts and courses on compilers). We’ve already covered SQL and relational algebra; translating between the two should be relatively familiar ground 2. optimization • This is the meat of chapter 13. How to figure out which plan, among many, is the best way to execute a query 3. evaluation • actually computing the results of the query is mostly mechanical (doesn’t require much cleverness) once a good plan is in place.

  6. Query Processing Example • Initial query: select balance from account where balance<2500 • Two different relational algebra expressions could represent this query: • sel balance<2500(Pro balance(account)) • Pro balance( sel balance<2500(account)) • which choice is better? It depends upon metadata (data about the data) and what indices are available for use on these operations.

  7. Query Processing Metadata • Cost parameters(some are easy to maintain; some are very hard -- this is statistical info maintained in the system’s catalog) • n(r ): number of tuples in relation r • b(r ): number of disk blocks containing tuples of relation r • s(r ): average size of a tuple of relation r • f(r ): blocking factor of r: how many tuples fit in a disk block • V(A,r): number of distinct values of attribute A in r. (V(A,r)=n(r ) if A is a candidate key) • SC(A,r): average selectivity cardinality factor for attribute A of r. Equivalent to n(r )/V(A,r). (1 if A is a key) • min(A,r): minimum value of attribute A in r • max(A,r): maximum value of attribute A in r

  8. Query Processing Metadata (2) • Cost parameters are used in two important computations: • I/O cost of an operation • the size of the result • In the following examination we’ll find it useful to differentiate three important operations: • Selection (search) for equality (R.A1=c) • Selection (search) for inequality (R.A1>c) (range queries) • Projection on attribute A1

  9. Selection for Equality (no indices) • Selection (search) for equality (R.A1=c) • cost (sequential search on a sorted relation) = b(r )/2 average unsuccessful b(r )/2 + SC(A1,r) -1 average successful • cost (binary search on a sorted relation) = log b(r ) average unsuccessful log b(r ) + SC(A1,r) -1 average successful • size of the result n(select(R.A1=c)) = SC(A1,r) = n(r )/V(A1,r)

  10. Selection for Inequality (no indices) • Selection (search) for inequality (R.A1>c) • cost (file unsorted) = b(r ) • cost (file sorted on A1) = b(r )/2 + b(r )/2 (if we assume that half the tuples qualify) b(r ) in general (regardless of the number of tuples that qualify. Why?) • size of the result = depends upon the query; unpredictable

  11. Projection on A1 • Projection on attribute A1 • cost = b(r ) • size of the result n(Pro(R,A1)) = V(A1,r)

  12. Selection (Indexed Scan) for Equality Primary Index on key: cost = (height+1) unsuccessful cost = (height+1) +1 successful Primary (clustering) Index on non-key: cost = (height+1) + SC(A1,r)/f(r ) all tuples with the same value are clustered Secondary Index cost = (height+1) + SC(A1,r) tuples with the same value are scattered

  13. Selection (Indexed Scan) for Inequality Primary Index on key: search for first value and then pick tuples >= value cost = (height+1) +1+ size of the result (in disk pages) = height+2 + n(r ) * (max(A,r)-c)/(max(A,r)-min(A,r))/f(r ) Primary (clustering) Index on non-key: cost as above (all tuples with the same value are clustered) Secondary (non-clustering) Index cost = (height+1) +B-treeLeaves/2 + size of result (in tuples) = height+1 + B-treeLeaves/2 + n(r ) * (max(A,r)-c)/(max(A,r)-min(A,r))

  14. Complex Selections • Conjunction (select where theta1 and theta2) (s1 = # of tuples satisfying selection condition theta1) combined SC = (s1/n(r )) * (s2/n(r )) = s1*s2/n(r )2 assuming independence of predicates • Disjunction (select where theta1 or theta2) combined SC = 1 - (1 - s1/n(r )) * (1 - s2/n(r )) = s1/n(r )) + s2/n(r ) - s1*s2/n(r )2 • Negation (select where not theta1) n(! Theta1) = n(r ) - n(Theta1)

  15. Complex Selections with Indices GOAL: apply the most restrictive condition first and combined use of multiple indices to reduce the intermediate results as early as possible • Why? No index will be available on intermediate results! • Conjunctive selection using one index B: • select using B and then apply remaining predicates on intermediate results • Conjunctive selection using a composite key index (R.A1, R.A2): • create a composite key or range from the query values and search directly (range search on the first attribute (MSB of the composite key) only) • Conjunctive selection using two indices B1 and B2: • search each separately and intersect the tuple identifiers (TIDs) • Disjunctive selection using two indices B1 and B2: • search each separately and union the tuple identifiers (TIDs)

More Related