выберите производительность запроса по сравнению с первичным, внешним ключом

У меня была аналогичная проблема с производительностью при импорте объектов из одной среды в другую, а затем я добавил недостающие ограничения, и тогда я увидел хорошее улучшение производительности. Поэтому я хотел бы попросить вас добавить ограничения, а затем запустить запрос на соединение.

Во-первых, первичные и уникальные ключи также являются индексами (то есть ограничение поддерживается индексом), поэтому отсутствие первичных или уникальных ключей может означать отсутствие индексов.

Кроме того, оптимизатор может применять некоторые преобразования, если существуют ограничения. Например, рассмотрим этот запрос (с использованием демонстрационной схемы Oracle HR):

select count(*)
from   employees e
       join departments d on d.department_id = e.department_id;

План выполнения:

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |     3 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                   |     1 |     3 |            |          |
|*  2 |   INDEX FULL SCAN| EMP_DEPARTMENT_IX |   106 |   318 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

Обратите внимание, что нет ссылки на departments. Оптимизатор знает, что department_id в employees должен существовать в departments, потому что это обеспечивается внешним ключом, а также он может встречаться только один раз в departments, потому что это первичный ключ. Следовательно, нет необходимости фактически оценивать объединение, поскольку все, что нужно сделать, это проверить, что e.department_id не равно нулю. Поскольку в этом столбце есть индекс, он может просто рассматривать индекс как тонкую таблицу и даже не касаться employees. (Если бы e.department_id было определено как not null, ему даже не понадобился бы этот фильтр.)

Теперь посмотрим, что произойдет, если мы отключим ограничение:

SQL> alter table employees disable constraint EMP_DEPT_FK;

Table altered.

SQL> select count(*)
  2  from   employees e
  3         join departments d on d.department_id = e.department_id;

  COUNT(*)
----------
       106

SQL> @xplan
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Compatibility is set to 12.2.0.0.0

Plan hash value: 1475840611

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |     1 |     7 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                   |     1 |     7 |            |          |
|   2 |   NESTED LOOPS      |                   |   106 |   742 |     0   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | EMP_DEPARTMENT_IX |   107 |   321 |     0   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| DEPT_ID_PK        |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")