ORACLE vs MySQL 对组合索引包含IN LIST执行计划研究(ORACLE部分)_PART1

本文主要研究下组合索引包含in条件（多个值），在单表查询，关联查询这两种SQL查询结果在ORACLE和MySQL里的区别。

ORACLE具有强大的优化器，一般来说，组合索引在ORACLE里不管是单表还是关联查询，都可以选择optimal的执行计划，只要统计信息等是准确的。

MySQL的优化器相对来说，要弱不少，很多功能不够健全，单表对于组合索引包含IN的处理貌似没有什么问题，但是JOIN情况下，
包含IN LIST组合索引的表是被驱动表，则问题比较严重，卖个关子，详细见本文MySQL部分讲述。

1.ORACLE组合索引包含IN LIST的执行计划研究

先上建表语句：

点击(此处)折叠或打开

  1. drop table t1;
  2. drop table t2;
  3. create table t1 as select * from dba_objects;
  4. create table t2 as select * from dba_objects;
  5. --建立组合索引
  6. create index idx1_t1 on t1(object_name,owner);
  7. create index idx1_t2 on t2(object_id,owner);
  8. --收集统计信息
  9. exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'dingjun123',tabname=>'t1',no_invalidate=>false);
  10. 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和执行计划如下：

点击(此处)折叠或打开

  1. select * from t1 where t1.object_name='T1' and t1.owner in ('SYS','DINGJUN123');
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 1182251260
  7. ---------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ---------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 2 | 196 | 4 (0)| 00:00:01 |
  11. | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 196 | 4 (0)| 00:00:01 |
  12. |* 2 | INDEX RANGE SCAN | IDX1_T1 | 2 | | 3 (0)| 00:00:01 |
  13. ---------------------------------------------------------------------------------------
  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------
  16.    2 - access("T1"."OBJECT_NAME"='T1')
  17.        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算子。

对应列基数和选择率如下：

点击(此处)折叠或打开

  1. select column_name,num_distinct,density from dba_tab_col_statistics where table_name='T1' and column_name in('OWNER','OBJECT_NAME');
  3. COLUMN_NAME NUM_DISTINCT DENSITY
  4. -------------------- ------------ ----------
  5. OWNER 27 .037037037
  6. OBJECT_NAME 60892 .000016423

如果前导列object_name也是in,因为选择性好，这里也不用第二列，前导列in转为or,调用INLIST ITERATOR：

点击(此处)折叠或打开

  1. select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123');
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 1236450337
  7. ------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 4 | 528 | 5 (0)| 00:00:01 |
  11. | 1 | INLIST ITERATOR | | | | | |
  12. | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 4 | 528 | 5 (0)| 00:00:01 |
  13. |* 3 | INDEX RANGE SCAN | IDX1_T1 | 1 | | 4 (0)| 00:00:01 |
  14. ------------------------------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17.    3 - access("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES')
  18.        filter("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS')
  19. Statistics
  20. ----------------------------------------------------------
  21.           0 recursive calls
  22.           0 db block gets
  23.           9 consistent gets
  24.           0 physical reads
  25.           0 redo size
  26.        2864 bytes sent via SQL*Net to client
  27.         473 bytes received via SQL*Net from client
  28.           2 SQL*Net roundtrips to/from client
  29.           0 sorts (memory)
  30.           0 sorts (disk)
  31.           2 rows processed
如果都是等值的，则都参与index access:

点击(此处)折叠或打开

  1. select * from t1 where t1.object_name ='DBA_OBJECTS' and t1.owner ='SYS';
  3. 1 row selected.
  5. Elapsed: 00:00:00.01
  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 2488322393
  11. -----------------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. -----------------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 1 | 132 | 4 (0)| 00:00:01 |
  15. | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 132 | 4 (0)| 00:00:01 |
  16. |* 2 | INDEX RANGE SCAN | IDX1_T1 | 1 | | 3 (0)| 00:00:01 |
  17. -----------------------------------------------------------------------------------------------
  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------
  22.    2 - access("T1"."OBJECT_NAME"='DBA_OBJECTS' AND "T1"."OWNER"='SYS')
  25. Statistics
  26. ----------------------------------------------------------
  27.           2 recursive calls
  28.           0 db block gets
  29.           7 consistent gets
  30.           0 physical reads
  31.           0 redo size
  32.        2692 bytes sent via SQL*Net to client
  33.         435 bytes received via SQL*Net from client
  34.           2 SQL*Net roundtrips to/from client
  35.           0 sorts (memory)
  36.           0 sorts (disk)
  37.           1 rows processed
2)如果前导列的选择性不够好，组合的选择性才好，如果有in条件，则会转为索引扫描多个区间，
调用INLIST ITERATOR，这时候in条件可以参与index access

点击(此处)折叠或打开

  1. drop index idx1_t1;
  2. --创建以owner为前导列的索引，owner选择性不好：
  3. create index idx2_t1 on t1(owner,object_name);
SQL如下：

点击(此处)折叠或打开

  1. 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算子：
 

点击(此处)折叠或打开

  1. Elapsed: 00:00:00.00
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 3655239226
  7. ------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 2 | 264 | 5 (0)| 00:00:01 |
  11. | 1 | INLIST ITERATOR | | | | | |
  12. | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 2 | 264 | 5 (0)| 00:00:01 |
  13. |* 3 | INDEX RANGE SCAN | IDX2_T1 | 2 | | 4 (0)| 00:00:01 |
  14. ------------------------------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  19.    3 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
  20.               "T1"."OBJECT_NAME"='DBA_OBJECTS')
  23. Statistics
  24. ----------------------------------------------------------
  25.           2 recursive calls
  26.           0 db block gets
  27.          10 consistent gets
  28.           0 physical reads
  29.           0 redo size
  30.        2692 bytes sent via SQL*Net to client
  31.         451 bytes received via SQL*Net from client
  32.           2 SQL*Net roundtrips to/from client
  33.           0 sorts (memory)
  34.           0 sorts (disk)
  35.           1 rows processed

如果两个列都是in list,因为前导列owner选择性不好，组合法务时刻选择性好，则会转为多个索引区间，这里索引区间对应2*2=4个,如下所示：

点击(此处)折叠或打开

  1. object_name='DBA_OBJECTS' and owner='SYS'
  2. object_name='DBA_OBJECTS' and owner='DINGJUN123'
  3. object_name='DBA_TABLES' and owner='SYS'
  4. object_name='DBA_TABLES' and owner='DINGJUN123'
SQL如下：

点击(此处)折叠或打开

  1. select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123');

从执行计划可以看到，可以看到都能参与index access，走INLIST ITERATOR:

点击(此处)折叠或打开

  1. Elapsed: 00:00:00.00
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 3655239226
  7. ------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 4 | 528 | 8 (0)| 00:00:01 |
  11. | 1 | INLIST ITERATOR | | | | | |
  12. | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 4 | 528 | 8 (0)| 00:00:01 |
  13. |* 3 | INDEX RANGE SCAN | IDX2_T1 | 4 | | 6 (0)| 00:00:01 |
  14. ------------------------------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  19.    3 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
  20.               ("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES'))
  22. Statistics
  23. ----------------------------------------------------------
  24.           2 recursive calls
  25.           0 db block gets
  26.          16 consistent gets
  27.           0 physical reads
  28.           0 redo size
  29.        2864 bytes sent via SQL*Net to client
  30.         469 bytes received via SQL*Net from client
  31.           2 SQL*Net roundtrips to/from client
  32. https://www.jiwenlaw.com/
  33.           0 sorts (memory)
  34.           0 sorts (disk)
  35.           2 rows processed
3）多表JOIN情况下的inlist iterator

如果SQL是JOIN，T1表是被驱动的走NESTED LOOPS：
先创建索引：让object_name在前，选择性好：

点击(此处)折叠或打开

  1. --第二个索引先删掉
  2. drop index idx2_t1;
  3. --创建object_name为前导列的索引：
  4. 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和执行计划如下：

点击(此处)折叠或打开

  1. select *
  2. from t1,t2
  3. where t1.object_name = t2.object_name
  4. and t1.owner in ('SYS','DINGJUN123')
  5. and t2.object_id <10;
  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 3442654963
  11. -----------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. -----------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 7 | 1372 | 38 (0)| 00:00:01 |
  15. | 1 | NESTED LOOPS | | | | | |
  16. | 2 | NESTED LOOPS | | 7 | 1372 | 38 (0)| 00:00:01 |
  17. | 3 | TABLE ACCESS BY INDEX ROWID| T2 | 7 | 686 | 3 (0)| 00:00:01 |
  18. |* 4 | INDEX RANGE SCAN | IDX1_T2 | 7 | | 2 (0)| 00:00:01 |
  19. | 5 | INLIST ITERATOR | | | | | |
  20. |* 6 | INDEX RANGE SCAN | IDX1_T1 | 2 | | 3 (0)| 00:00:01 |
  21. | 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 98 | 5 (0)| 00:00:01 |
  22. -----------------------------------------------------------------------------------------
  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------
  27.    4 - access("T2"."OBJECT_ID"<10)
  28.    6 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
  29.               ("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS'))
  32. Statistics
  33. ----------------------------------------------------------
  34.           1 recursive calls
  35.           0 db block gets
  36.          43 consistent gets
  37.           0 physical reads
  38.           0 redo size
  39.        3314 bytes sent via SQL*Net to client
  40.         520 bytes received via SQL*Net from client
  41.           2 SQL*Net roundtrips to/from client
  42.           0 sorts (memory)
  43.           0 sorts (disk)
  44.           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")

点击(此处)折叠或打开

  1. 19C是比较准确的：
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 1792326996
  7. -------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. -------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 8 | 2112 | 27 (0)| 00:00:01 |
  11. | 1 | NESTED LOOPS | | 8 | 2112 | 27 (0)| 00:00:01 |
  12. | 2 | NESTED LOOPS | | 8 | 2112 | 27 (0)| 00:00:01 |
  13. | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 8 | 1056 | 3 (0)| 00:00:01 |
  14. |* 4 | INDEX RANGE SCAN | IDX1_T2 | 8 | | 2 (0)| 00:00:01 |
  15. |* 5 | INDEX RANGE SCAN | IDX1_T1 | 1 | | 2 (0)| 00:00:01 |
  16. | 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 132 | 3 (0)| 00:00:01 |
  17. -------------------------------------------------------------------------------------------------
  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------
  22.    4 - access("T2"."OBJECT_ID"<10)
  23.    5 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")
  24.        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更大，因为前导列选择性足够好：

点击(此处)折叠或打开

  1. select/*+OPTIMIZER_FEATURES_ENABLE('11.2.0.4')*/ *
  2. from t1,t2
  3. where t1.object_name = t2.object_name
  4. and t1.owner in ('SYS','DINGJUN123')
  5. and t2.object_id <10;
  6. Execution Plan
  7. ----------------------------------------------------------
  8. Plan hash value: 3442654963
  10. -----------------------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. -----------------------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 8 | 2112 | 43 (0)| 00:00:01 |
  14. | 1 | NESTED LOOPS | | | | | |
  15. | 2 | NESTED LOOPS | | 8 | 2112 | 43 (0)| 00:00:01 |
  16. | 3 | TABLE ACCESS BY INDEX ROWID| T2 | 8 | 1056 | 3 (0)| 00:00:01 |
  17. |* 4 | INDEX RANGE SCAN | IDX1_T2 | 8 | | 2 (0)| 00:00:01 |
  18. | 5 | INLIST ITERATOR | | | | | |
  19. |* 6 | INDEX RANGE SCAN | IDX1_T1 | 2 | | 3 (0)| 00:00:01 |
  20. | 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 132 | 5 (0)| 00:00:01 |
  21. -----------------------------------------------------------------------------------------
  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------
  26.    4 - access("T2"."OBJECT_ID"<10)
  27.    6 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
  28.               ("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS'))
  31. Statistics
  32. ----------------------------------------------------------
  33.           2 recursive calls
  34.           0 db block gets
  35.          45 consistent gets
  36.           0 physical reads
  37.           0 redo size
  38.        5598 bytes sent via SQL*Net to client
  39.         519 bytes received via SQL*Net from client
  40.           2 SQL*Net roundtrips to/from client
  41.           0 sorts (memory)
  42.           0 sorts (disk)
  43.           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如下：

点击(此处)折叠或打开

  1. select *
  2. from t1,t2
  3. where t1.object_name = t2.object_name
  4. and t1.owner in ('SYS','DINGJUN123')
  5. and t1.object_type in('TABLE'，'VIEW')
  6. 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:

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 1219290223
  5. -------------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. -------------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 8 | 2112 | 59 (0)| 00:00:01 |
  9. | 1 | NESTED LOOPS | | 8 | 2112 | 59 (0)| 00:00:01 |
  10. | 2 | NESTED LOOPS | | 32 | 2112 | 59 (0)| 00:00:01 |
  11. | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 8 | 1056 | 3 (0)| 00:00:01 |
  12. |* 4 | INDEX RANGE SCAN | IDX1_T2 | 8 | | 2 (0)| 00:00:01 |
  13. | 5 | INLIST ITERATOR | | | | | |
  14. |* 6 | INDEX RANGE SCAN | IDX2_T1 | 4 | | 5 (0)| 00:00:01 |
  15. | 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 132 | 7 (0)| 00:00:01 |
  16. -------------------------------------------------------------------------------------------------
  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------
  21.    4 - access("T2"."OBJECT_ID"<10)
  22.    6 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
  23.               ("T1"."OBJECT_TYPE"='TABLE' OR "T1"."OBJECT_TYPE"='VIEW') AND
  24.               "T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

4)组合索引前导列是IN多个值能否消除排序，取决于排序列是否有前导列

点击(此处)折叠或打开

  1. drop index idx1_t1;
  2. drop index idx2_t1;
  3. create index idx2_t1 on t1(owner,object_name);
  4. select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123')
  5. order by owner desc,object_name desc;
因为排序列按照组合索引顺序，且access两个列都用到，排序方向一致，可以消除排序：
 

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 3833075694
  5. -----------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. -----------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 4 | 528 | 8 (0)| 00:00:01 |
  9. | 1 | INLIST ITERATOR | | | | | |
  10. | 2 | TABLE ACCESS BY INDEX ROWID | T1 | 4 | 528 | 8 (0)| 00:00:01 |
  11. |* 3 | INDEX RANGE SCAN DESCENDING| IDX2_T1 | 4 | | 6 (0)| 00:00:01 |
  12. -----------------------------------------------------------------------------------------
  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------
  17.    3 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
  18.               ("T1"."OBJECT_NAME"='DBA_OBJECTS' OR "T1"."OBJECT_NAME"='DBA_TABLES'))
排序列无前导列，只有第二个列，则必须前导列是等值条件才可以消除排序，这里前导列是IN有2个分支，可以看到没有消除排序，
执行计划有：SORT ORDER BY 
SQL和执行计划如下：

点击(此处)折叠或打开

  1. select * from t1 where t1.object_name in ('DBA_OBJECTS','DBA_TABLES') and t1.owner in ('SYS','DINGJUN123')
  2. order by object_name;
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 4272791971
  7. -------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. -------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 4 | 528 | 9 (12)| 00:00:01 |
  11. | 1 | SORT ORDER BY | | 4 | 528 | 9 (12)| 00:00:01 |
  12. | 2 | INLIST ITERATOR | | | | | |
  13. | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 4 | 528 | 8 (0)| 00:00:01 |
  14. |* 4 | INDEX RANGE SCAN | IDX2_T1 | 4 | | 6 (0)| 00:00:01 |
  15. -------------------------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  20.    4 - access(("T1"."OWNER"='DINGJUN123' OR "T1"."OWNER"='SYS') AND
  21.               ("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算子实现走各区间索引扫描提高效率。
posted @ 2024-07-19 21:32  农民小工程师  阅读(14)  评论(0)  编辑  收藏  举报
jiwenlaw.com