План выполнения SQL-запросов хранимой процедуры

Я немного застрял в хранимой процедуре, которая выполняется очень медленно. Хранимая процедура в основном содержит запрос, который использует входящий параметр (in_id) и помещается в курсор следующим образом:

open tmp_cursor for select col1, col2, col3 from table1 tab where ((in_id is null) or (tab.id = in_id)); -- tab.id is the PK 

Когда я получаю план выполнения SQL-запроса отдельно с предопределенным значением, я получаю хорошие результаты с запросом с использованием индекса. Однако, когда я вызываю эту процедуру из своего приложения, я вижу, что индекс не используется, и таблица получает полное сканирование, что дает медленную производительность.
Если я удалю первую часть предложения WHERE "(in_id is null), производительность приложения снова будет быстрой.
Почему индекс не используется во время вызова из моего приложения (in_id передается в)?

Предполагая, что in_id – это параметр запроса, а не имя столбца:

Запрос должен иметь только один план exec, независимо от ввода. Поэтому, если вы передаете параметр in_id как NULL, он должен возвращать ВСЕ строки. Если вы передаете не-NULL in_id , следует вернуть только одно значение PK.

Поэтому Oracle выбирает «худший возможный» exec. планируют заниматься сценарием «наименее возможно». «Общие» запросы – это путь в ад. Просто разделите запрос на два.

 select col1, col2, col3 from table1 tab where in_id is null or in_id is not null; 

Это будет использовать FULL сканирование таблицы, что является лучшим способом получения всех строк.

 select col1, col2, col3 from table1 tab where tab.id = in_id; -- tab.id is the PK 

Это будет использовать UNIQUE index scan, что является лучшим способом получить одну индексированную строку.

in_id имеет значение null

Я ответил на аналогичный вопрос здесь. https://stackoverflow.com/a/26633820/3989608

Некоторые факты о значениях NULL и INDEX:

  • Полностью NULL-ключи не вводятся в «нормальное» дерево B * в Oracle

  • Поэтому, если у вас есть объединенный индекс, скажем, C1 и C2, тогда вы, скорее всего, найдете значения NULL в нем, поскольку у вас может быть строка, где C1 является NULL, но C2 не является NULL – это значение ключа будет в индексе.

Некоторая часть демонстрации Томаса Кита относительно того же:

 ops$tkyte@ORA9IR2> create table t 2 as 3 select object_id, owner, object_name 4 from dba_objects; Table created. ops$tkyte@ORA9IR2> alter table t modify (owner NOT NULL); Table altered. ops$tkyte@ORA9IR2> create index t_idx on t(object_id,owner); Index created. ops$tkyte@ORA9IR2> desc t Name Null? Type ----------------------- -------- ---------------- OBJECT_ID NUMBER OWNER NOT NULL VARCHAR2(30) OBJECT_NAME VARCHAR2(128) ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats(user,'T'); PL/SQL procedure successfully completed. 

Ну, этот индекс, безусловно, может использоваться для удовлетворения «IS NOT NULL» при применении к OBJECT_ID:

 ops$tkyte@ORA9IR2> set autotrace traceonly explain ops$tkyte@ORA9IR2> select * from t where object_id is null; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34) 2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1) 

На самом деле – даже если в таблице не было столбцов NOT NULL, или нам не нужен / должен был иметь объединенный индекс, включающий OWNER, – есть прозрачный способ легко найти значения NULL OBJECT_ID:

 ops$tkyte@ORA9IR2> drop index t_idx; Index dropped. ops$tkyte@ORA9IR2> create index t_idx_new on t(object_id,0); Index created. ops$tkyte@ORA9IR2> set autotrace traceonly explain ops$tkyte@ORA9IR2> select * from t where object_id is null; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34) 2 1 INDEX (RANGE SCAN) OF 'T_IDX_NEW' (NON-UNIQUE) (Cost=2 Card=1) 

Источник: Ничего нечего Томасу Ките

 select col1, col2, col3 from table1 tab where (tab.id = nvl(in_id,tab.id)); 

Может быть, поможет .. или вы можете использовать подсказку оракула

 +Use_concat