SQL Optimizers

When we execute a SQL Statement, Optimizer (a component of Database) must decide how best to access the data operated on by that Statement.

Oracle Supports 2 types of Optimizers.

• Rule-Base Optimizer(default)
• Cost-Base Optimizer

The optimizers consider the following to find out the optimal execution path for a SQL statement.

• The syntax specified for the statement.
• Any conditions that the data must satisfy (WHERE clauses).
• All possible indexes that can be used in retrieving data from the table.
• The Oracle Database(RDBMS) Version.
• The database tables that the SQL statement will need to access.
• SQL Statement Hints.
• The current Optimizer Mode.
• INIT.ORA settings
• The physical Table Location (distributed SQL).