最近在优化一条sql语句，做一个测试例子测试看看。遇到一些问题记录一下:
1.建立环境：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id1 number,id2 number,name varchar2(100) ) pctfree 99;
alter table t modify(id1 not null);
alter table t modify(id2 not null);
insert into t select rownum id,1e4-rownum+1 id2 ,lpad('x',100,'x') from dual connect by level <=1e4;
commit ;
--这样建立了一张大表，记录10000行.注意这张表id1,id2没有等于0的记录。
create index i_t_id1 on t(id1);
create index i_t_id2 on t(id2);
exec dbms_stats.gather_table_stats(user,'T',cascade=>TRUE);
2.测试：
--注意：生产环境cursor_sharing=force。
--模拟语句如下：程序没有使用绑定变量，直接带数值的。
select * from t where (id1=440 or 440=0) and (id2=0 or 0=0);
select * from t where (id1=0 or 0=0) and (id2=9561 or 9561=0);
select * from t where (id1=440 or 440=0) and (id2=9561 or 9561=0);
--如果使用绑定变量，语句如下：
select * from t where (id1=:v1 or :v1=0) and (id2=:v2 or :v2=0);
-- 可以看出开发的本意是程序既可以查询id1字段也可以查询id2字段，一个很牛逼的设计!
-- 初始带入的参数都是0。
3.在cursor_sharing=exact的情况下：
SCOTT@test> select * from t where (id1=440 or 440=0) and (id2=0 or 0=0);
ID1 ID2 NAME
---------- ---------- --------------------
440 9561 xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 627f4u46pmh2a, child number 0
-------------------------------------
select * from t where (id1=440 or 440=0) and (id2=0 or 0=0)
Plan hash value: 1183254286
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID1 | 1 | 1 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID1"=440)
SCOTT@test> select * from t where (id1=0 or 0=0) and (id2=9561 or 9561=0);
ID1 ID2 NAME
---------- ---------- --------------------
440 9561 xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID avvjxv0a4dvs5, child number 0
-------------------------------------
select * from t where (id1=0 or 0=0) and (id2=9561 or 9561=0)
Plan hash value: 3974417878
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID2 | 1 | 1 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("ID2"=9561)--可以发现cursor_sharing=exact的情况下，oracle知道0=0为真9561=0为假，这样 (id1=0 or 0=0) and (id2=9561 or 9561=0)
--实际变为id2=9561，这样的情况下可以使用i_t_id2索引。
--看看执行计划2 - access("ID2"=9561)就很容易明白。
4.在cursor_sharing=force的情况下：
SCOTT@test> select * from t where (id1=430 or 430=0) and (id2=0 or 0=0);
ID1 ID2 NAME
---------- ---------- --------------------
430 9571 xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
--注意我换了1个值，440=>430.原来的语句还是会使用索引。
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID 5ff9cdq6bmvgt, child number 1
-------------------------------------
select * from t where (id1=:"SYS_B_0" or :"SYS_B_1"=:"SYS_B_2") and
(id2=:"SYS_B_3" or :"SYS_B_4"=:"SYS_B_5")
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2740 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 2740 (1)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(((:SYS_B_1=:SYS_B_2 OR "ID1"=:SYS_B_0) AND
(:SYS_B_4=:SYS_B_5 OR "ID2"=:SYS_B_3)))
--换成这样，oracle就瞎眼了，不知道里面的内在逻辑，仅仅选择全表扫描。
使用提示呢？
SCOTT@test> select /*+ index(t i_t_id1) */ * from t where (id1=430 or 430=0) and (id2=0 or 0=0);
ID1 ID2 NAME
---------- ---------- --------------------
430 9571 xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID 18buqaphf9pzt, child number 0
-------------------------------------
select /*+ index(t i_t_id1) */ * from t where (id1=:"SYS_B_0" or
:"SYS_B_1"=:"SYS_B_2") and (id2=:"SYS_B_3" or :"SYS_B_4"=:"SYS_B_5")
Plan hash value: 2057376682
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 10026 (100)|
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10026 (1)|
| 2 | INDEX FULL SCAN | I_T_ID1 | 10000 | 22 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(((:SYS_B_1=:SYS_B_2 OR "ID1"=:SYS_B_0) AND
(:SYS_B_4=:SYS_B_5 OR "ID2"=:SYS_B_3)))
--这样做INDEX FULL SCAN 扫描，效率不如全表扫描。
5.解决方法：
1.修改参数cursor_sharing=exact，这样可以彻底解决这样，实际上这样的类似的语句有许多处。
--补充1点，我个人认为如果现在开发的OLTP系统再没有使用并且合理的使用绑定变量，这样的项目可以讲就是一个豆腐渣工程！
--oracle相关优化的书籍对这个问题多次提到。不要简单以为修改参数cursor_sharing=force可以解决这个问题。
2.当然是修改程序代码。程序中不要出现or，并且改成绑定变量。
3.实际上还有一种方法就是加入提示 /*+ CURSOR_SHARING_EXACT */ ，这样就不会转换里面的常量数值，从而解决这个问题。
SCOTT@test> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ---------------------------------------- -----------------------
cursor_sharing string FORCE
SCOTT@test> select /*+ CURSOR_SHARING_EXACT */ * from t where (id1=450 or 450=0) and (id2=0 or 0=0);
ID1 ID2 NAME
---------- ---------- --------------------
450 9551 xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 058w6jcj7twzr, child number 0
-------------------------------------
select /*+ CURSOR_SHARING_EXACT */ * from t where (id1=450 or 450=0)
and (id2=0 or 0=0)
Plan hash value: 1183254286
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID1 | 1 | 1 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID1"=450)