Query Optimization Example

1
Query Optimization Example

• Source Query Optimization, Y. E. Ioannidis, ACM
  Computing Surveys, 28(1), March 1996.

• Database Tables
• Emp (name, age, sal, dno)
• Dept (dno, dname, floor, budget, mgr, ano)
• Acnt (ano,t ype, balance, bno)
• Bank (bno, bname,a ddress)

• Query Q
• select name, floor
• from emp, dept
• where emp.dnodept.dno and salgt100K.

System Catalog Information Table Emp 20,000
pages, 100,000 tuples, clustering Btree index on
emp.sal (3-levels) selectivity( salary gt 100K)
10 percent Table Dept 10 pages, 100 tuples,
clustering Hash-file on dept.dno (1.2
pages/bucket) Memory Buffer 3 pages Disk I/O
cost 20 ms/page
2
Algebraic Tree Transformations
3
Dynamic Programming Step 1 Options at Leafs
4
Step 2a Options for Join nested loop
5
Step 2 Options for Join
6
Semantic Query Optimization

• Query Q
• select name, floor
• from emp, dept
• where emp.dnodept.dno
• And job Sr. Programmer

• Q? Compare queries Q and Q on
• Query Result
• Efficiency of query plans

• Integrity Constraint
• assert sal-constraint on emp
• salgt100K where job Sr. Programmer

• Query Q
• select name, floor
• from emp, dept
• where emp.dnodept.dno
• and job Sr. Programmer
• and salgt100K.

System Catalog Information Table Emp 20,000
pages, 100,000 tuples, clustering Btree index on
emp.sal (3-levels) selectivity( salary gt 100K)
10 percent Table Dept 10 pages, 100 tuples,
clustering Hash-file on dept.dno (1.2
pages/bucket) Memory Buffer 3 pages Disk I/O
cost 20 ms/page