Table order in connection request

Question

I found this paragraph in the Oracle documentation

If you want to select the name of each department along with the name of its manager, you can write a request in one of two ways. In the first example that follows, the hint ++ ++ ++ / says to join the table order appear in the FROM clause with an attempt 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# 

Assume that there are 10 departments and 1000 employees, and that the internal table in each query has an index on the join column. In the first query, the first table produces 10 qualification rows (in this case, the entire table). In the second query, the first table produces 1000 qualification 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 get access to the DEPT table 1000 times. Therefore, the first request will work much better. As a rule of thumb, tables should be with the smallest effective row count and the largest effective row count. The effective size of the table row in the query is obtained by applying logical conditions that are fully allowed on this table.

But I do not quite understand this. If there are rows m in table t2 in table t2 and n , will the sql engine go through rows mxn in both cases?

Update: Thanks for all the answers. I will not redefine the optimizer, I just wanted to confirm my point.

Answer 1

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

However, writing queries using an ORDERED hitn like this is not common practice. It’s best to leave optimization to the optimizer most of the time.

I'm not sure what documentation you received this quotation from, but where I saw it is preceded by this very important paragraph that you omitted. I quote it here for others who might think that this method of writing queries is standard:

Usually, the optimizer chooses the best execution plan, the optimal order of the tables that need to be combined. In case the optimizer does not create a good execution plan, you can control the execution order using the HINTS SQL function. For more information, see the Oracle Database Lite SQL Reference.

- Oracle® Database Lite Developer's Guide

Answer 2

It depends on the WHERE clause.

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

Selects all managers for each department. Since there are 10 departments, this results in 10 entries.

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

This will select all employees with the name of the department in which they work. Since the number of employees is 1000 people, your result will have 1000 lines.

A JOIN will never make your engine cross lines mxn , you are the result of an internal join always m if m < n

Answer 3

Did you really find that in oracle documents?

You should not use the ORDERED hint, and let the oracle make a decision for you - this in most cases works very well these days.

However, the order of the connections makes a difference in performance.

The example, for example, discusses NESTED LOOPS combining:

 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