Оптимизация запросов.

Допустим, есть таблицы kk_op1 и kk_op2, которые надо соединить по ключам id.

Если мы хотим для каждой строки kk_op1 запрашивать значения из kk_op2, то можно использовать хинт use_nl (use nested loops). Неплохой результат можно получить, если есть индекс по kk_op2.id или в kk_op1 мало строк:

select /*+ use_nl(kk_op1 kk_op2)*/

*

from kk_op1, kk_op2

where kk_op1.id = kk_op2.id;

Если мы хотим попробовать построить hash по таблицам, считая, что поиск по индексу будет неэффективным, то можно использовать хинт use_hash:

select /*+ use_hash(kk_op1 kk_op2)*/

*

from kk_op1, kk_op2

where kk_op1.id = kk_op2.id;

Если мы хотим явно указать, что таблицу kk_op1 нужно сканировать по индексу IX_KK_OP1, то можно использовать хинт index:

select /*+ index(kk_op1 ix_kk_op1)*/

*

from kk_op1;

Если в запросе таблицам даны алиасы, то именно их нужно использовать в хинте, иначе хинт не будет работать.

Оптимизация запросов с помощью хинтов и модификации метода соединения таблиц не так эффективна, как изменение логики самого запроса.

Имеет смысл соединять маленькие по объему данных подзапросы, а не большие таблицы. Например, вместо запроса:

select a.id, a.f1, b.id, b.id2, b.f2, c.id, c.dt

from a, b, c

where a.id = b.id

and b.id2 = func(c.id)

and a.f1 = 'ограничение выборки'

and nvl(b.f2, '*') <> 'ограничение выборки2'

and c.dt between to_date('начало ограничения') and

to_date('конец ограничения');

стоит пробовать использовать запрос:

with aa as

(select /*+ materialize*/

id, f1

from a

where f1 = 'ограничение выборки'),

bb as

(select /*+ materialize*/

id, id2, f2

from b

where f2 <> 'ограничение выборки2'

or f2 is null /*использование nvl меняем на использование is null*/),

cc as

(select /*+ materialize*/

id, func(c.id) func_id /*рассчитанное поле, по которому будем соединять, материализуем*/, dt

from c

where dt between to_date('начало ограничения') and

to_date('конец ограничения'))

select aa.id, aa.f1, bb.id, bb.id2, bb.f2, cc.id, cc.dt

from aa, bb, cc /*стараемся соединять маленькие таблицы, а не большие*/

where aa.id = bb.id

and bb.id2 = cc.func_id;

Автору нравится использование конструкции WITH и хинта /*+ materialize*/, которое позволяет по ходу выполнения SQL-запроса фактически заниматься ETL-шагами, не занимаясь написанием create table as select.

Если же производится не оптимизация SQL-запроса, а оптимизация PL/SQL процедуры загрузки, то имеет смысл под каждый WITH создать промежуточную таблицу.

Затем в процедуре загрузки следует очищать эту промежуточную таблицу и заново заливать ее данными.

При этом продолжительность заполнения промежуточной таблицы, а также количество вставленных в нее строк разумно логировать в таблицу с логами.

По логам можно выявлять узкие места в загрузке и продолжать оптимизировать именно те загрузки в промежуточные таблицы, которые выполняются медленно.