ORACLE具有强大的优化器，一般来说，组合索引在ORACLE里不管是单表还是关联查询，都可以选择optimal的执行计划，只要统计信息等是准确的。
MySQL的优化器相对来说，要弱不少，很多功能不够健全，单表对于组合索引包含IN的处理貌似没有什么问题，但是JOIN情况下，包含IN LIST组合索引的表是被驱动表，则问题比较严重。
1.ORACLE组合索引包含IN LIST的执行计划
drop table t1;
drop table t2;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
--建立组合索引
create index idx1_t1 on t1(object_name,owner);
create index idx1_t2 on t2(object_id,owner);
--收集统计信息
exec BMS_STATS.GATHER_TABLE_STATS(ownname=>'dingjun123',tabname=>'t1',no_invalidate=>false);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'dingjun123',tabname=>'t2',no_invalidate=>false);
in (list)相当于多个OR的组合，其实也是个范围条件，如果前导列是in条件，则排序键必须包含前导列才可能消除排序，排序列没有前导列，则必须前导列在where条件里是等值才可以消除排序。
但是in (list)又是个特殊的范围查询，因为可以转为or条件，这样与其他列组合，可以组合为多个等值条件，这样做索引扫描时，可以利用inlist iterator算子提高效率。
比如：
a=1 and b in (1,2) 可以转为 a=1 and (b=1 or b=2)则转为两个索引区间：
a=1 and b=1
a=1 and b=2
然后使用inlist iterator算子，两个列条件都可以参与index access。
a in (1,2) and b in (1,2)可以转为
(a=1 or a=2 ) and (b=1 or b=2)，转为4个区间：
a=1 and b=1
a=1 and b=2
a=2 and b=1
a=2 and b=2
然后利用inlist iterator算子，都可以利用a,b条件index access。
ORACLE处理组合索引含有in条件时，对于in条件列是索引非前导列，也会考虑选择性：
1）如果前导列选择性很好，后面的列条件是in,可能index access只有前导列，in的作为index filter，这时候不用inlist iterator算子。
2）如果前导列选择性不好，则会调用inlist iterator算子，转为多个索引区间扫描，这样in条件也会index access。
如果in条件是索引前导列，因为索引前导列要参与index access,所以基本都会采用inlist iterator算子，转为多个索引区间，这样都可以参与index access。
1)索引IDX1_T1(object_name,owner),对应的非前导列owner是in条件有多个值
这时候是否走inlist iterator要看前导列的选择性是不是很好，如果很好，index access
只需要前导列即可，非前导列in的index filter
如果前导列选择性不好，则会将in list转为or,然后合并前导列，扫描多个索引区间，
使用inlist iterator算子，则多个列都可以参与index access
SQL和执行计划如下：
select * from t1 where t1.object_name='T1' and t1.owner in ('SYS','DINGJUN123');
Execution Plan
----------------------------------------------------------
Plan hash value: 1182251260
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 196 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_T1 | 2 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_NAME"='T1')
filter("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS')
可以从执行计划看到对于索引是(object_name,owner)的，owner非前导列用了in,没有参与index access,而是
index filter，原因是object_name选择性很好，等值查询选择性1/num_distinct=.000016423,不调用inlist iterator算子。
对应列基数和选择率如下：
select column_name,num_distinct,density from dba_tab_col_statistics where table_name='T1' and column_name in('OWNER','OBJECT_NAME');
COLUMN_NAME NUM_DISTINCT DENSITY
-------------------- ------------ ----------
OWNER 27 .037037037
OBJECT_NAME 60892 .000016423
如果前导列object_name也是in,因为选择性好，这里也不用第二列，前导列in转为or,调用INLIST ITERATOR：
select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123');
Execution Plan
----------------------------------------------------------
Plan hash value: 1236450337
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 528 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 4 | 528 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX1_T1 | 1 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES')
filter("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2864 bytes sent via SQL*Net to client
473 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
如果都是等值的，则都参与index access:
select * from t1 where t1.object_name ='DBA_OBJECTS' and t1.owner ='SYS';
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2488322393
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 132 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_T1 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_NAME"='DBA_OBJECTS' AND "T1"."OWNER"='SYS')
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2692 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2)如果前导列的选择性不够好，组合的选择性才好，如果有in条件，则会转为索引扫描多个区间，
调用INLIST ITERATOR，这时候in条件可以参与index access
drop index idx1_t1;
--创建以owner为前导列的索引，owner选择性不好：
create index idx2_t1 on t1(owner,object_name);
SQL如下：
select * from t1 where t1.object_name='DBA_OBJECTS' and t1.owner in ('SYS','DINGJUN123');
从执行计划可以看到，access里owner转为or然后 and object_name,两个列都可以index access,调用INLIST ITERATOR算子：
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3655239226
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 264 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 264 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX2_T1 | 2 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
"T1"."OBJECT_NAME"='DBA_OBJECTS')
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2692 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果两个列都是in list,因为前导列owner选择性不好，组合选择性好，则会转为多个索引区间，这里索引区间对应2*2=4个,如下所示：
object_name='DBA_OBJECTS' and owner='SYS'
object_name='DBA_OBJECTS' and owner='DINGJUN123'
object_name='DBA_TABLES' and owner='SYS'
object_name='DBA_TABLES' and owner='DINGJUN123'
SQL如下：
select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123');
从执行计划可以看到，可以看到都能参与index access，走INLIST ITERATOR:
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3655239226
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 528 | 8 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 4 | 528 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX2_T1 | 4 | | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES'))
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
2864 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
3）多表JOIN情况下的inlist iterator：
如果SQL是JOIN，T1表是被驱动的走NESTED LOOPS：
先创建索引：让object_name在前，选择性好：
--第二个索引先删掉
drop index idx2_t1;
--创建object_name为前导列的索引：
create index idx1_t1 on t1(object_name,owner);
执行计划走NESTED LOOPS，t1被驱动，t1表走idx1_t1索引，因为前导列object_name是等值条件，owner是in条件，相当于范围，可以转为OR。
在11g里这个ID=6的访问谓词显示有问题，对应的owner in条件也是在index access中，单表查询时，11g里只有object_name,因为object_name 选择性好，JOIN时都参与index access,这是算法上的问题，不过这个不影响性能。
和19C的谓词部分显示不一样，算法不一样，19c还是选择性选择性好的前导列访问：11g都参与index access,有INLIST ITERATOR，object_name前导列是关联列，
SQL和执行计划如下：
select *
from t1,t2
where t1.object_name = t2.object_name
and t1.owner in ('SYS','DINGJUN123')
and t2.object_id <10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3442654963
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1372 | 38 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 7 | 1372 | 38 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 7 | 686 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX1_T2 | 7 | | 2 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN | IDX1_T1 | 2 | | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 98 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"<10)
6 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43 consistent gets
0 physical reads
0 redo size
3314 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
19C执行计划ID=5显示的谓词对应owner in ('SYS','DINGJUN123')变成filter("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS'),
19C没有INLIST ITERATOR
index access的列只有access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
19C是比较准确的：
Execution Plan
----------------------------------------------------------
Plan hash value: 1792326996
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 2112 | 27 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 8 | 2112 | 27 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 2112 | 27 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 8 | 1056 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX1_T2 | 8 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX1_T1 | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 132 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"<10)
5 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
filter("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS')
如果19c里使用OPTIMIZER_FEATURES_ENABLE('11.2.0.4')，则会显示和11g一样的计划，都走index access,有INLIST ITERATOR，
这么看对于in非前导列的JOIN被驱动走NESTED LOOPS的执行计划，有改进，感觉19C才是对的，因为使用inlist的cost更大，因为前导列选择性足够好：
select/*+OPTIMIZER_FEATURES_ENABLE('11.2.0.4')*/ *
from t1,t2
where t1.object_name = t2.object_name
and t1.owner in ('SYS','DINGJUN123')
and t2.object_id <10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3442654963
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 2112 | 43 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 8 | 2112 | 43 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 8 | 1056 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX1_T2 | 8 | | 2 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN | IDX1_T1 | 2 | | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 132 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"<10)
6 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS'))
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
5598 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
下面删除idx1_t1,创建owner索引：
drop index idx1_t1;
--创建以owner为前导列的索引，owner选择性不好,且不是关联列，看能否全部列走索引。
drop index idx2_t1;
###这里创建三个列索引，将选择性好的放后面：
create index idx2_t1 on t1(owner,object_type,object_name);
SQL如下：
select *
from t1,t2
where t1.object_name = t2.object_name
and t1.owner in ('SYS','DINGJUN123')
and t1.object_type in('TABLE'，'VIEW')
and t2.object_id <10;
从执行计划看，使用INLIST ITERATOR，和单表测试一样：
这里相当于每一行t2传递给t1,然后扫描4个索引区间，如果将选择性好的object_name放前面则不需要很多区间，
比如索引(owner,object_name,object_type),则object_type in list可能不参与index access,而走index filter:
Execution Plan
----------------------------------------------------------
Plan hash value: 1219290223
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 2112 | 59 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 8 | 2112 | 59 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 32 | 2112 | 59 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 8 | 1056 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX1_T2 | 8 | | 2 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN | IDX2_T1 | 4 | | 5 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 132 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"<10)
6 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
("T1"."OBJECT_TYPE"='TABLE' OR "T1"."OBJECT_TYPE"='VIEW') AND
"T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
4)组合索引前导列是IN多个值能否消除排序，取决于排序列是否有前导列
drop index idx1_t1;
drop index idx2_t1;
create index idx2_t1 on t1(owner,object_name);
select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123')
order by owner desc,object_name desc;
因为排序列按照组合索引顺序，且access两个列都用到，排序方向一致，可以消除排序：
Execution Plan
----------------------------------------------------------
Plan hash value: 3833075694
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 528 | 8 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 4 | 528 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| IDX2_T1 | 4 | | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES'))
排序列无前导列，只有第二个列，则必须前导列是等值条件才可以消除排序，这里前导列是IN有2个分支，可以看到没有消除排序，
执行计划有：SORT ORDER BY
SQL和执行计划如下：
select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123')
order by object_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 4272791971
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 528 | 9 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 528 | 9 (12)| 00:00:01 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 4 | 528 | 8 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX2_T1 | 4 | | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES'))
总结：在Oracle里组合索引inlist,不管是单表还是关联，不管多表JOIN的inlist列是否是关联列，都可以充分利用索引扫描，有时会将非前导列放到filter里，也是因为前导列选择性好，不会影响效率。
对于排序来说，因为in相当于范围，如果where是in条件的前导列，排序里没有前导列，则不能消除排序，如果排序列包含此前导列，且索引列方向一致，也可以消除排序。
在Oracle里组合索引in在单表、多表JOIN、排序里没有什么问题，遵循索引的leftmost prefix访问规则，inlist组合不管是否是前导列，是否单表，多表JOIN，都能转为OR，与其他索引列组合转为多个索引扫描区间，使用INLIST ITERATOR算子实现走各区间索引扫描提高效率。
1072
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
