The above script created table T1 with 1,000,000 rows and table T2with 100,000 rows. We will now use queries that are similar to those thatwere used with the 100 and 10 row tables.
The smaller table (T2) as the driving table:
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT /*+ USE_NL(T1 T2) */
T1.C1,
T1.C2,
T1.C3,
T1.C4,
T2.C1,
T2.C2,
T2.C3,
T2.C4
FROM
T1,
T2
WHERE
T1.C1=T2.C1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2610346857
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 4687K| 300K (1)| 01:00:12 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 100K| 4687K| 300K (1)| 01:00:12 |
| 3 | TABLE ACCESS FULL | T2 | 100K| 2343K| 889 (1)| 00:00:11 |
|* 4 | INDEX RANGE SCAN | IND_T1_C1 | 1 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 24 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C1"="T2"."C1")
The larger table as the driving table:
SELECT /*+ USE_NL(T1 T2) */
T1.C1,
T1.C2,
T1.C3,
T1.C4,
T2.C1,
T2.C2,
T2.C3,
T2.C4
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 BETWEEN 1 AND 10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2331401024
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 468K| 11353 (1)| 00:02:17 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10001 | 468K| 11353 (1)| 00:02:17 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 10001 | 234K| 348 (0)| 00:00:05 |
|* 4 | INDEX RANGE SCAN | IND_T1_C2 | 10001 | | 25 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T2_C1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 24 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C2">=1 AND "T1"."C2"<=10000)
5 - access("T1"."C1"="T2"."C1")
So, what is happening Is it simply the case that it is theexpected number of rows that will be returned from each table that determineswhich table will be the driving table Let’s test:
SET AUTOTRACE OFF
SELECT
COUNT(*)
FROM
T1
WHERE
T1.C1 BETWEEN 890000 AND 1000000;
COUNT(*)
----------
110001
SELECT
COUNT(*)
FROM
T2