oracle: Tables Joins (объединения таблиц)

Из Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Understanding Joins
CBO access path


Виды соединений:
Nested Loop Joins
Hash Joins
Sort Merge Joins
Cartesian Joins
Outer Joins


Как оптимизатор выбирает метод соединения
Оптимизатор оценивает стоимость каждого метода и выбирает самый дешёвый из них.

• Nested Loop Joins обычно не эффективен при соединении большого кол-ва строк (обычно более 10 000 строк уже много). Стоимость Nested Loop Joins рассчитывается по формуле:
  

  cost= access cost of A + (access cost of B * number of rows from A)

•  Hash Joins эффективны для большого кол-ва строк. Стоимость Hash Joins рассчитывается по формуле:
  

  cost= (access cost of A * number of hash partitions of B) + access cost of B

• При использовании RBO оптимизатора эффективны Merge Joins. Стоимость Merge Joins рассчитывается по формуле:

  cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)

  Если данные уже отсортированы, то стоимость сортировки равна нулю.
  

Как оптимизатор выбирает Execution Plan (план выполнения)

Оптимизатор должен решить в каком порядке соединять таблицы, т.е. какая таблица будет 1й, 2й и т.д.
Если нужно указать порядок соединения вручную, то используется хинт ORDERED (/*+ ORDERED */), тогда соединение осуществляется в том порядке, в котором указаны таблицы после from.
Например: select /*+ ORDERED*/ col1, col2, col3, col4 from t1, t2, t3


Это одинаково для CBO и RBO:

•  Оптимизатор определяет какая из таблиц вернёт не более чем одну строку основываясь на ограничениях UNIQUE и PRIMARY KEY для таблицы. Если такая ситуация действительна, то оптимизатор ставит эту таблицу на первое место в соединении
• For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. 

With the CBO, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:

• The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary.
• The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.