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算子实现走各区间索引扫描提高效率。