oracle的optimizer会对一些sql语句进行查询转换，比如：
- 合并视图
- 子查询非嵌套化
- inlist转换
下面讲讲遇到的in list转化优化的案例:
create table test(
col1 varchar2(12)
col2 number
ext varchar2(4000)
); create index test_ind on test(user_id, col2);
create sequence seq_test cache 200;
第一步：准备一些测试数据（10个线程随机的插入数据）：
#!/bin/sh
for((i=1;i<=$1;i++))
do
/home/oracle/insert.sh &
done
#######################################################
#!/bin/sh
. /home/oracle/.bash_profile
sqlplus -S /nolog<<EOF
conn test/ali88
declare
type arraylist is table of varchar2(20 byte);
arr_user arraylist;
ran number;
begin
arr_user := arraylist();arr_user.extend(3);arr_user(1):='xpchild001';
arr_user(2):='xpchild002';arr_user(3):='xpchild003';
ran :=dbms_random.value(1,3);
while(1>0) loop
insert into test(col1,col2,ext)values(arr_user(ran),seq_test.nextval,dbms_random.string('|', 300));
commit;
DBMS_LOCK.SLEEP(0.05);
end loop;
end;
/
EOF
下面看这个语句的执行计划：
delete from test t where col1 =:1 and col2 in
( :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21); --------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 948 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| test | 4 | 948 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | test_ind | 4 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("col1"=:1)
filter("col2"=TO_NUMBER(:2) OR "col2"=TO_NUMBER(:3) OR
.........
"col2"=TO_NUMBER(:21))
分析：对于test_ind(col1,col2)这样的组合索引，oracle的优化器使用了access+filter的扫描方式，而对于热点表，或者col1存在大量记录的时候，
这样的扫描会从col1找到最小的col2,顺着leaf节点的链表，找到col2的最大值的区间里，进行filter，看下autotrace后的结果：
Statistics
----------------------------------------------------------
12389 consistent gets
20 rows
这里扫描了col1=:1前导列的所有leaf块，所以尽管只有20条记录，却有12389的逻辑读。
下面对这个sql进行inlist的查询转换：
ops$admin@orcl>alter session set optimizer_index_caching=100; delete /*+ use_concat*/from test t where col1 =:1 and col2 in
( :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21); ---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 948 | 3 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| test | 4 | 948 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | test_ind | 4 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("col1"=:1 AND ("col2"=TO_NUMBER(:2) OR "col2"=TO_NUMBER(:3) OR
"col2"=TO_NUMBER(:4) OR "col2"=TO_NUMBER(:5) OR "col2"=TO_NUMBER(:6) OR
......
/*+
BEGIN_OUTLINE_DATA
NUM_INDEX_KEYS(@"DEL$1" "T"@"DEL$1" "test_ind" 2)
INDEX(@"DEL$1" "T"@"DEL$1" ("test"."col1"
"test"."col2"))
OUTLINE_LEAF(@"DEL$1")
ALL_ROWS
OPT_PARAM('optimizer_index_caching' 100)
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_index_join_enabled' 'false')
OPT_PARAM('query_rewrite_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
注意：oracle的优化器会对in 列表的查询转换为or的查询，使用use_concat的hint提示，使oracle可以把or的操作转化为concatenate的union all操作。
但是这里如果想要转化为inlist的eterator操作，必须还要调整optimizer_index_caching，可以在system或者session级别，optimizer_index_caching的值的范围
是0到100，表示的是百分比，这个参数影响oracle优化器在选择index时的cost计算，这里设置成100，表示扫描过的root，branch节点块都cache在buffer里，所以每次
iterate从root到branch再到leaf节点时，oracle优化器认为cost会比较小，倾向于使用inlist iterator。
看一下autotrace后的结果：
Statistics
----------------------------------------------------------
81 consistent gets
20 rows
即每条记录平均三个逻辑读，从root->branch->leaf->table block。
如果在生产环境中可以使用profile来固定上面的执行计划，这样不用更改任何的代码逻辑:
declare
v_hints sys.sqlprof_attr;
sql_fulltext clob;
begin
select SQL_FULLTEXT
into sql_fulltext
from v$sqlarea
where sql_id = 'xxxxxx';
v_hints := sys.sqlprof_attr('NUM_INDEX_KEYS(@"DEL$1" "T"@"DEL$1" "test_ind" 2)',
'INDEX(@"DEL$1" "T"@"DEL$1" ("test"."col1"',
'"test"."col2"))',
'OUTLINE_LEAF(@"DEL$1")',
'ALL_ROWS',
'OPT_PARAM(''optimizer_index_caching'' 100)');
dbms_sqltune.import_sql_profile(sql_fulltext,
v_hints,
'test',
force_match => true);
end;
/