First page Back Continue Last page Graphics
Choosing Join Plan
CBO decides the following for all joins:
- Access path
- e.g. full scan, index range scan, etc.
- Join method
- e.g. nested loops, hash, sort-merge, etc.
- Join order
- What table will drive the join process
Each one can have an impact on performance
Notes:
For joins, the cost-based optimizer must decide on more than just the access path. (We learned about access paths in the previous lesson.) CBO must also decide:
Join Method – The Oracle9i optimizer has several join methods available, which are a primary focus of this lesson.
Join Order – For join methods such as nested loops and hash joins, CBO must decide which table to access first to “drive” the join. This is usually a critical performance decision. It is also a primary focus of this lesson.