个人简介
about me:oracle ace pro,optimistic,passionate and harmonious. focus on oracle,mysql and other database programming,peformance tuning,db design, j2ee,linux/aix,architecture tech,etc
文章分类
全部博文（169）
- cbo（54）
- tuning&performan（32）
- concepts&archite（4）
- troubleshooting（5）
- new feature（7）
- sql（52）
- pl/sql（8）
- miscellaneous（3）
- mysql（4）
- 未分配的博文（0）
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2024-10-18 10:09:58
对于表t，有object_id,按照where object_id =:oid查询，如果传入的oid是null，则查询全部（ 希望走全表扫描 ），相当于from t where 1=1,如果object_id传入非null值（ 希望走索引扫描 ），则执行查询from t where object_id =:oid,这是常见的根据传入的值不同，执行不同条件查询的语句。
构造数据如下：
根据开头说的需求，这个语句应该怎么写呢？如果传入的是null，则where 1=1查找全部，如果传入的是非null，则执行object_id=:oid。显然地，这是一个
if condition1 then
do something1;
else
do something2;
end if;
{banned}最佳容易想到的是：
写法1：使用or条件
虽然绑定变量下autotrace可能不准确，但是这里是准确的，走的全表扫描，返回1行，逻辑读1075,这显然是不可接受的，应该走索引才对。当然，如果不用绑定变量的话，就可以走索引了：
为什么用绑定变量没有走索引呢，按理说有binding peeking,oracle应该知道传入的是20，完全可以将带绑定变量的语句转为上面字面量一样的语句，然后走索引的计划，但是事实很遗憾，通过10053发现，oracle根本不进行这种转换：
从上面可以看出，oracle根本不考虑使用索引，直接按照5%的selectivity走全表扫描。那么如何才能走索引呢？其实这种常见的sql类型，oracle已经考虑到了，并且有个隐含参数来控制：
这个参数的意思是针对nvl和decode的谓词，可以进行or扩展，如下：
写法2：使用nvl或decode
使用nvl和decode改写如下：
写成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扩展失效，走全表扫描：
注意，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语句：
很显然，现在考虑了null了，对比3类写法传入:oid是null的结果：
本文讨论了对传入不同值，执行不同分支条件的sql语句及其优化方式，在oracle中，很多常见的需求，oracle会进行特定的查询转换，而有时候，这种转换是针对特定的语句写法进行的，比如本文中的使用nvl/decode代替or写法，立马oracle就可以识别。另外本文还讨论了nvl和decode写法在特定需求下会丢失数据的问题，{banned}最佳后用union all来满足考虑null因素以及该走索引走索引该全表扫描全表扫描的问题。
构造数据如下：
|
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因素以及该走索引走索引该全表扫描全表扫描的问题。
给主人留下些什么吧！~~