为什么在这个执行计划的同一个索引上同时有过滤器和访问谓词?

Question

考虑这个查询的执行计划:

SQL_ID  1m5r644say02b, child number 0
-------------------------------------
select * from hr.employees where department_id = 80 intersect select * 
from hr.employees where first_name like 'A%'

Plan hash value: 1738366820

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |      4 |00:00:00.01 |       8 |       |       |          |
|   1 |  INTERSECTION                         |             |      1 |        |      4 |00:00:00.01 |       8 |       |       |          |
|   2 |   SORT UNIQUE                         |             |      1 |     34 |     34 |00:00:00.01 |       6 |  6144 |  6144 | 6144  (0)|
|*  3 |    TABLE ACCESS FULL                  | EMPLOYEES   |      1 |     34 |     34 |00:00:00.01 |       6 |       |       |          |
|   4 |   SORT UNIQUE                         |             |      1 |     11 |     10 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |      1 |     11 |     10 |00:00:00.01 |       2 |       |       |          |
|*  6 |     INDEX SKIP SCAN                   | EMP_NAME_IX |      1 |     11 |     10 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

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

3 - filter("DEPARTMENT_ID"=80)
6 - access("FIRST_NAME" LIKE 'A%')
filter("FIRST_NAME" LIKE 'A%')

执行计划在EMP_NAME_IX索引上具有具有相同A%谓词的访问谓词和筛选谓词。但是访问谓词在这里还不够吗，因为它们都将过滤相同的行?为什么要执行附加的过滤器谓词?

当access和filter相同时，是否有一个通用规则?基于GV$SQL_PLAN，当一个操作有访问谓词或过滤谓词时，它们的相等概率仅为1%左右。这种情况只会发生在像INDEX (FULL/RANGE/SKIP/UNIQUE)和SORT (JOIN/UNIQUE)这样的操作和选项上。

select *
from gv$sql_plan
where access_predicates = filter_predicates;

Answer 1

假设您在hr.employees上有一个索引，其中包括first_name列。但是您从hr.employees中选择*，这样从索引中获得的行就必须追溯到(即连接)表。

从概念上理解，它有助于将索引视为普通表，其外键指向原始表的主键。如果使用索引有帮助，这两个表将被连接起来。当索引包含所有需要的列时，该索引单独使用。

在本例中，我们假设需要一个连接，因为您选择了*。当access为intersect的第二次查询查询hr.employee表时，由于where子句filter位于索引列上，因此在过滤之前执行对索引的连接。

第一次出现"FIRST_NAME" LIKE 'A%'是决定索引使用的原因。第二次出现就是实际的过滤。过滤只发生一次，而不是两次。

这些被列为不同的操作，因为决定使用索引(并因此执行连接)有自己的成本。