Order of tables in join query

Question

I found this paragraph in Oracle documentation

if you want to select the name of each department along with the name of its manager, you can write the query in one of two ways. In the first example which follows, the hint /++ordered++/ says to do the join in the order the tables appear in the FROM clause with attempting to optimize the join order.

SELECT /*+ordered*/ d.NAME, e.NAME
FROM DEPT d, EMP e WHERE d.MGR = e.SS#

or:

SELECT /*+ordered*/ d.NAME, e.NAME 
FROM EMP e, DEPT d WHERE d.MGR = e.SS# 

Suppose that there are 10 departments and 1000 employees, and that the inner table in each query has an index on the join column. In the first query, the first table produces 10 qualifying rows (in this case, the whole table). In the second query, the first table produces 1000 qualifying rows. The first query will access the EMP table 10 times and scan the DEPT table once. The second query will scan the EMP table once but will access the DEPT table 1000 times. Therefore the first query will perform much better. As a rule of thumb, tables should be arranged from smallest effective number rows to largest effective number of rows. The effective row size of a table in a query is obtained by applying the logical conditions that are resolved entirely on that table.

But I don't correctly understand this. If there are m rows in table t1 and n rows in table t2, wouldn't the sql engine go through m x n rows in both cases?

Update: Thanks for all the replies. I won't be overriding the optimizer, just wanted to confirm my thought.

Answer 1

That depends on the WHERE statement.

SELECT /++ordered++/ d.NAME, e.NAME FROM DEPT d, EMP e WHERE d.MGR = e.SS#

Will select all managers for each department. As there are 10 departments, this results in 10 records being fetched.

SELECT /++ordered++/ d.NAME, e.NAME FROM EMP e, DEPT d

This will select all employees with the name of the department they're working in. As there are 1000 employees, your resultset will have 1000 rows.

A JOIN will never cause your engine to loop over m x n rows, you're resultset of an inner join will always be m if m < n

Answer 2

Well, in the first case the number of logical reads is 10+10, in the second 1000+1000, with each department being read on average 100 times.

However, writing queries with the ORDERED hitn like this is not normal practice. It's best to leave optimisation to the optimiser most of the time.

I'm not sure exactly which documentation you got that quote from, but where I have seen it it is preceded by this very important paragraph that you omitted. I quote it here for the benefit of others who may otherwise think this method of writing queries is standard:

Normally optimizer picks the best execution plan, an optimal order of tables to be joined. In case the optimizer is not producing a good execution plan you can control the order of execution using the HINTS feature SQL. For more information see the Oracle Database Lite SQL Reference.

-- Oracle® Database Lite Developer's Guide

Answer 3

You really found that in oracle docs?

You should not use the ORDERED hint and let oracle do the decision for you--that is most of the time working very well nowadays.

However, the join order makes an difference performance wise.

The example seems to discuss the NESTED LOOPS join:

Case 1:
 -> 1 lookup to find 10 rows in table A
 -> 10 index lookups in table B

Case 2:
 -> 1 lookup to find 1000 rows in table B 
 -> 1000 index lookups in table A