Coursehero >> Arizona >> Arizona >> CS 460

Course Hero has millions of student submitted documents similar to the one below including study guides, homework solutions, papers, exam answer keys and textbook solutions.

Lifetime t11o01: of a Query DBMS Catalog Information Optimizer SQL Query Scanner/ Parser Query In Intermediate Form Candidate Plan Generator Cost Estimator Logical Query Plan Code Generator Query Execution Code Query Processor Query Result t11o01 (1/1) t11o02 (1/1) t11o02: Query Trees What are the names of the active suppliers of nuts? select sname from s, p, spj where s.sno = spj.sno and spj.pno = p.pno and status <> 0 and pname = 'Nut'; The Corresponding `Standard' Query Tree: sname status <> 0 pname=`N ut P # S# S SPJ P After a Bit of Rule Based Optimization: sname P # S# status <> 0 SPJ S pname=`N ut P t11o03 (1/1) t11o03: Nested Loops Join Pseudocode: for each tuple in relation r for each tuple in relation s if the tuples match according to the join condition output their marriage r X D C B D Y 3 4 1 2 s X D B D A B Z 8 7 6 1 1 r s X D D B B D D Y 3 3 1 1 2 2 Z 8 6 7 1 8 6 t11o04 (1/1) t11o04: Sort Merge Join Pseudocode: Make the first tuple of relation r the current tuple Loop until one of the relations is exhausted: Make a set of all tuples from relation r that have the same join attribute value as the current tuple For each tuple from relation s that has a matching join attribute value Output the marriages of it to each of the members of the set The next tuple from relation r that is not a member of the set becomes the new current tuple r X B C D D Y 1 4 3 2 s X A B B D D Z 1 7 1 8 6 r s X B B D D D D Y 1 1 3 2 3 2 Z 7 1 8 8 6 6 t11o05 (1/1) t11o05: Hash Join Pseudocode: Hash each relation using the same hash function on the join attributes of the tuples. For each of the M corresponding pairs of buckets: Build an in-memory hash index on the contents of the first bucket in the pair For each tuple in the second bucket: Probe the index with its join attribute For each matching tuple found: Output the marriage r X D C B D Y 3 4 1 2 s X D B D A B Z 8 7 6 1 1 r s X D D B D D B Y 3 2 1 3 2 1 Z 8 8 7 6 6 1 C D B D A D B D B Odd Even t11o06 (1/1) t11o06: PostgreSQL's explain Command The Query: explain select from where and and and The Output: NOTICE: QUERY PLAN: sname s, spj, p s.sno = spj.sno spj.pno = p.pno status <> 0 pname = 'Nut'; Nested Loop (cost=24.46..44.76 rows=24 width=60) -> Merge Join (cost=24.46..24.88 rows=2 width=36) -> Sort (cost=1.79..1.79 rows=24 width=24) -> Seq Scan on spj (cost=0.00..1.24 rows=24 width=24) -> Sort (cost=22.67..22.67 rows=10 width=12) -> Seq Scan on p (cost=0.00..22.50 rows=10 width=12) -> Index Scan using s_pkey on s (cost=0.00..8.16 rows=10 width=24) After vacuum: NOTICE: QUERY PLAN: Nested Loop (cost=0.00..3.73 rows=1 width=60) -> Nested Loop (cost=0.00..2.61 rows=1 width=36) -> Seq Scan on p (cost=0.00..1.07 rows=1 width=12) -> Seq Scan on spj (cost=0.00..1.24 rows=24 width=24) -> Seq Scan on s (cost=0.00..1.06 rows=4 width=24) After vacuum analyze: NOTICE: QUERY PLAN: (cost=3.77..3.88 rows=4 width=60) (cost=2.65..2.65 rows=4 width=36) Nested Loop (cost=0.00..2.61 rows=4 width=36) -> Seq Scan on p (cost=0.00..1.07 rows=1 width=12) -> Seq Scan on spj (cost=0.00..1.24 rows=24 width=24) (cost=1.12..1.12 width=24) rows=5 Seq Scan on s (cost=0.00..1.06 rows=5 width=24) Merge Join -> Sort -> -> Sort -> t11o07 (1/1) t11o07: Oracle 10g's explain plan for Command The Query: explain plan for select from where and and and sname s, spj, p s.sno = spj.sno spj.pno = p.pno status <> 0 pname = 'Nut'; The Plan Produced by Rule-Based Optimization (RBO): ------------------------------------------------------| Id | Operation | Name | ------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | SPJ | |* 4 | TABLE ACCESS BY INDEX ROWID| P | |* 5 | INDEX UNIQUE SCAN | SYS_C00665352 | |* 6 | TABLE ACCESS BY INDEX ROWID | S | |* 7 | INDEX UNIQUE SCAN | SYS_C00665351 | ------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------4 5 6 7 filter("PNAME"='Nut') access("SPJ"."PNO"="P"."PNO") filter("STATUS"<>0) access("S"."SNO"="SPJ"."SNO") Note ----- rule based optimizer used (consider using cbo) t11o08 (1/1) t11o08: Oracle 10g's explain plan for Command (continued) Cost-based Optimization (CBO) After exec DBMS STATS.delete schema stats(): ----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 220 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 220 | 7 (15)| 00:00:01 | | 2 | MERGE JOIN CARTESIAN| | 5 | 230 | 4 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | P | 1 | 17 | 2 (0)| 00:00:01 | | 4 | BUFFER SORT | | 5 | 145 | 2 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | S | 5 | 145 | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL | SPJ | 24 | 216 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------1 - access("S"."SNO"="SPJ"."SNO" AND "SPJ"."PNO"="P"."PNO") 3 - filter("PNAME"='Nut') 5 - filter("STATUS"<>0) Note ----- dynamic sampling used for this statement Cost-based Optimization (CBO) After exec DBMS STATS.gather schema stats(): -----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 104 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 104 | 6 (17)| 00:00:01 | | 2 | NESTED LOOPS | | 4 | 56 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| P | 1 | 8 | 2 (0)| 00:00:01 | |* 4 | INDEX FULL SCAN | SYS_C00665354 | 4 | 24 | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | S | 5 | 60 | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------1 - access("S"."SNO"="SPJ"."SNO") 3 - filter("PNAME"='Nut') 4 - access("SPJ"."PNO"="P"."PNO") filter("SPJ"."PNO"="P"."PNO") 5 - filter("STATUS"<>0)

Find millions of documents here - Study Guides, Homework Solutions, Papers, Exam Answer Keys and more. Course Hero has millions of course related materials that will enable you to learn better, faster and get an A in all your courses.
Below is a small sample set of documents: