SQL Optimizers - Cost Based

SQL Optimizers - Cost Based : The Oracle Cost Based Optimizer (CBO) is a sophisticated facility. It uses database information (e.g., table size, number of rows, key spread) rather than a set of rigid rules. This information is available once statistics have been calculated on the table, index, or cluster, To calculate statistics, the Oracle ANALYZE command (Oracle 8i or earlier) or the DBMS_STATS supplied packaged (Oracle 9i and Oracle 10g) is used. If a table does not have statistics, the Cost Based Optimizer can use only rule-based logic to select the best access path provided that table is the only table in the query and the database is pre-Oracle 10g. If multiple tables are involved in the query, and one table has statistics, but the others do not, the optimizer will quickly generate statistics for those tables provided that the database is pre-Oracle 10g. Those statistics have a good chance of leading to poor execution plans. It is advised to calculate statistics on all database objects.

In Oracle 10g, if no statistics are available, the CBO will perform dynamic sampling depending on the setting of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter. In Oracle 10g, the Rule Based Optimizer will only be used if the /*+ RULE */ hint is employed. In Oracle 10g, this hint is unsupported and should not be used.

The ANALYZE function (Oracle 8i and earlier) and the DBMS_STATS supplied package (Oracle 9i and Oracle 10g) collects statistics about tables, clusters, and indexes, and stores them in the data dictionary. The DBMS_STATS package lets you store statistics in your own schema in statistics tables that you create with the DBMS_STATS.CREATE_STAT_TABLE procedure. The DBMS_STATS package lets you export these statistics to be imported into another database. However, the CBO will only use statistics present in the data dictionary.