Slideshow:
The basic access operators (read input relations)
Example Index-Scan: σlname='Smith'( employee ) when employee has an index on lname
These scan operators are used to read tuples in a relation (in order to process the query)
What are the cost and constraints of these basic access operators ?
Review: clustered file
Review: unclustered file
Performance cost of Table-Scan and Index-scan for
clustered files
Index-Scan on a index on a key attribute will always access 1 disk block
Performance cost of Table-Scan and Index-scan for
unclustered files
- There are 2 basic tuple
access operators available for the
Physical Query Plan:
- Table-Scan(R):
- Read tuples from the relation R by reading data blocks - one block at a time from disk to memory
- Index-Scan(R):
- The relation R must have an
index
- The index is scanned
to find blocks that
contain the
desired tuples
- All the blocks containing desired tuples are then read into the memory --- one block at a time.
- The relation R must have an
index
- Table-Scan(R):
- Recall: clustered and
unclustered files
(see:
click here)
- Clustered file:
- A file that stores
records of
one relation
- A file that stores
records of
one relation
- Unclustered file:
(less common)
- A file that stores
records of
multiple relation
- A file that stores
records of
multiple relation
- Clustered file:
- Situation:
the relation R is
clustered
Operator I/O cost Explanation Table-Scan B(R) Read all blocks from the relation: B(R) blocks. 
Index-Scan ≤ B(R) Depends on number of values in the index is scanned Single value: I/O cost = O(1) (a few blocks) << B(R)
- Situation:
the relation R is
unclustered
Operator I/O cost Explanation Table-Scan ~ T(R) Assuming that the tuples are sparsely stored in the file: 
We will read 1 disk block per tuple.
Total disk blocks read = T(R)Index-Scan ≤ T(R) Depends on number of values in the index is scanned Single value: I/O cost = O(1) (a few blocks) << B(R) << T(R)