У меня есть общая проблема с ORACLE в следующем примере кода:
create or replace procedure usp_test
(
p_customerId number,
p_eventTypeId number,
p_out OUT SYS_REFCURSOR
)
as
begin
open p_out for
select e.Id from eventstable e
where
(p_customerId is null or e.CustomerId = p_customerId)
and
(p_eventTypeId is null or e.EventTypeId = p_eventTypeId)
order by Id asc;
end usp_test;
«ИЛИ» в «(p_customerId is null or e.CustomerId = p_customerId)» убивает производительность процедуры, потому что оптимизатор не будет оптимально использовать индекс (надеюсь, для поиска по индексу) в столбце «CustomerId», что приводит к сканированию вместо поиска. Индекс «CustomerId» имеет множество различных значений.
При работе с MSSQL 2008 R2 (последний SP) или MSSQL 2012 я могу подсказать запрос с помощью «option (recompile)», который будет:
- Перекомпилируйте только этот запрос
- Разрешить значения для всех переменных (они известны после вызова sproc)
- Замените все разрешенные переменные константами и устраните константные предикатные части.
Например: если я передам p_customerId = 1000, то выражение «1000 равно null» всегда будет ложным, поэтому оптимизатор его проигнорирует. Это добавит некоторую нагрузку на ЦП, но в основном используется для редко вызываемых процедур массивных отчетов, так что здесь проблем нет.
Есть ли способ сделать это в Oracle? Dynamic-SQL не вариант.
Добавляет
Та же процедура, только без «p_customerId is null» и «p_eventTypeId is null» выполняется в течение ~ 0,041 секунды, а верхняя выполняется в течение ~ 0,448 секунды (у меня ~ 5 000 000 строк).
i_
,o_
иio_
). - person APC   schedule 26.12.2012