Query Evaluation Techniques for Larger Databases**

Query Evaluation Techniques for Larger Databases** By Goetz Graefe Elaborado por: Edwin Andrés Bernal López Claudia Jeanneth Becerra Cortés Curso: Tópicos Avanzados de Bases de Datos Bogotá, Marzo 23 del 2006 **Portland State University, Computer Science Department, P. O. Box751, Portland, Oregon 97207-0751, Received January 1992, final revision accepted February 1993, Published ACM Computing Surveys, Vol. 25, No 2, June 1993.

Lista de Publicaciones de Goetz Graefehttp://www.informatik.uni-trier.de/~ley/db/indices/a-tree/g/Graefe:Goetz.html

Estado del Arte en Query Processing/93 Bulletin of the Technical Committee on Data Engineering December, 1993 Vol. 16 No. 4 IEEE Computer Society Special Issue on Query Processing in Commercial Database Systems Letter from the Special Issue Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Goetz Graefe Query Optimization in the IBM DB2 Family . . . . . . . . . . . . . . . . . . Peter Gassner,and Guy Lohman Query Processing in the IBM Application System 400. . . . . . . . .Richard L. Cole, Mark J. Anderson Query Processing in NonStop SQL . . A. Chen, Y-F Kao, M. Pong, D. Shak, S. Sharma, J. Vaishnav Query Processing in DEC Rdb: Major Issues and Future Challenges . . . . . Gennady Antoshenkov Letter from the Editor-in-Chief “… Goetz Graefe, our issue editor, has succeeded in overcoming these difficulties. He has collected four papers from prominent database vendors. These papers introduce us to the inside world of ”real” query processing” Letter from the Special Issue Editor “…Second, in some aspects of query processing, the industrial reality has bypassed academic research. By asking leaders in the industrial field to summarize their work, I hope that this issue is a snapshot of the current state of the art. Undoubtedly, some researchers will find inspirations for new, relevant work of their own in these articles.”

Conferencias en VLDB

Contribuciones a SQL Server 7

Incorporación de “PIVOT” a SQL

Lista de Publicaciones de Goetz Graefehttp://www.informatik.uni-trier.de/~ley/db/indices/a-tree/g/Graefe:Goetz.html

Tabla de Contenido del Paper (1a. Pte) • INTRODUCTION • ARCHITECTURE OF QUERY EXECUTION ENGINES • SORTING AND HASHING • 2.1 Sorting • 2.2.Hashing • 3. DISK ACCESS • 3.1 File Scans • 3.2 Associative Access Using Indices • 3.3. Buffer Management • 4. AGGREGATION AND DUPLICATE REMOVAL • 4.1 Aggregation Algorithm Based on Nested Loops • 4.2 Aggregation Algorithms Based on Sortlng • 4.3. Aggregation Algorithms Based on Hashing • 4.4. A Rough Performance Comparison • 4.5. Additional Remarks on Aggregation • 5. BINARY MATCHING OPERATIONS • 5.1. Nested-Loops Join Algorithms • 5.2. Merge-Join Algorithms • 5.3. Hash Join Algorithms • 5.4. Pointer-Based Joins • 5.5. Rough Performance Comparison • 6. UNIVERSAL QUANTIFICATION • 7. DUALITY OF SORT- AND HASH-BASED QUERY PROCESSING ALGORITHMS

Tabla de Contenido del Paper (2a. Pte) • 8. EXECUTION OF COMPLEX QUERY PLANS • 9. MECHANISMS FOR PARALLEL QUERY EXECUTION • 9.1. Parallel versus Distributed Database Systems • 9.2 Forms of Parallelism • 9.3. Implementation Strategies • 9.4. Load Balancing and Skew • 9.5. Architectures and Architecture Independence • PARALLEL ALGORITHMS • 10.1 Parallel Selections and Updates • 10.2. Parallel Sorting • 10.3. Parallel Aggregation and Duplicate Removal • 10.4. Parallel Joins and Other Binary Matching Operations • 10.5. Parallel Universal Quantification • 11. NON STANDARD QUERY PROCESSING ALGORITHMS • 11.1. Nested Relations • 11.2. Temporal and Scientific Database Management • 11.3. Object-oriented Database Systems • 11.4. More Control Operators • 12. ADDITIONAL TECHNIQUES FOR PERFORMANCE IMPROVEMENT • 12.1 . Precomputatlon and Derived Data • 12.2. Data Compression • 12.3. Surrogate Processing • 12.4. Bit Vector Filtering • 12.5. Specialized Hardware • SUMMARY AND OUTLOOK

INTRODUCTION

INTROD.: Query Processing Steps [2]

1. ARCHITECTURE OF QUERY EXECUTION ENGINES

2. SORTING AND HASHING

Access Path Algorithm + data structure used to locate rows satisfying some condition • File scan: can be used for any condition • Hash: equality search; all search key attributes of hash index are specified in condition • B+ tree: equality or range search; a prefix of the search key attributes are specified in condition • Binary search: Relation sorted on a sequence of attributes and some prefix of sequence is specified in condition

2. ACCESS PATHS

Sorting and Hashing

General External Merge Sort • To sort a file with N pages using B buffer pages: • Pass 0: use B buffer pages. Produce sorted runs of B pages each. • Pass 2, …, etc.: merge B-1 runs.

Cost of External Merge Sort • Number of passes: • Cost = 2N * (# of passes) • E.g., with 5 buffer pages, to sort 108 page file: • Pass 0: = 22 sorted runs of 5 pages each (last run is only 3 pages) • Pass 1: = 6 sorted runs of 20 pages each (last run is only 8 pages) • Pass 2: 2 sorted runs, 80 pages and 28 pages • Pass 3: Sorted file of 108 pages

Number of Passes of External Sort

INPUT 1 INPUT 1' INPUT 2 OUTPUT INPUT 2' OUTPUT' b block size Disk INPUT k Disk INPUT k' B main memory buffers, k-way merge Double Buffering To reduce wait time for I/O request to complete, can prefetch into `shadow block’. • Potentially, more passes; in practice, most files still sorted in 2-3 passes.

Sorting Records! • Sorting has become a blood sport! • Parallel sorting is the name of the game ... • Datamation: Sort 1M records of size 100 bytes • Typical DBMS: 15 minutes • World record: 3.5 seconds • 12-CPU SGI machine, 96 disks, 2GB of RAM • New benchmarks proposed: • Minute Sort: How many can you sort in 1 minute? • Dollar Sort: How many can you sort for $1.00?

Using B+ Trees for Sorting • Scenario: Table to be sorted has B+ tree index on sorting column(s). • Idea: Can retrieve records in order by traversing leaf pages. • Is this a good idea? • Cases to consider: • B+ tree is clusteredGood idea! • B+ tree is not clusteredCould be a very bad idea!

Clustered B+ Tree Used for Sorting

Unclustered B+ Tree Used for Sorting

External Sorting vs. Unclustered Index • p: # of records per page • B=1,000 and block size=32 for sorting • p=100 is the more realistic value.

Query Evaluation

Relational Operations • We will consider how to implement: • Selection ( ) Selects a subset of rows from relation. • Projection ( ) Deletes unwanted columns from relation. • Join ( ) Allows us to combine two relations. • Set-difference ( ) Tuples in reln. 1, but not in reln. 2. • Union ( ) Tuples in reln. 1 and in reln. 2. • Aggregation (SUM, MIN, etc.) and GROUP BY • Since each op returns a relation, ops can be composed! After we cover the operations, we will discuss how to optimize queries formed by composing them.

Access Path Algorithm + data structure used to locate rows satisfying some condition • File scan: can be used for any condition • Hash: equality search; all search key attributes of hash index are specified in condition • B+ tree: equality or range search; a prefix of the search key attributes are specified in condition • Binary search: Relation sorted on a sequence of attributes and some prefix of sequence is specified in condition

1. ARCHITECTURE OF QUERY EXECUTION ENGINES

Access Paths • A tree index matches (a conjunction of) terms that involve only attributes in a prefix of the search key. • E.g., Tree index on <a, b, c> matches the selectiona=5 AND b=3, and a=5 AND b>6, but notb=3. • A hash index matches (a conjunction of) terms that has a term attribute = value for every attribute in the search key of the index. • E.g., Hash index on <a, b, c> matches a=5 AND b=3 AND c=5; but it does not matchb=3, or a=5 AND b=3, or a>5 AND b=3 AND c=5.

Access Paths Supported by B+ tree Example: Given a B+ tree whose search key is the sequence of attributes a2, a1, a3, a4 • Access path for search a1>5  a2=3.0  a3=‘x’ (R): find first entry having a2=3.0  a1>5  a3=‘x’ and scan leaves from there until entry having a2>3.0 . Select satisfying entries • Access path for search  a2=3.0  a3 >‘x’ (R): locate first entry having a2=3.0 and scan leaves until entry having a2>3.0 . Select satisfying entries • No access path for search  a1>5  a3 =‘x’ (R)

Choosing an Access Path • Selectivity of an access path refers to its cost • Higher selectivity means lower cost (#pages) • If several access paths cover a query, DBMS should choose the one with greatest selectivity • Size of domain of attribute is a measure of the selectivity of domain • Example:  CrsCode=‘CS305’  Grade=‘B’ - a B+ tree with search key CrsCode is more selective than a B+ tree with search key Grade

Computing Selection condition: (attr op value) • No index on attr: • If rows unsorted, cost = F • Scan all data pages to find rows satisfying the condition • If rows sorted on attr, cost = log2F + (cost of scan) • Use binary search to locate first data page containing row in which (attr = value) • Scan further to get all rows satisfying (attr op value)

Computing Selection condition: (attr op value) • B+ tree index on attr (for equality or range search): • Locate first index entry corresponding to a row in which (attr = value); cost = depth of tree • Clustered index - rows satisfying condition packed in sequence in successive data pages; scan those pages; cost depends on number of qualifying rows • Unclustered index - index entries with pointers to rows satisfying condition packed in sequence in successive index pages; scan entries and sort pointers to identify table data pages with qualifying rows, each page (with at least one such row) fetched once

Unclustered B+ Tree Index Index entries satisfying condition data page Data File B+ Tree

Computing Selection • Hash index on attr (for equality search only): • Hash on value; cost  1.2 (to account for possible overflow chain) to search the (unique) bucket containing all index entries or rows satisfying condition • Unclustered index - sort pointers in index entries to identify data pages with qualifying rows, each page (containing at least one such row) fetched once condition: (attr = value)

Complex Selections • Conjunctions: a1 =x a2 <y  a3=z (R) • Use most selective access path • Use multiple access paths • Disjunction:  (a1 =x or a2 <y) and (a3=z) (R) • DNS (disjunctive normal form) • (a1 =x  a3 =z) or (a2 < y  a3=z) • Use file scan if one disjunct requires file scan • If better access path exist, and combined selectivity is better than file scan, use the better access paths, else use a file scan

Two Approaches to General Selections • First approach:Find the most selective access path, retrieve tuples using it, and apply any remaining terms that don’t match the index: • Most selective access path: An index or file scan that we estimate will require the fewest page I/Os. • Terms that match this index reduce the number of tuples retrieved; other terms are used to discard some retrieved tuples, but do not affect number of tuples/pages fetched. • Consider day<8/9/94 AND bid=5 AND sid=3. A B+ tree index on day can be used; then, bid=5 and sid=3 must be checked for each retrieved tuple. Similarly, a hash index on <bid, sid> could be used; day<8/9/94 must then be checked.

Intersection of Rids • Second approach(if we have 2 or more matching indexes that use Alternatives (2) or (3) for data entries): • Get sets of rids of data records using each matching index. • Then intersect these sets of rids • Retrieve the records and apply any remaining terms. • Consider day<8/9/94 AND bid=5 AND sid=3. If we have a B+ tree index on day and an index on sid, both using Alternative (2), we can retrieve rids of records satisfying day<8/9/94 using the first, rids of recs satisfying sid=3 using the second, intersect, retrieve records and check bid=5.

Query Evaluation Techniques for Larger Databases**