Database Reference
In-Depth Information
***************
Now joining: EMPLOYEES[EMP]#3
***************
…
***************
Now joining: EMPLOYEES[MGR]#4
***************
…
***********************
Best so far: Table#: 0 cost: 2.0044 card: 1.0000 bytes: 48
Table#: 1 cost: 5.0159 card: 19.0000 bytes: 1425
Table#: 2 cost: 6.0390 card: 73.2857 bytes: 6862
Table#: 3 cost: 9.5672 card: 15.1429 bytes: 2400
Table#: 4 cost: 11.6050 card: 15.0013 bytes: 2580
***********************
Join order[2]: LOCATIONS[L]#0 JOBS[J]#1 DEPARTMENTS[D]#2
EMPLOYEES[MGR]#4 EMPLOYEES[EMP]#3
***************
Now joining: EMPLOYEES[MGR]#4
…
***********************
For each potential join order, the optimizer evaluates the cost of these three join methods:
Nested loops (NL) join
Sort-merge (SM) join
Hash join (HA)
At the end of each section entitled “Now joining”, CBO reports the best join method
(e.g., “Best:: JoinMethod: NestedLoop”).
Join order[8]: LOCATIONS[L]#0 DEPARTMENTS[D]#2 EMPLOYEES[EMP]#3
JOBS[J]#1 EMPLOYEES[MGR]#4
***************
Now joining: EMPLOYEES[EMP]#3
***************
NL Join
Outer table: Card: 3.86 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 67
Inner table: EMPLOYEES Alias: EMP
Access Path: TableScan
NL Join: Cost: 8.09 Resp: 8.09 Degree: 0
Cost_io: 8.00 Cost_cpu: 316513
Resp_io: 8.00 Resp_cpu: 316513
