Посторонний предикат фильтра Oracle Optimizer?

avatar
Alex Bartsmon
1 июля 2021 в 20:40
95
1
1

Почему Oracle по-прежнему применяет предикат фильтра к индексу даже после того, как предикат доступа к тому же индексу гарантирует, что предикат фильтра всегда верен?

drop table index_filter_child
;

drop table index_filter_parent
;

create table index_filter_parent 
  as
  select level id, chr(mod(level - 1, 26) + ascii('A')) code from dual connect by level <= 26
;

create table index_filter_child 
  as
  with
    "C" as (select chr(mod(level - 1, 26) + ascii('A')) code from dual connect by level <= 26)
    select rownum id, C1.code from C C1, C C2
;

exec dbms_stats.gather_table_stats('USER','INDEX_FILTER_PARENT')
;

exec dbms_stats.gather_table_stats('USER','INDEX_FILTER_CHILD')
;

create index ix_index_filter_parent on index_filter_parent(code)
;

create index ix_index_filter_child on index_filter_child(code)
;

select P.* 
  from index_filter_parent "P" 
       join index_filter_child "C" 
         on C.code = P.code
where P.code in('A','Z') --same result if we predicate instead on C.code in('A','Z')
;


--------------------------------------------------------------------------------------------------------------
| id  | Operation                     | name                         | rows  | Bytes | cost (%CPU)| time     |
--------------------------------------------------------------------------------------------------------------
|   0 | select statement              |                              |     5 |    35 |     4   (0)| 00:00:01 |
|   1 |  nested LOOPS                 |                              |     5 |    35 |     4   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |                              |       |       |            |          |
|   3 |    table access by index ROWID| INDEX_FILTER_PARENT          |     2 |    10 |     2   (0)| 00:00:01 |
|*  4 |     index range scan          | IX_INDEX_FILTER_PARENT       |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   index range scan            | IX_INDEX_FILTER_CHILD        |     2 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   4 - access("P"."CODE"='A' or "P"."CODE"='Z')
   5 - access("C"."CODE"="P"."CODE")
       filter("C"."CODE"='A' or "C"."CODE"='Z')   <========== why is this needed?

Почему предикат фильтра в 5 необходим в свете access("C"."CODE"="P"."CODE"), гарантирующего C.code 'A' или 'Z'?

Заранее спасибо.

Oracle 12.1 Enterprise Edition.

Источник

Ответы (1)

avatar
Sayan Malakshinov
2 июля 2021 в 00:39
1

Это результат трансформации "транзитивного замыкания": подробнее об этом можно прочитать здесь:

  1. Транзитивность и транзитивное замыкание (идентификатор документа 68979.1) Идентификатор документа 68979.1
  2. Джонатан Льюис - Декартово слияние
  3. Jonathan Lewis - Transitive Closure (или, что еще лучше, в его книге "Cost Based Oracle Fundamentals")

Если вы получите трассировку CBO (alter session set events '10053 trace name context forever, level 1' или alter session set events 'trace[SQL_Optimizer.*]), вы увидите, что преобразование происходит до выбора метода соединения и путей доступа. Это позволяет CBO анализировать больше различных путей доступа и выбирать наилучший доступный план. Более того, в случае адаптивных планов это позволяет оракулу менять метод соединения на лету. Например, вы можете получить такой план:

. 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |    52 |   364 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN                            |                        |    52 |   364 |     4   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR                     |                        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| INDEX_FILTER_PARENT    |     2 |    10 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IX_INDEX_FILTER_PARENT |     2 |       |     1   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR                     |                        |       |       |            |          |
|*  6 |    INDEX RANGE SCAN                   | IX_INDEX_FILTER_CHILD  |    52 |   104 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("C"."CODE"="P"."CODE")
   4 - access("P"."CODE"='A' OR "P"."CODE"='Z')
   6 - access("C"."CODE"='A' OR "C"."CODE"='Z')

Фактически его можно отключить с помощью события 10155: CBO disable generation of transitive OR-chains.

Ваш пример:

alter session set events '10155';
explain plan for
select P.* 
  from index_filter_parent "P" 
       join index_filter_child "C" 
         on C.code = P.code
where P.code in('A','Z');

Результаты:

SQL> alter session set events '10155';

Session altered.

SQL> explain plan for
  2  select P.*
  3    from index_filter_parent "P"
  4         join index_filter_child "C"
  5           on C.code = P.code
  6  where P.code in('A','Z') ;

Explained.

SQL> @xplan typical


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 2543178509

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |    52 |   364 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                        |    52 |   364 |     4   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR                     |                        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| INDEX_FILTER_PARENT    |     2 |    10 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IX_INDEX_FILTER_PARENT |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN                    | IX_INDEX_FILTER_CHILD  |    26 |    52 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   4 - access("P"."CODE"='A' OR "P"."CODE"='Z')
   5 - access("C"."CODE"="P"."CODE")

Note
-----
   - this is an adaptive plan

22 rows selected.

Как видите, этот предикат исчез.

ПС. Другие события для транзитивных предикатов:

  • ORA-10155: CBO отключает генерацию транзитивных цепочек ИЛИ
  • ORA-10171: CBO отключает предикаты транзитивного соединения
  • ORA-10179: CBO отключает замену транзитивного предиката
  • ORA-10195: CBO не использует проверочные ограничения для транзитивных предикатов