Oracle includes a cost-based optimizer, which can recognize

1. partitions and
2. star schema queries.

With an Oracle database, you can partition a table or index. Partitioning allows you to specify that

1. different rows in a table, or
2. entries in an index,

are stored in different physical locations based on the values in the database object.
Partitioning is frequently implemented to avoid unnecessary disk I/O in queries. For instance, if a table and its index are partitioned based on the value of a column containing a state abbreviation, a query looking for rows for a particular state could go directly to the right partition for the state, skipping all other partitions.
The cost-based optimizer ^[1] in Oracle is aware of partitions, and will choose the optimal execution plan based on the partitions implemented for the table.

The CBO determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The CBO also considers hints, which are optimization suggestions placed in a comment in the statement. The CBO performs the following steps:

1. The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.
2. The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement. The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory. Serial plans with higher costs take more time to execute than those with smaller costs. When using a parallel plan, however, resource use is not directly related to elapsed time.
3. The optimizer compares the costs of the plans and chooses the one with the lowest cost.

The star schema is a type of schema designed for the data warehouse, where a single fact table, which typically contains an enormous number of small rows, is surrounded by a number of dimension tables that typically contain a much smaller number of rows. The previous versions of the cost-based query optimizer might not recognize the existence of a star schema and consequently develop a less-than-optimal execution plan.
The cost-based optimizer in Oracle has been improved, automatically recognizing star schemas and making the proper execution plan choice when the star schema exists.

You can influence the decision of the cost-based optimizer through the use of hints. A hint tells the optimizer to use a particular access path in the execution plan.
Oracle includes several hints

1. to guide the cost-based optimizer to a star schema;
2. to use parallelism when accessing a partitioned index; and
3. to use a fast full index scan when the index contains all the values needed in a query.

In the next lesson, you will learn how to use stored outlines.

[1] optimizer: Built-in database software that determines the most efficient way to execute a SQL statement. The query optimizer is made up of the query transformer, the estimator, and the plan generator. The optimizer generates a set of potential execution plans for SQL statements, estimates the cost of each plan, calls the plan generator to generate the plan, compares the costs, and chooses the plan with the lowest cost. The database uses this approach when the data dictionary has statistics for at least one of the tables accessed by the SQL statements.