Chapter 11, Problem 20P

Explanation of Solution

Creating two alternative access plans with the data that are provided:

The below table shows the two alternative access plans for the data that are provided:

Given:

Total vendors: 10,000

Number of vendors in the cityTN : 113

Assumption:

Considering there are no indexes and reading each row will have I/O cost of “1”.

Table creation:

Plan	Step	Operations	I/O operations	I/O cost	Resulting row sets	Total I/O cost
A	A1	Performs full table scan on the table VENDOR. Select rows with V_STATE= “TN”	10,000	10,000	113	10,000
	A2	Perform SORT operations and select rows that matches the given criteria.(V_STATE).	113	113	113	10,113
B	B1	Performs Index scan for the given area code. (VEN_MYNDX1). Select rows based on the state...