按照不同输入值，执行不同分支条件的语句优化

最新推荐文章于 2021-04-06 22:51:07 发布

编程小强

最新推荐文章于 2021-04-06 22:51:07 发布

阅读量2k

点赞数

分类专栏： Oracle开发

Oracle开发专栏收录该内容

38 篇文章 0 订阅

订阅专栏

转载自：http://blog.chinaunix.net/uid-7655508-id-3763232.html

在ITPUB上遇到一个很有意思的问题（ http://www.itpub.net/thread-1796130-1-1.html ），对于表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;
最容易想到的是：
写法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写法在特定需求下会丢失数据的问题，最后用UNION ALL来满足考虑NULL因素以及该走索引走索引该全表扫描全表扫描的问题。