Physical structures and QP optimization

Page 1

This is a selection of exercises on Physical structures and QP optimization 2015 2015/09/30 - Actors and Roles A table Role(Actor, Movie, Character) records 400K roles played by Hollywood actors in over many decades. Estimate the execution cost (under reasonable assumptions) of the following query in the scenarios listed below. Please briefly describe the considered query plan in each scenario. select Actor, Movie, count(*) as NumberOfCharacters from Role group by Actor, Movie // Extracts actors playing three or more roles in the same movie having count(*) > 2 1. The table is primarily stored in 16K blocks, with tuples in no particular order. There is also a hash based secondary index with Movie as key, with 5K buckets of 1 block each ( val(Movie)=20K, val(Actor)=25K ). 2. The table is primarily stored in 16K blocks, with tuples se quentially ordered according to the Movie attribute (as they are sequentially appended as soon as new movies are released), and there are no secondary access structures. 3. The table is primarily stored as in case 1, but the seconda ry structure, instead of being a hash, is a B+ tree with two attributes as key (Actor, Movie) – i.e., the key is composed of the two attributes, in this order. The tree has depth 3 (a root, an intermediate level, and 3.5K leaf nodes). 2015/09/07 - Possibly Pale Blue A table T( PK, A, B, C, RefToIDofS ) is primarily stored as entry-sequenced, with 40K tuples into 8K blocks. A much larger table S( ID, X, Y ) contains 1M tuples in a primary hash-based storage, indexed by the primary key, with 100K buckets and very sparse, virtually free from overflow chains. Knowing that PK<1000 for 2% of the tuples in T, that A is a unique attribute, and that val(B) = 125 (homogeneously distributed), estimate the execution cost of…