---

Selecting an algorithm for σ

---

---

---

 

Slideshow:

Choosing an algorithm for the selection (σ) operator
 
Possible choices of implementation algorithms for selection (σ):

Selection criteria:   minimize # disk IO operations
The cost of the selection σ_Cond using the Table-Scan operation
 
How to process σ_C (for any condition C) using a Table-Scan:

(I.e.: we read all tuples and test the selection condition)
 
The cost of σ_C using Table-Scan:

The cost of σ_A=c using Index-Scan   -   clustering index
 
Case 1:   index on A is clustering

See: click here
The cost of σ_A=c using Index-Scan   -   non-clustering index
 
Case 2:   index on A is non-clustering

See: click here
The cost of σ_{A < c} using Index-Scan   -   clustering ordered index
 
Case 1:   index on A is ordered and clustering

The cost of σ_A=c using Index-Scan   -   non-clustering ordered index
 
Case 2:   index on A is ordered and non-clustering

Processing an AND-condition in selection (σ_{A=c AND Cond})
 
The cost of processing an AND-condition in selection (σ_{A=c AND Cond})
 
Example: picking the best algorithm for σ
 
Problem description:

(Click on picture to pull out)
Example: picking the best algorithm for σ
 
Choices of execution plans for σ _{x=1 ∧ y=2 ∧ z<5} (R):

Example: picking the best algorithm for σ
 
Cost of execution plans for σ _{x=1 ∧ y=2 ∧ z<5} (R):


Example: picking the best algorithm for σ
 
Cost of execution plans for σ _{x=1 ∧ y=2 ∧ z<5} (R):


❮ ❯



---

---

---

---

• Available implementation algorithms for σ
  • There are 2 possible implementation algorithms to execute a selection σ operation:

    Table-scan Index-scan

    See: click here

    ---

    ---

  • Buffer requirement:

    Buffer requirement for Table-scan: 1 buffer          Buffer requirement for Index-scan:             2 (~= 1) buffer

    In both cases, the buffer requirement is negligible.

    ---

    ---

  • How to choose a scan operator:

    Select the scan operator with the minimal disk IO           I.e.: use index scan whenever possible !!!

  ---

  ---

  ---

  ---

• Selecting an algorithm to process:   σ_{A θ c}
  • Selection guideline for σ_{A θ c} :

    If there is an ordered index (see: click here) on A: Use index to find first tuple satisfying A = c       Scan the sorted index forward/backward (if possible) to find all tuples satisfying A θ c If there is no ordered index: Use Table-Scan and test every tuple in the relation

  ---

  ---

  ---

  ---

• Processing the selection condition of the form:   (A θ c) AND cond2
  • Strategy to process σ _{(A θ c) AND cond2} :

    Use Algebraic law to re-write σ(A θ c) AND cond2 into: σ(A θ c) AND cond2(...) = σcond2 ( σA θ c ( ... ) ) If possible, use an index to process σA θ c ( ... ) And pass the result to σcond2 (..) Graphically:

    ---

  • Terminology:

    The 2nd selection is called a filter

  ---

  ---

  ---

  ---

• Multiple conditions in selection: σ_{c1 and c2}
  • Fact:

    When there are multiple conditions in σc1 and c2, we can choose a best processing order:        (1) σc1 ( σc2 ( ... ) ) Or: (2) σc2 ( σc1 ( ... ) )

    ---

    ---

  • How to choose the best order:

    Best order = the plan (= order) that has least # disk IOs         I.e.: choose the index that selects the least number of tuples !!!

  ---

  ---

  ---

  ---

• Estimating the cost (# disk IOs) of the selection (σ) algorithms
  • σ_C (any condition) using Table-Scan:

    Table-Scan will read the relation one time: # disk IOs   =   B(R)           Graphically: scan R and test each tuple

    ---

    ---

  • σ_A=c using Index-Scan on attribute A   (must have an index on A):

    B(R) If index on A is clustering: -------- (See: click here ) V(R,A) T(R) If index on A is non-clustered: -------- V(R,A)

    ---

    ---

    ---

    ---

  • σ _{(A < c)} using Index-Scan on attribute A   (must have an ordered index on A):

    Since we assumed that: T( σA < c ) ~= 1/3 T(R) (see: click here) We will assume that σA < c will access 1/3 of all tuples. Therefore: B(R) If index is clustering: -------- (See: click here ) 3 T(R) If index is non-clustered: -------- 3

  ---

  ---

  ---

  ---

• Estimating the cost to process σ_{(A = c) AND c2}

  • σ _{(A=c) AND Cond} using Index-Scan on attribute A   (must have an index on A):

    σ(A=c) AND Cond can be processed using an Index-Scan plus a filter: Furthermore: There is no disk IO necessary to pass the tuples from one selection operator to the next selection operator Cost estimation for σ (A=c) AND Cond

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Example: selecting best plan for σ
  • Problem description:

    Given: Relation R(x,y,z) is clustered R has sorted indexes on x, y and z Index z is clustering index --- see: click here T(R) = 5000 B(R) = 200 V(R,x) = 100 V(R,y) = 500 Find the best physical query plan (= algorithm) for σx=1 ∧ y=2 ∧ z<5(R)

    ---

    ---

  • Solution:

    You have the following choices of plans (including not using the indexes): Plan 1: Plan 2: Plan 3: Plan 4: