17 THE ORACLE OPTIMIZER EXPLAIN THE EXPLAIN PLAN
used for the three involved tables in the query. The P (
PRODUCTS
) table joins to the S (
SALES
) table and then to the
C (CUSTOMERS) table.
The join order is determined based on cost, which is strongly influenced by the cardinality estimates and the access
paths available. The Optimizer will also always adhere to some basic rules:
Joins that result in at most one row always go first. The Optimizer can determine this based on UNIQUE
and PRIMARY KEY constraints on the tables.
When outer joins are used the row preserving table (table without the outer join operator) must come after
the other table in the predicate (table with the outer join operator) to ensure all of the additional rows that
don’t satisfy the join condition can be added to the result set correctly.
When a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come
after those tables in the outer query block to which they were connected or correlated. However, hash
antijoins and semijoins are able to override this ordering condition under certain circumstances.
If view merging is not possible all tables in the view will be joined before joining to the tables outside the
view.
If the join order is not what you expect check the cardinality estimates for each of the objects and the access
methods are correct.
Partitioning
Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces. Each piece of the
database object is called a Partition. Partition pruning or Partition elimination is the simplest means to improve
performance using Partitioning. For example, if an application has an
ORDERS
table that contains a record of all
orders for the last 2 years, and this table has been partitioned by day, a query requesting orders for a single week
would only access seven partitions of the
ORDERS
table instead of 730 partitions (the entire table).
Partition pruning is visible in an execution plan in the
PSTART
and
PSTOP
columns. The
PSTART
column contains
the number of the first partition that will be accessed and
PSTOP
column contains the number of the last partition
that will be accessed
1
. In Figure 24 four partitions from
SALES
are accessed, namely partitions 9,10,11, and 12.
Figure 24: Example plan output highlighting Partition pruning for a single-level partitioned table
1
Note that not necessarily all partitions between PSTART and PSTOP have to be accessed. More details about Partitioning and Partition pruning can
be found on OTN on the Partitioning page