oracle用一条语句实现对传入不同条件执行不同分支的技巧

对于表T，有object_id,按照where object_id =:oid查询，如果传入的oid是NULL，则查询全部（ 希望走全表扫描 ），相当于FROM t WHERE 1=1,如果object_id传入非NULL值（ 希望走索引扫描 ），则执行查询FROM t WHERE object_id =:oid,这是常见的根据传入的值不同，执行不同条件查询的语句。

  构造数据如下：

dingjun123@ORADB> DROP TABLE t;
Table dropped.

dingjun123@ORADB> CREATE TABLE t AS SELECT * FROM dba_objects;
Table created.

dingjun123@ORADB> CREATE INDEX idx_t ON t(object_id);
Index created.

dingjun123@ORADB> EXEC dbms_stats.gather_table_stats(NULL,'T',cascade => TRUE);
PL/SQL procedure successfully completed.

dingjun123@ORADB> select count(*) from t;
  COUNT(*)
----------
     75236

   根据开头说的需求，这个语句应该怎么写呢？如果传入的是NULL，则WHERE 1=1查找全部，如果传入的是非NULL，则执行object_id=:oid。显然地，这是一个
IF condition1 THEN
  do something1; 
ELSE 
 do something2;
END IF;
  {BANNED}最佳容易想到的是：
写法1：使用OR条件

dingjun123@ORADB> set autotrace traceonly
dingjun123@ORADB> var oid number;
dingjun123@ORADB> exec :oid := 20;
PL/SQL procedure successfully completed.
dingjun123@ORADB> select * from t WHERE   :OID IS NULL OR object_id=:OID ;
1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3763 |   356K|   300   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  3763 |   356K|   300   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:OID IS NULL OR "OBJECT_ID"=TO_NUMBER(:OID))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1075  consistent gets
          0  physical reads
          0  redo size
       1389  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    虽然绑定变量下autotrace可能不准确，但是这里是准确的，走的全表扫描，返回1行，逻辑读1075,这显然是不可接受的，应该走索引才对。当然，如果不用绑定变量的话，就可以走索引了：

dingjun123@ORADB> select * from t WHERE  20 IS NULL OR object_id=20 ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    97 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    97 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=20)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
       1392  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    为什么用绑定变量没有走索引呢，按理说有binding peeking,ORACLE应该知道传入的是20，完全可以将带绑定变量的语句转为上面字面量一样的语句，然后走索引的计划，但是事实很遗憾，通过10053发现，ORACLE根本不进行这种转换：

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0ed92eac  bln=22  avl=02  flg=05
  value=20
 Bind#1
  No oacdef for this bind.
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 1332 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T
    #Rows: 75236  #Blks:  1095  AvgRowLen:  97.00
Index Stats::
  Index: IDX_T  Col#: 4
    LVLS: 1  #LB: 167  #DK: 75235  LB/K: 1.00  DB/K: 1.00  CLUF: 1307.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T[T] 
  Table: T  Alias: T
    Card: Original: 75236.000000  Rounded: 3763  Computed: 3762.75  Non Adjusted: 3762.75
  Access Path: TableScan
    Cost:  299.85  Resp: 299.85  Degree: 0
      Cost_io: 298.00  Cost_cpu: 29503827
      Resp_io: 298.00  Resp_cpu: 29503827
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 299.85  Degree: 1  Resp: 299.85  Card: 3762.75  Bytes: 0

    从上面可以看出，ORACLE根本不考虑使用索引，直接按照5%的selectivity走全表扫描。那么如何才能走索引呢？其实这种常见的SQL类型，ORACLE已经考虑到了，并且有个隐含参数来控制：

dingjun123@ORADB> SELECT  VALUE,DESCRIPTION FROM all_parameters WHERE NAME='_or_expand_nvl_predicate';
VALUE                DESCRIPTION
-------------------- ------------------------------------------------------------
TRUE                 enable OR expanded plan for NVL/DECODE predicate
1 row selected.

   这个参数的意思是针对NVL和DECODE的谓词，可以进行OR扩展，如下：
写法2：使用NVL或DECODE
使用NVL和DECODE改写如下：

dingjun123@ORADB> select * from t WHERE   object_id = nvl(:OID,object_id) ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1189289681
---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       | 75236 |  7126K|   303   (1)| 00:00:04 |
|   1 |  CONCATENATION                |       |       |       |            |          |
|*  2 |   FILTER                      |       |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | T     | 75235 |  7126K|   301   (1)| 00:00:04 |
|*  4 |   FILTER                      |       |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| T     |     1 |    97 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:OID IS NULL)
   3 - filter("OBJECT_ID" IS NOT NULL)
   4 - filter(:OID IS NOT NULL)
   6 - access("OBJECT_ID"=:OID)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size
       1389  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

     写成SELECT* FROM t WHERE   object_id = decode(:OID,null,object_id,:OID) ;计划与NVL的一样（略）。 现在的计划，ORACLE实际是转换成两条语句，然后进行类似于UNION ALL的操作。注意看计划中的 FILTER 操作， FILTER操作的子操作，如果是单个子操作，那么就会先执行FILTER（父操作）,满足FILTER条件的，则执行子操作 ，否则不执行子操作（如果FILTER有2个子操作，则类似于NESTED LOOPS的操作） 。 看ID=2的FILTER条件是:OID IS NULL,ID=4的FILTER操作:OID IS NOT NULL,他们两个完全是互斥条件，所以，对于传入的:OID,肯定只能执行一个分支：要么执行全表扫描（传入NULL），要么执行索引扫描（传入非NULL值） 。这也就实现了前面说的IF .... ELSE ....END IF的操作。

 当然，NVL和DECODE改写是受隐含参数 _or_expand_nvl_predicate影响的，如果禁用，则OR扩展失效，走全表扫描：

dingjun123@ORADB> ALTER SESSION SET "_or_expand_nvl_predicate"=FALSE;
Session altered.

dingjun123@ORADB> select * from t WHERE   object_id = decode(:OID,null,object_id,:OID) ;
1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    97 |   301   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    97 |   301   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=DECODE(:OID,NULL,"OBJECT_ID",:OID))

     注意，NVL2的写法，也是全表扫描： select * from t WHERE   object_id = nvl2(:OID,:OID,object_id) ;  还有一个值得注意的地方：如果:OID传入的是非NULL值，那就等价于FROM t WHERE object_id=:oid， 如果传入的是NULL，那么NVL和DECODE写法会转为执行FROM t where object_id =object_id，等价于FROM t where object_id is not null(从计划中的谓词也可以看出来），这样如果object_id存储了NULL值，那么NVL和DECODE写法会丢失数据，但是原来的OR写法 select * from t WHERE   :OID IS NULL OR object_id=:OID，很显然是考虑了object_id存储NULL的情况 。为了解决这个问题，如果原始需求需要object_id为NULL的情况（ 除非原始需求有where过滤掉object_id为NULL或者object_id有NOT NULL约束，那么可以完全用NVL,DECODE改写 ），用第三种方法：

写法3：使用UNION ALL
  因为写法1 OR写法，考虑了object_id为NULL的情况，但是走不了索引，写法2 NVL/DEOCDE可以走索引，但是丢失了object_id为NULL的情况 ，那么第三种方法就是既考虑object_id为NULL的情况又要对传入:oid为非NULL的时候走索引，NULL的时候走全表扫描，也就是将前面的NVL/DECODE OR扩展改为显式的UNION ALL语句：

dingjun123@ORADB> select * from t WHERE  :OID IS NULL
  2  UNION ALL
  3  SELECT * FROM t WHERE :OID IS NOT NULL AND object_id =:OID ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1690602373
---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       | 75237 |  7126K|   303   (2)| 00:00:04 |
|   1 |  UNION-ALL                    |       |       |       |            |          |
|*  2 |   FILTER                      |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL          | T     | 75236 |  7126K|   301   (1)| 00:00:04 |
|*  4 |   FILTER                      |       |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| T     |     1 |    97 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:OID IS NULL)
   4 - filter(:OID IS NOT NULL)
   6 - access("OBJECT_ID"=TO_NUMBER(:OID))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1389  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

    很显然，现在考虑了NULL了，对比3类写法传入:oid是NULL的结果：

dingjun123@ORADB> exec :oid := null;
PL/SQL procedure successfully completed.
--原始数据75236条
dingjun123@ORADB> SELECT COUNT(*) from t ;
  COUNT(*)
----------
     75236
1 row selected.
--写法1考虑NULL情况
dingjun123@ORADB> select count(*) from t WHERE   :OID IS NULL OR object_id=:OID ;
  COUNT(*)
----------
     75236
1 row selected.

--NVL/DECODE写法未考虑NULL情况，少1条数据
dingjun123@ORADB> SELECT COUNT(*) from t WHERE   object_id = decode(:OID,null,object_id,:OID) ;
  COUNT(*)
----------
     75235
1 row selected.

dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id = nvl(:OID, object_id);
  COUNT(*)
----------
     75235
1 row selected.

--写法3 UNION ALL写法考虑了NULL情况
dingjun123@ORADB> SELECT COUNT(*)
  2  FROM (
  3  SELECT * FROM t WHERE :OID IS NULL
  4  UNION ALL
  5  SELECT *  FROM t WHERE :OID IS NOT NULL AND object_id =:OID
  6  ) ;
  COUNT(*)
----------
     75236
1 row selected.

     本文讨论了对传入不同值，执行不同分支条件的SQL语句及其优化方式，在ORACLE中，很多常见的需求，ORACLE会进行特定的查询转换，而有时候，这种转换是针对特定的语句写法进行的，比如本文中的使用NVL/DECODE代替OR写法，立马ORACLE就可以识别。另外本文还讨论了NVL和DECODE写法在特定需求下会丢失数据的问题，{BANNED}最佳后用UNION ALL来满足考虑NULL因素以及该走索引走索引该全表扫描全表扫描的问题。