Database Reference
In-Depth Information
SQL> select /*+ leading(t1 t2) use_nl(t1 t2) */ 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 4d0j95yfpgxwp, child number 0
-------------------------------------
select /*+ leading(t1 t2) use_nl(t1 t2) */ 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:01.28 | 110K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.28 | 110K|
| 2 | NESTED LOOPS | | 1 | 2500 | 5000 |00:00:01.28 | 110K|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 5000 | 5000 |00:00:00.01 | 22 |
|* 4 | TABLE ACCESS FULL| T1 | 5000 | 1 | 5000 |00:00:01.27 | 110K|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."C1"=2)
4 - filter(("T2"."C2"=10000 AND "T2"."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" "T1"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/
Die Tabellenreihenfolge in diesem Auto-Join ist sehr ungünstig, weil der Wert 2 der Spalte
C1 5000mal vorkommt, dagegen der Wert 10000 der Spalte C2 lediglich einmal. Ändern
wir diese Reihenfolge und führen wir unsere SQL-Anweisung nochmals aus.
