Database Reference
In-Depth Information
information that can be collected while executing part of an execution plan to determine how another part should be
carried out. For this purpose, the query optimizer adds what are called
subplans
. Also added are operations that are
responsible for determining which subplans to activate.
■
Note
adaptive execution plans are available only in enterprise edition.
From version 12.1 onward, the query optimizer can use adaptive execution plans in the following situations:
•
To switch the join method from a nested loops join to a hash join, and vice versa.
•
To switch the distribution method from hash to broadcast for SQL statements executed in
parallel.
The case related to parallel processing is covered in Chapter 15. The following example illustrates how switching
the join method works. The example shows an excerpt of the output generated by the
adaptive_plan.sql
script. The
query is a simple join between two tables. It's carried out with a regular nested loops join:
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM t1, t2
4 WHERE t1.id = t2.id
5 AND t1.n = 666;
SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate +note'));
PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 1837274416
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS FULL | T1 |
|* 4 | INDEX UNIQUE SCAN | T2_PK |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."N"=666)
4 - access("T1"."ID"="T2"."ID")
Note
-----
-
this is an adaptive plan
