andrey_anonymousDimetsлогика работы proc1 следующая:
1. Проверить наличие записи с table1.id в table2, если такая запись есть, то в результирующую таблица table_result пишется результат (id,'есть в table2')
2. Если в table2 нет записи с table1.id идем проверять в table3, если такая запись есть, то в результирующую таблица table_result пишется результат (id,'есть в table3')
3. Если в table3 нет записи с table1.id идем проверять в table4, если такая запись есть, то в результирующую таблица table_result пишется результат (id,'есть в table4')
4. Если в table4 нет записи с table1.id идем проверять, то в результирующую таблица table_result пишется результат (id,'нет нигде')
Каким образом можно кардинально ускорить работы процедуры?
Зависит от.
К примеру, можно так (insert или merge - по потребности, индексы по id на всех таблицах - обязательны):
1.
2.
3.
4.
5.
6.
7.
8.
insert into table_result
select id
, coalesce( (select 'есть в table2' x from dual where exists (select null from table2 where id = t.id))
, (select 'есть в table3' x from dual where exists (select null from table3 where id = t.id))
, (select 'есть в table4' x from dual where exists (select null from table4 where id = t.id))
, 'нет нигде'
)
from table1 t
Если индексов нет, но есть много памяти и уверенность в уникальности id - можно внешние hash join-ы и выбирать результат тем же coalesce в стиле:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
insert into table_result
select /*+ leading(t, t3, t4, t2) use_hash(t3) use_hash(t4) use_hash(t2)*/
t.id
, coalesce( nvl2(t2.id,'есть в table2', null)
, nvl2(t3.id,'есть в table3', null)
, nvl2(t4.id,'есть в table4', null)
, 'нет нигде'
)
from table1 t
, table2 t2
, table3 t3
, table4 t4
where t.id = t2.id(+)
and t.id = t3.id(+)
and t.id = t4.id(+)
;
Этот вариант, при наличии навыков, можно еще и параллелить.
Также можно попробовать самовыразиться на ANSI:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
insert into table_result
select t.id
, coalesce( nvl2(t2.id,'есть в table2', null)
, nvl2(t3.id,'есть в table3', null)
, nvl2(t4.id,'есть в table4', null)
, 'нет нигде'
)
from table1 t
left join table2 t2 on t.id = t2.id
left join table3 t3 on t.id = t3.id and t2.id is null
left join table4 t4 on t.id = t4.id and t3.id is null
;
но тут лучше иметь индексы по t3, t4
Если же требуется именно процедурой и именно построчно - то
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
procedure proc1(p_id) is
begin
for i in (select 'есть в table2' x from dual where exists (select null from table2 where id = p_id))
loop
insert into table_result values(p_id, i.x);
return;
end loop;
...
for i in (select 'есть в table4' x from dual where exists (select null from table4 where id = p_id))
loop
insert into table_result values(p_id, i.x);
return;
end loop;
insert into table_result values(p_id, 'нет нигде');
end;
Индексы есть такие везде. Попробовал вариант с coalesce, ускорение есть, но не на порядок как требуется
