代码之家 › 专栏 › 技术社区 › Superdooperhero

Oracle-索引与可选参数一起使用

database-indexes oracle

2

Superdooperhero · 技术社区 · 7 年前

我使用以下技巧来索引具有一些null的列:

create index xx_people_idx1 on xx_people(id_number, -1);

这很有效。遗憾的是,当您使用可选参数时,这并没有帮助,尽管:

select *
from xx_people
where id_number = nvl(:p_id_number, id_number);

这将导致全表扫描,即使您为 p_id_number . 在这种情况下使用索引有什么诀窍吗?

由于按id号和名称搜索是我仅有的两次搜索,因此这是非常理想的。

1 回复 | 直到 7 年前

1

Jon Heller TenG 7 年前

这个 NVL 这个技巧应该奏效,并允许索引访问。事实上 NVL公司 通常是最好的方法,并且通常比其他条件更好 CASE 或 OR 。我用过 NVL公司 技巧多次,下面的简单测试用例显示它可以使用索引。

架构

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在运行时决定在绑定变量为null(且返回所有行)时使用完整表扫描,在绑定变量不为null(且仅返回少数行)时使用索引。

这都意味着在你的具体案例中可能会发生一些奇怪的事情。您可能需要发布一个完全可复制的测试用例,以便我们了解为什么不使用索引。