Driving table

[Queyon]

In table joining operation, how could I identify the driving table?

Queyon

[yoda]

"Too bad that all the people who know how to run the country are busy driving taxicabs and cutting hair."
-- George Burns (1896-1996), American actor, author, commedian, vaudevilian

[stecal]

From MetaLink Note 10585.1

Consider the following common join query:

SELECT *
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL > 5000;

The plan tree is:

NESTED LOOPS()
TABLE ACCESS(FULL) DEPT
TABLE ACCESS(BY ROWID) EMP
INDEX(RANGE SCAN) EMP_IDX NON-UNIQUE

NESTED LOOPS means that a sequential scan is done on one
table (DEPT) and each DEPTNO retrieved is looked up in the index
EMP_IDX on EMP. This query has what is called a driving table.
In this case, the driving table is DEPT. In these type of joins,
the driving table is determined by which table is listed last.
This is because both tables have equal rank access paths (both
have non-unique indexes on DEPTNO) and since all rows must be
searched in at least one table, it is more efficient to perform a
full table scan on one table and look up the join column in the
index of the other to meet the join predicate. In such cases,
ensure that the driving table with the fewest number of rows is
the last one listed in the FROM clause. Note that the order of
tables in the FROM clause currently influences how the ORACLE
RDBMS chooses the access path for this type of join.