Допустим, есть таблицы kk_op1 и kk_op2, которые надо соединить по ключам id.
Если мы хотим для каждой строки kk_op1 запрашивать значения из kk_op2, то можно использовать хинт use_nl (use nested loops). Неплохой результат можно получить, если есть индекс по kk_op2.id или в kk_op1 мало строк. Хинт ordered указывает порядок соединения таблиц, важно в секции from первой указать маленькую таблицу:
select /*+ ordered 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
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 создать промежуточную таблицу.
Затем в процедуре загрузки следует очищать эту промежуточную таблицу и заново заливать ее данными.
При этом продолжительность заполнения промежуточной таблицы, а также количество вставленных в нее строк разумно логировать в таблицу с логами.
По логам можно выявлять узкие места в загрузке и продолжать оптимизировать именно те загрузки в промежуточные таблицы, которые выполняются медленно.
Приведем еще немного примеров того, как хинтовка запросов может менять их время выполнения.
Далее мы будем последовательно создавать таблицы с выборками из свежесозданных таблиц, используя различные хинтовки.
Время выполнения может быть разным в зависимости от производительности сервера. Предлагается самостоятельно записать его и сравнить результаты.
Предлагается также смотреть планы запросов в PL/SQL Developer с помощью File -> New -> Explain Plan Window.
Продемонстрируем, как можно сделать выборку по всем счетам 20% клиентов, используя разные хинтовки запроса:
create table kk_op11 as
select /*+ ordered use_nl(c a) index(c ix_clients_clientid) index(a ix_accounts_clientid)*/
a.accountid, a.clientid, a.account_comment
from kk_op_clients c, kk_op_accounts a
where mod(c.clientid, 5) = 0
and c.clientid = a.clientid;
create table kk_op12 as
select /*+ use_hash(c a) full(c) full(a)*/
a.accountid, a.clientid, a.account_comment
from kk_op_clients c, kk_op_accounts a
where mod(c.clientid, 5) = 0
and c.clientid = a.clientid;
create table kk_op13 as
select /*+ use_hash(c a) index(c ix_clients_clientid) full(a)*/
a.accountid, a.clientid, a.account_comment
from kk_op_clients c, kk_op_accounts a
where mod(c.clientid, 5) = 0
and c.clientid = a.clientid;
А теперь смотрим производительность таких запросов, когда нужно сделать выборку по всем счетам 1% клиентов:
create table kk_op21 as
select /*+ ordered use_nl(c a) index(c ix_clients_clientid) index(a ix_accounts_clientid)*/
a.accountid, a.clientid, a.account_comment
from kk_op_clients c, kk_op_accounts a
where mod(c.clientid, 100) = 0
and c.clientid = a.clientid;
create table kk_op22 as
select /*+ use_hash(c a) full(c) full(a)*/
a.accountid, a.clientid, a.account_comment
from kk_op_clients c, kk_op_accounts a
where mod(c.clientid, 100) = 0
and c.clientid = a.clientid;
create table kk_op23 as
select /*+ use_hash(c a) index(c ix_clients_clientid) full(a)*/
a.accountid, a.clientid, a.account_comment
from kk_op_clients c, kk_op_accounts a
where mod(c.clientid, 100) = 0
and c.clientid = a.clientid;
Скорее всего, результаты запроса покажут, что когда из большой таблицы kk_op_accounts нужно взять 1% строк, то эффективно обращение к индексу ix_accounts_clientid с помощью nested loops, а если нужно брать много строк, то индекс скорее помешает.
