Big Table and Small Table Join strategy in Oracle(六)
WHERE
T2.C2 BETWEEN 900000 AND 1000000;
COUNT(*)
----------
100000
The above shows that if we specify T1.C1 BETWEEN890000 AND 1000000 in the WHERE clause there will be 110,001 rows from the largertable that match the criteria. If we specify T2.C2 BETWEEN 900000 AND 1000000 in the WHERE clause there will be 100,000 rows from the smallertable that match the criteria. If we execute the following query, which tablewill be the driving table, the 10 times larger T1 table where we are retrieving110,001 rows or the smaller T2 table where we are retrieving 100,000 rows
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.C3=T2.C3
AND T1.C1 BETWEEN 890000 AND 1000000
AND T2.C2 BETWEEN 900000 AND 1000000;
This is the result that I received, which seems to demonstratethat it is not just the size of the tables, nor is it the number of expectedrows to be returned from the tables, that determines which table will be thedriving table:
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 503M| 11M (1)| 37:03:27 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 11M| 503M| 11M (1)| 37:03:27 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 110K| 2578K| 3799 (1)| 00:00:46 |
|* 4 | INDEX RANGE SCAN | IND_T1_C1 | 110K| | 248 (1)| 00:00:03 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T2 | 100 | 2400 | 101 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C1">=890000 AND "T1"."C1"<=1000000)
5 - access("T1"."C3"="T2"."C3")
6 - filter("T2"."C2">=900000 AND "T2"."C2"<=1000000)
110,001 rows from T1 is still somewhat close in number to the100,000 rows from T2, so let’s try an experiment selecting 992,701 rows fromT1:
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.C3=T2.C3
AND T1.C1 BETWEEN 7300 AND 1000000
AND T2.C2 BETWEEN 900000 AND 1000000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3718770616
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99M| 4544M| 100M (1)|334:20:23 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 99M| 4544M| 100M (1)|334:20:23 |
|* 3 | TABLE ACCESS FULL | T1 | 992K| 22M| 8835 (1)| 00:01:47 |
|* 4 | INDEX RANGE SCAN | IND_T2_C3 | 100 | | 1 (0)| 00:00: