The NVL
技巧应该起作用并允许索引访问。实际上,NVL
通常是执行此操作的最佳方法,并且通常比涉及其他条件的效果更好CASE
or OR
。我用过NVL
欺骗了很多次,下面的简单测试用例表明它可以使用索引。
Schema
create table xx_people(id_number number, a number, b number);
insert into xx_people
select level, level, level from dual connect by level <= 100000;
commit;
begin
dbms_stats.gather_table_stats(user, 'xx_people');
end;
/
create index xx_people_idx1 on xx_people(id_number, -1);
生成执行计划
explain plan for
select *
from xx_people
where id_number = nvl(:p_id_number, id_number);
select * from table(dbms_xplan.display);
执行计划
Plan hash value: 3301250992
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 3808K| 106 (1)| 00:00:01 |
| 1 | VIEW | VW_ORE_67373E14 | 100K| 3808K| 106 (1)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| XX_PEOPLE | 1 | 15 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | XX_PEOPLE_IDX1 | 1 | | 2 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL | XX_PEOPLE | 100K| 1464K| 103 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:P_ID_NUMBER IS NOT NULL)
5 - access("ID_NUMBER"=:P_ID_NUMBER)
6 - filter(:P_ID_NUMBER IS NULL)
7 - filter("ID_NUMBER" IS NOT NULL)
这个计划一开始有点令人困惑。但它兼具了两全其美的优点。过滤操作允许Oracle在运行时决定当绑定变量为空(并且返回所有行)时使用全表扫描,以及当绑定变量不为空(并且仅返回几行)时使用索引。
这一切都意味着在您的具体情况下可能会发生一些奇怪的事情。您可能需要发布一个完全可重现的测试用例,以便我们找出不使用索引的原因。