SQL optimizers

When a user executes a SQL statement, one of the components of a database system called the optimizer must decide on the best way to access the data, which this command operates. Oracle provides two optimizers: rule-based optimizer (he was introduced as the first) and the cost-based optimizer.

In order to determine the optimal scenario, performance optimizers take into account the following issues:

• syntax of the commands
• the conditions that must be met data (WHERE clause)
• database tables, which would require a command
• all possible indices that can be used to retrieve data from the table
• the version of the Oracle database management
• The current mode of the optimizer
• SQL command guidance
• All available statistics on objects (created using the ANALYZE command)
• Physical location of tables (distributed SQL)
• IN1T.ORA file settings (parallel query, asynchronous IO, etc.)

Oracle offers the possibility to choose one of the options optimization: rule-based optimizer predictable and more "intelligent" cost optimizer.

Action rule-based optimizer

Rule-based optimizer (RBO) to determine the access path to the data database uses the rules of priority. The kernel of a database management system uses a rule-based optimizer when:

• INIT ORA file entry OPTIMIZER_MODE = RULE
• init.ora file contains the following entry OPTIMIZER_MODE = CHOOSE and for any table related to the executed command did not create statistics
• issued the command ALTER SESSION SET OPTIMIZER_MODE = RULE
• issued the command ALTER SESSION SET 0PTIMIZER_MODE = CHOOSE and for any table related to the executed command did not create statistics
• the executed command uses the appropriate guidelines (for example, SELECT / * + RULE * / ...)

Action rule-based optimizer is based primarily on the 20 ranks of conditions (ie, the "golden rule".) These rules allow the optimizer to determine the path of the execution of the command, dictate when to use one index instead of the second, and when to conduct a review of the entire table. They are immutable, set out above and - in contrast to the cost-based optimizer - they do not have the impact of external factors (size of tables, indexes, etc. distributions)

While knowledge of the rules is helpful, it is not, they say too much about how to perform tuning using the rule-based optimizer. The following sections present information to help the reader make up those deficiencies.