oracle in查询可以用list,学习猿地-oracle查询转换_inlist转换

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

子查询非嵌套化

inlist转换

下面讲讲遇到的in list转化优化的案例:

create tabletest(

col1varchar2(12)

col2numberextvarchar2(4000)

);create index test_ind on test(user_id, col2);create sequence seq_test cache 200;

第一步：准备一些测试数据(10个线程随机的插入数据)：

#!/bin/shfor((i=1;i<=$1;i++))

do/home/oracle/insert.sh &done

#######################################################

#!/bin/sh

./home/oracle/.bash_profile

sqlplus-S /nolog<

conn test/ali88declaretype arraylistis table of varchar2(20byte);

arr_user arraylist;

rannumber;beginarr_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) loopinsert into test(col1,col2,ext)values(arr_user(ran),seq_test.nextval,dbms_random.string(‘|‘, 300));commit;

DBMS_LOCK.SLEEP(0.05);endloop;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 byoperation 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

----------------------------------------------------------

12389consistent gets20 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 byoperation 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

----------------------------------------------------------

81consistent gets20 rows

即每条记录平均三个逻辑读，从root->branch->leaf->table block。

如果在生产环境中可以使用profile来固定上面的执行计划，这样不用更改任何的代码逻辑:

declarev_hints sys.sqlprof_attr;

sql_fulltext clob;begin

selectSQL_FULLTEXTintosql_fulltextfromv$sqlareawhere 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;/