复合索引的filter和access(二）

上一篇blog http://www.dbaxiaoyu.com/archives/2354 简单的讲述了index range scan的filter和access的区别，这里再进行一点补充，索引对于sql优化的意义确实是非常大的。 [oracle@redhat_ora11g ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Prod

上一篇blog http://www.dbaxiaoyu.com/archives/2354 简单的讲述了index range scan的filter和access的区别，这里再进行一点补充，索引对于sql优化的意义确实是非常大的。

[oracle@redhat_ora11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 3 06:38:37 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> create table tbak03 as select * from dba_objects;

Table created.

SQL> create index ind_tbak_owner_objid_type on tbak03(owner,object_id,object_type);

Index created.

SQL> select * from tbak03 where owner='SYS' and object_type='INDEX';

1212 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3404448970

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1061 | 214K| 163 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1061 | 214K| 163 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_OBJID_TYPE | 148 | | 159 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='INDEX')
filter("OBJECT_TYPE"='INDEX')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
84 recursive calls
0 db block gets
536 consistent gets
1 physical reads
0 redo size
130958 bytes sent via SQL*Net to client
1403 bytes received via SQL*Net from client
82 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1212 rows processed

这个sql走的是index range scan，access过滤的条件是(&＃8220;OWNER&＃8221;=&＃8217;SYS&＃8217; AND &＃8220;OBJECT_TYPE&＃8221;=&＃8217;INDEX’)，但是filter中又出现了(&＃8220;OBJECT_TYPE&＃8221;=&＃8217;INDEX’)，这个是表示优化器在index range scan扫描时不能绝对保证扫描leaf block满足(&＃8220;OBJECT_TYPE&＃8221;=&＃8217;INDEX’)，所以需要在index range scan完成后再次对object_type进行过滤。

我们再看下面的sql语句的执行计划：

SQL> select * from tbak03 where owner='SYS' and object_type='INDEX' and object_id>10000;

161 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3404448970

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 120 | 24840 | 139 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 120 | 24840 | 139 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_OBJID_TYPE | 125 | | 135 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS' AND "OBJECT_ID">10000 AND "OBJECT_TYPE"='INDEX' AND "OBJECT_ID" IS
NOT NULL)
filter("OBJECT_TYPE"='INDEX')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
248 consistent gets
0 physical reads
0 redo size
18929 bytes sent via SQL*Net to client
633 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
161 rows processed

虽然同样是index range scan，access部分出现了三个条件值时，但是filter中还是出现了(&＃8220;OBJECT_TYPE&＃8221;=&＃8217;INDEX’)这个条件，同样这里表示优化器在index range scan扫描时候不能保证扫描的leaf block绝对满足(&＃8220;OBJECT_TYPE&＃8221;=&＃8217;INDEX’)。

上面两个sql都可以总结为一类sql，就是在index range scan时候，在谓词条件中如果复合索引的部分列没有包含或者没有等值谓词过滤条件，而且出现了此列在复合索引相关位置的后面的列的谓词条件，那么后面相关的列肯定需要走filter，index range scan扫描leaf block是没有办法确定扫描的列都是满足access的条件，当然leaf block扫描时就可能会扫描一些不满足access部分涉及的谓词条件的leaf block

而如果索引的所有列都出现在查询中，而且都是等值的谓词条件，那么索引的index range scan不会出现filter

SQL> select * from tbak03 where owner='SYS' and object_type='INDEX' and object_id=10000;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3404448970

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_OBJID_TYPE | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS' AND "OBJECT_ID"=10000 AND "OBJECT_TYPE"='INDEX')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

如果存在不等值的查询，只要保证这个不等值的谓词是复合索引的最后列，index range scan扫描时不会出现filter部分，索引范围扫描时候也能够精确的定位到需要扫描的leaf block

SQL> create index ind_tbak_owner_type_objid on tbak03(owner,object_type,object_id);

Index created.

SQL> select * from tbak03 where owner='SYS' and object_type='INDEX' and object_id>10000;

3606 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1247367584

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 579 | 57321 | 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 579 | 57321 | 29 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TBAK_OWNER_TYPE_OBJID | 579 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='INDEX' AND "OBJECT_ID">10000 AND "OBJECT_ID" IS
NOT NULL)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
667 consistent gets
15 physical reads
0 redo size
406174 bytes sent via SQL*Net to client
3163 bytes received via SQL*Net from client
242 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3606 rows processed

下面我们来看index skip scan的access和filter部分有何区别：

SQL> select * from tbak03 where object_id=10000;


Execution Plan
----------------------------------------------------------
Plan hash value: 846494542

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 99 | 6 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_TBAK_OWNER_OBJID_TYPE | 1 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=10000)
filter("OBJECT_ID"=10000)


Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
1615 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

index skip scan时候好像谓词部分access和filter也同时出现了，而且还是针对同一个值

再来看看下面的sql语句又出现index skip scan：

SQL> select * from tbak03 where object_id=10000 and owner>'SYS';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 846494542

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 99 | 6 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_TBAK_OWNER_OBJID_TYPE | 1 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER">'SYS' AND "OBJECT_ID"=10000 AND "OWNER" IS NOT NULL)
filter("OBJECT_ID"=10000)

Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed

索引IND_TBAK_OWNER_OBJID_TYPE是(owner,object_id,object_type）的复合索引，但是上面这个查询没有出现我们熟知的index range scan，然后在filter部分再次出现object_id的过滤，而是直接以index skip scan的方式来扫描，然后再filter部分再次出现object_id的等值过滤，这跟我们熟知的index range scan时候当索引的前导列不存在时才出现是对立的。

优化器在index skip scan时是这么完成的，首先通过object_id=10000 and owner>&＃8217;SYS’条件从root节点找到branch block再到leaf block然后索引范围扫描，当发现object_id不再满足条件时，则重新回到了root节点再到下一个定位的branch block和leaf block做范围扫描，跟之前index range scan所不同的就是，index skip scan会重新回到branch block再到leaf block，类似于一个索引的迭代，而index range scan则大多表现为一旦定位到branch block和leaf block后，就只会在leaf block通过双向指针滑动来扫描满足条件的leaf block，对于一些复合索引如果前导列不同值较少，然后后导列不同值较多，优化器评估object_id=10000 and owner>&＃8217;SYS’这类谓词条件时会走index skip scan来完成查询。

接下来如果是下列谓词条件的话，优化器多半不会选择index range scan来完成查询。

SQL> create index ind_test02 on xiaoyu03(owner,object_id,data_object_id);

Index created.

SQL> select * from xiaoyu03 where object_id
177 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4138876341

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2296 | 197K| 43 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XIAOYU03 | 2296 | 197K| 43 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<"DATA_OBJECT_ID")


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
151 consistent gets
0 physical reads
0 redo size
12358 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
177 rows processed

SQL> select /*+index_ffs(xiaoyu03 IND_OBJID_DATAID)*/* from xiaoyu03 where object_id
177 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4138876341

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2296 | 197K| 43 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XIAOYU03 | 2296 | 197K| 43 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<"DATA_OBJECT_ID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
151 consistent gets
0 physical reads
0 redo size
12358 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
177 rows processed

这里的hint为什么无法生效了，索引快速扫描时优化器是没有办法回表的，这里由于需要回表，hint将会被忽略掉。

SQL> select /*+index(xiaoyu03)*/* from xiaoyu03 where object_id

177 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3964829153

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2296 | 197K| 78 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| XIAOYU03 | 2296 | 197K| 78 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | IND_TEST02 | 2296 | | 31 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"<"DATA_OBJECT_ID")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68 consistent gets
11 physical reads
0 redo size
23874 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
177 rows processed

强制走index，需要注意的index full scan其实也是index range scan，只是index full scan时oracle需要扫描所有的leaf block，但是其实扫描的机制还是一样的。

再来看另外比较常见的or和and来使用索引的两类查询，我们先来看下包含多个谓词条件的and查询：

SQL> create index ind_objid_dataobjid on tbak03(object_id,data_object_id);

Index created.

SQL> select * from tbak03 where object_id between 10000 and 10019 and data_object_id between 9980 and 10092;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3552957621

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 99 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_DATAOBJID | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=10000 AND "DATA_OBJECT_ID">=9980 AND "OBJECT_ID"<=10019 AND
"DATA_OBJECT_ID"<=10092)
filter("DATA_OBJECT_ID"<=10092 AND "DATA_OBJECT_ID">=9980)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

对于上述这类包含两个不同谓词过滤条件的and条件，需要明确的and情况下oracle很多情况都会只用一个索引来完成查询，然后回表进行第二次过滤，但是如果是需要两个索引回表才能完成查询，优化器就只能选择全表扫描或者转换bitmap后做bitmap and回表。

SQL> drop index ind_objid_dataobjid;

Index dropped.
SQL> create index ind_objid on tbak03(object_id);

Index created.

SQL> create index ind_dataobjid on tbak03(data_object_id);

Index created.

SQL> select * from tbak03 where object_id between 8809 and 10003 and data_object_id between 19980 and 30002;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2924755239

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1386 | 13 (16)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TBAK03 | 14 | 1386 | 13 (16)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | IND_DATAOBJID | 1182 | | 4 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 8 | SORT ORDER BY | | | | | |
|* 9 | INDEX RANGE SCAN | IND_OBJID | 1182 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("DATA_OBJECT_ID">=19980 AND "DATA_OBJECT_ID"<=30002)
9 - access("OBJECT_ID">=8809 AND "OBJECT_ID"<=10003)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

这里oracle将其中的索引b tree的rowid先转化为bitmap，然后将两个bitmap进行bitmap and最后再转化为rowed回表，一般而言出现这类执行计划都是建议创建更优秀的复合索引来较少sql语句消耗的资源。

再来看下包含两个谓词条件的or查询：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5svu4zamsud9q, child number 0
-------------------------------------
select /*+gather_plan_statistics*/object_name from tbak03 where
object_id between 10000 and 10019 or data_object_id between 8080 and
10092

Plan hash value: 2095522732

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 217 |00:00:00.01 | 76 | 1 |
| 1 | CONCATENATION | | 1 | | 217 |00:00:00.01 | 76 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 21 | 20 |00:00:00.01 | 10 | 0 |
|* 3 | INDEX RANGE SCAN | IND_OBJID | 1 | 21 | 20 |00:00:00.01 | 4 | 0 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 1 | 181 | 197 |00:00:00.01 | 66 | 1 |
|* 5 | INDEX RANGE SCAN | IND_DATAOBJID | 1 | 201 | 197 |00:00:00.01 | 16 | 1 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=10019)
4 - filter((LNNVL("OBJECT_ID"<=10019) OR LNNVL("OBJECT_ID">=10000)))
5 - access("DATA_OBJECT_ID">=8080 AND "DATA_OBJECT_ID"<=10092)

26 rows selected.

这个concatenation是类似oracle的union all，就是分别通过rowid回表，但是需要注意的是其中 谓词信息4 &＃8211; filter(LNNVL(&＃8220;DATA_OBJECT_ID&＃8221;<=10092) OR LNNVL("DATA_OBJECT_ID">=9980))去掉了重复满足多个条件的数据，这样可以保证通过这种方式取回的数据是准确的。

可能有朋友会问到了，如果对于or创建一个复合索引是否可以避免上述这类concatenation方式了，其实or的情况下是不能单单利用一次index range scan来完成查询的，即使这个索引是个包含两个过滤条件的复合索引，优化器如果走index range scan，也需要走两次index range scan回表然后concatenation的方式，或者直接走一次index full scan然后回表。

SQL> create index ind_objid_dataobjid on tbak03(object_id,data_object_id);

Index created.
SQL> select /*+index(tbak03 ind_objid_dataobjid)*/object_name from tbak03 where object_id between 10000 and 10019 or data_object_id between 8080 and 10092;

217 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 392751159

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 222 | 6660 | 214 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 222 | 6660 | 214 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | IND_OBJID_DATAOBJID | 222 | | 210 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("DATA_OBJECT_ID"<=10092 AND "DATA_OBJECT_ID">=8080 OR "OBJECT_ID"<=10019 AND
"OBJECT_ID">=10000)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
254 consistent gets
208 physical reads
0 redo size
7594 bytes sent via SQL*Net to client
677 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
217 rows processed

再来另外一种更简单的or查询，需要注意的in (‘A’,’B’…)这种查询，oracle的查询转换也会改写为or的形式，由于都是同一个列的谓词条件，优化器大多数会走比concatenation更加高效的inlist iterator，inlist iterator的方式类似于在反复的从root节点到branch再到leaf block来定位满足条件的键值，然后回表，而concatenation的方式则是通过多次index range scan回表的方式合并结果集。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1guyuwmd6wsj6, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from tbak03 where object_id=90 or
object_id=29292


Plan hash value: 3397823708


-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 7 | 2 |
| 1 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 7 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| TBAK03 | 2 | 2 | 2 |00:00:00.01 | 7 | 2 |
|* 3 | INDEX RANGE SCAN | IND_OBJID | 2 | 2 | 2 |00:00:00.01 | 5 | 2 |
-------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


3 - access(("OBJECT_ID"=90 OR "OBJECT_ID"=29292))




21 rows selected.

关于索引的filter和access部分这里xiaoyu再举出上述的例子以供大家参考，下篇将对bitmap索引进行探讨。

原文地址：复合索引的filter和access(二）, 感谢原作者分享。