Database Reference
In-Depth Information
2 - access("N1"=8)
filter( EXISTS (SELECT /*+ PUSH_SUBQ LEADING ("T3" "T2") FULL ("T3")
USE_HASH ("T2") FULL ("T2") */ 0 FROM "T3" "T3","T2" "T2" WHERE
"T2"."ID"="T3"."ID" AND "T2"."N1"=:B1 AND "T3"."N1"=4))
3 - access("T2"."ID"="T3"."ID")
4 - filter("T3"."N1"=4)
5 - filter("T2"."N1"=:B1)
■
this query is another case where the
v$sql_plan
and
v$sql_plan_statistics_all
views show wrong
information. in this case,
EXPLAIN PLAN
shows the correct predicates shown above. the wrongly displayed predicate is
the one associated with the filter of operation 2:
2 - access("N1"=8)
filter( IS NOT NULL)
Caution
Also in this case, if you carefully look at the
Starts
column, you'll notice something strange. The operations up
to 2 are executed only once, and the operations from 3 to 5 are executed 13 times. This execution plan carries out the
operations as follows:
Operation 2, which is the first one being executed, applies the
"N1"=8
access predicate by
scanning the
i1
index. It extracts from the index, for the key fulfilling the access predicate,
not only the rowid but also the value of the
n2
column. For each distinct value of the
n2
column, the subquery (operations 3 to 5) is executed once. This is done to apply the filter
predicate. Notice that the query optimizer transformed the
IN
into an
EXISTS
. The join
carried out by the subquery is implemented with a hash join, an unrelated-combine
operation.
1.
Operation 4, the first child of the hash join, reads the
t3
table with a full scan and passes
the rows that fulfill the
"T3"."N1"=4
filter predicate to its parent (3).
2.
Operation 5, the second child of the hash join, reads the
t2
table with a full scan and
passes the rows fulfilling the
"T2"."N1"=:B1
filter predicate to its parent (3). The bind
variable (
B1
) is used to pass the value to be checked to the subquery.
3.
4.
Operation 3 joins the two sets of rows passed by operations 3 and 4. It passes the data to its
parent (2) when at least one row is found.
5.
Operation 2, for each row fulfilling the condition implemented by the subquery, passes
one rowid to its parent (1).
Operation 1 uses the rowids it receives from its child (2) to access the
t1
table and extract
its columns. It passes the data to its parent (0).
6.
7.
Operation 0 sends the data to the caller.
Adaptive Execution Plans
Object statistics don't always provide all the information the query optimizer needs to find an optimal execution plan.
To improve this situation, the query optimizer, during the parse phase, can take advantage of dynamic sampling to
get additional insights into the data to be processed. (Dynamic sampling is described in Chapter 9). In addition, as of
