oracle查询转换_inlist转换

oracle的optimizer会对一些sql语句进行查询转换，比如：

1. 合并视图
2. 子查询非嵌套化
3. 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;
/