Database Reference
In-Depth Information
SQL> select /*+ leading(t2 t1) use_nl(t2 t1) */ count(*)
2 from t1, t1 t2
3 where
4 t1.c1 = 2 and
5 t2.c2 = 10000 and
6 t1.c1 = t2.c1;
COUNT(*)
----------
5000
SQL>
SQL> select * from table(sys.dbms_xplan.display_cursor('','','ALLSTATS ADVANCED LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------
SQL_ID cbt0xw1sg37r6, child number 0
-------------------------------------
select /*+ leading(t2 t1) use_nl(t2 t1) */ count(*) from t1, t1 t2
where t1.c1 = 2 and t2.c2 = 10000 and t1.c1 = t2.c1
Plan hash value: 3700912137
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 44 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 44 |
| 2 | NESTED LOOPS | | 1 | 2500 | 5000 |00:00:00.01 | 44 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 22 |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 5000 | 5000 |00:00:00.01 | 22 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T2"."C2"=10000 AND "T2"."C1"=2))
4 - filter("T1"."C1"=2)
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_NL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Diese zweite Ausführung ist wesentlich performanter, hat aber denselben Plan-Hashwert
3700912137. Die beiden Ausführungspläne sind als Texte identisch, aber als Pläne unter-
schiedlich, weil die zugehörigen Prädikate umgedreht sind.
P.: „
So ein Fall ist nicht einfach zu erkennen. Oder
?“
