首页 > 数据库 >ORACLE vs MySQL 对组合索引包含IN LIST执行计划研究(ORACLE部分)_PART1

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

时间：2024-07-19 21:29:00浏览次数：6  
标签：00 01 name object LIST t1 PART1 ORACLE T1

本文主要研究下组合索引包含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算子实现走各区间索引扫描提高效率。

标签：00,01,name,object,LIST,t1,PART1,ORACLE,T1
From： https://www.cnblogs.com/77cxw/p/18312418

相关文章

  • Oracle数据库 oracle八大性能视图
    V$SYSSTATVSYSSTAT......
  • 链表（Linked List）-Python实现-使用类和使用函数
    链表链表（LinkedList）单链表（SinglyLinkedList）节点类（NodeClass）链表类（LinkedListClass）使用链表类不用类的方法实现链表实现单链表使用函数实现链表具体讲解类的方法实现链表Node类LinkedList类不用类的方法实现链表创建节点添加节点删除节点搜索节点显示链表总......
  • Oracle RAC的坑应用分离
    RAC最大的坑就是内存融合，也不能说它是坑，只是在使用过程中，或者传统数据库升级到RAC上会带来巨大的性能下降。RAC从8时代的并行技术发展过来的，到了11G正式成熟稳定，一路走来都是解决高可用的问题，达到双机热备，不仅仅是热备而是双活，同时工作，同时对外服务，一个挂了，另外一个接过活来干。......
  • oracle数据库状态查询
    oracle数据库状态查询（表空间，表空间利用率，是否自动扩展，IOPS，MBPS，数据库总大小）排除了('SYSAUX','USERS','SYSTEM','UNDOTBS1')结果示例--查询用户自定义表空间的使用信息WITHuser_tablespacesAS(SELECTDISTINCTtablespace_nameFROMdba_segmentsWHERE......
  • Seata 1.3.0 Oracle 回滚测试验证 报错 ORA-02289: 序列不存在
    使用Seata1.3.0版本，测试A服务调用B服务，且A方法中，手动写了一个异常，测试是否正常回滚（Mysql已经测试过）发现报错：ORA-02289:序列不存在 一看就是undo_log这张表的sequence没有建立，导致数据保存不了，太久没有用oracle了，之前搜索undo_log的建表SQL是直接在网上搜索的，所以缺少了建seq......
  • oracle 存储过程造数
    BEGINFORiIN1..{count_insert}LOOPINSERTINTOIKASTRIGGER.IKAS10B(COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6,COLUMN7,COLUMN8,COLUMN9,COLUMN10)......
  • 易优CMS模板标签modelsartlist频道循环输出顶级栏目列表
    [基础用法]标签：modelsartlist（channelartlist）备注：使用channelartlist也可以正常输出描述：获取当前栏目分类的下级栏目的文档列表用法：{eyou:modelsartlisttypeid='栏目ID'type='son'loop='20'}<ahref='{eyou:fieldname='typeurl'/}'>{eyou:fi......
  • WPF ListBox's ItemsSource depend on another's ListBoxItem and fully implemented
    //xaml<Grid><Grid.ColumnDefinitions><ColumnDefinition/><ColumnDefinition/><ColumnDefinition/></Grid.ColumnDefinitions><ListBoxGrid.Column="0"ItemsSource=&......
  • 揭秘Java世界：轻松检测两个List是否有交集
    哈喽，大家好，我是木头左！快速入门：什么是List的交集？在Java中，当提到两个List的交集，指的是这两个列表共有的元素集合。例如，如果有两个List，其中一个包含元素A,B,C，另一个包含B,C,D，那么它们的交集就是B,C。理解了这一点，就可以开始探讨如何检测这个交集，以及它对编程实践的意义。Ja......
  • mormot2 IDocList&IDocDict
    mormot2IDocList&IDocDictusesmormot.core.variantsmormot2模仿PythonListsandDictionariesJSON操作，封装了IDocList&IDocDict俩个新的接口。IDocList(别名IDocArray)存储alistofelements;IDocDict(别名IDocObject)存储adictionaryofkey:valuepairs.例程一......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99