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 ?

Back to index of questions


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


Back to top

Back to index of questions