week 9

9.3 Query Optimisation

The query optimiser is one of the most sophisticated components of a DBMS. It generates a query execution plan — the step-by-step instructions for retrieving data — and chooses the plan with the lowest estimated cost.

COST FACTORS the optimiser considers:

1.    Number of disk I/O operations (the most expensive operation)

2.    Amount of data transferred over the network

3.    CPU processing cost

4.    Size of intermediate results

5.    Available indexes

6.    Statistics about data distribution (maintained in the catalogue)

OPTIMISATION STRATEGIES:

1.    Perform selections as early as possible (reduces rows early)

2.    Perform projections as early as possible (reduces columns)

3.    Reorder joins to start with the smallest result sets

4.    Use indexes to avoid full table scans

5.    Use join methods appropriate to data size (Nested Loop, Hash Join, Sort-Merge Join)