The Oracle (tm) Users' Co-Operative FAQ
Which Oracle features require and/or force the use of Cost Based Optimisation ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 1st Nov 2002 Oracle version(s): 8.1 - 9.2 |
| Which Oracle features require and/or force the use of Cost Based Optimisation ? |
There is a list of Oracle features in the Performance Tuning Guide that are quoted as 'forcing Cost Based Optimisation' to happen. Unfortunately, this list is a little misleading. Some of the features (such as parallel tables) force CBO to kick in, others simply will not work unless CBO has been invoked. This note splits the list into the two relevant groups.
The presence of the following features appears to force CBO to kick in.
Index Organized Tables (IOTs)
Partitioned Tables
Parallel Tables
The SAMPLE clause
ANSI Outer joins
The Rule Based Optimizer will not notice the presence of the following features - you must invoke CBO explicitly
Reverse key indexes
Function based indexes
Bitmap indexes
Bitmap Join indexes
The Rule based optimiser will use the following, without invoking CBO
Inline (from clause) views
Partition views
ANSI natural and cross joins
Other comments
Other functionality such as star joins, bitmap star transformations, the progress meter (v$session_longops) and hash joins are also dependent on the cost based optimiser being invoked. However I have not included them in the lists above, as they are 'action-oriented' options rather than having what might be called a sort of 'physical presence'.
Further reading: N/A
