---

Intro to the cost query processing

---

• Introductory example on processing cost
  • Consider the relations:

    Employee SSN fname salary dno --------- ------ --------- ----------- 123456789 John 20900.00 5 333445555 Frankl 50000.00 5 999887777 Alicia 25000.00 4 987654321 Jennif 43000.00 4 666884444 Ramesh 38000.00 5 453453453 Joyce 25000.00 5 987987987 Ahmad 25000.00 4 888665555 James 55000.00 1 Department: dnumber dname ----------- --------------- 5 Research 4 Administration 1 Headquarters

    Note:

    There are no indexes on any of the relations

    ---

    ---

  • Find all employee in the Research department who earn > $50,000:

    σ salary > 50000 ∧ dname='Research' ( employee ⋈ department )

    ---

    ---

  • Query plan 1:

    In tree form: Execution: for ( tuple t1 ∈ employee ) do for ( tuple t2 ∈ department ) do if ( t1.dno = t2.dnumber ) { if ( sal > 5000 && dname = 'Research' ) output t1 . t2 } Cost of execution: Accesses n2 tuples

    ---

    ---

  • Query plan 2:

    In tree form: Execution: for ( tuple t1 ∈ employee ) do if ( sal > 50000 ) output t1 to tmpRel; for ( tuple t1 ∈ tmpRel ) for ( tuple t2 ∈ department ) do if ( t1.dno = t2.dnumber ) { if ( dname = 'Research' ) output t1 . t2 } Cost of execution: Accesses n + k×n tuples           Note: k << n        (k is much smaller than n) !!!

    ---

    ---

  • Query plan 3:

    In tree form: Execution: for ( tuple t1 ∈ employee ) do if ( sal > 50000 ) output t1 to tmpRel1; for ( tuple t2 ∈ department ) do if ( dname = 'Research' ) output t2 to tmpRel2; for ( tuple t1 ∈ tmpRel ) for ( tuple t2 ∈ tmpRel2 ) do if ( t1.dno = t2.dnumber ) { output t1 . t2 } Cost of execution: Accesses n + n + k×k tuples           Note: k << n !!!