### The basic (relation) access operators and their cost

• The basic tuple access operators

• 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.

• Recall: clustered and unclustered files/relations

• Clustered file:

• A file that stores records of one relation

• Unclustered file: (less common)

• A file that stores records of multiple relation

• The cost of the basic scan operators

• 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)