甲骨文.如果参数为null，则全选，否则返回特定项目的问题 [英] Oracle. Select all if parameter is null else return specific item issue

问题描述

如果输入参数为空，我需要返回所有记录.

I need to return all records if input parameter is null.

我写了一个简单的查询

declare  
   l_sql varchar2(100);
   i number := 1;
 begin
   l_sql:= 'EXPLAIN PLAN for select * from job where :i is null or id = :i';
   execute immediate l_sql;
   commit;
  end;

SELECT * FROM TABLE(DBMS_XPLAN.display); 

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                                  
|   0 | SELECT STATEMENT   |      |  1386 |   217K|   226   (1)| 00:00:03 |       |       |                                                                                                                                                                                                                  
|   1 |  PARTITION LIST ALL|      |  1386 |   217K|   226   (1)| 00:00:03 |     1 |    13 |                                                                                                                                                                                                                  
|*  2 |   TABLE ACCESS FULL| JOB  |  1386 |   217K|   226   (1)| 00:00:03 |     1 |    13 |                                                                                                                                                                                                                  

如您所见，索引不起作用，但是如果我删除:i为null"，则检查索引开始起作用.

As you can see index is not working, but if I remove ":i is null" check index starts to work.

declare  
   l_sql varchar2(100);
   i number := 1;
 begin
   l_sql:= 'EXPLAIN PLAN for select * from job where id = :i';
   execute immediate l_sql;
   commit;
  end;

SELECT * FROM TABLE(DBMS_XPLAN.display);

| Id  | Operation                          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                             

|   0 | SELECT STATEMENT                   |           |     1 |   161 |     2   (0)| 00:00:01 |       |       |                                                                                                                                                                                             
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| JOB       |     1 |   161 |     2   (0)| 00:00:01 | ROWID | ROWID |                                                                                                                                                                                             
|*  2 |   INDEX RANGE SCAN                 | IX_JOB_ID |     1 |       |     1   (0)| 00:00:01 |       |       |

那么完成此任务的更好方法是什么?为什么索引在这种情况下不起作用?

So what is the better way to complete this task? Why index is not work in that situation?

请记住，过程可以具有许多这样的参数.

Keep in mind that procedure can have many such parameters.

推荐答案

只需使用coalesce.这是最容易理解的方式.由于逻辑包含在一个谓词中，因此更易于维护和删除:

Simply use coalesce. It is the most readable and understandable way to write this. Since the logic is contained in one predicate, it's easier to maintain and remove:

select * from job where id = coalesce(:i, id)

---

根据要求，证明"实际上使用了索引:

---

As requested, a 'proof' this actually uses the index:

create table x ( id number(15) null );

create unique index x_pk on x( id );

select id
from   x
where  id = coalesce(:x, id)
; -- Uses index

select id
from   x
where  id = :x or :x is null
; -- Full table scan

计划:

SELECT STATEMENT ALL_ROWS费用:1个字节:13个基数:1

SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 13 Cardinality: 1

1个索引全扫描索引(唯一)X_PK成本:1个字节:13个基数:1

1 INDEX FULL SCAN INDEX (UNIQUE) X_PK Cost: 1 Bytes: 13 Cardinality: 1

这篇关于甲骨文.如果参数为null，则全选，否则返回特定项目的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！