oracle查询转换_inlist转换
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; /
