Изменения плана выполнения Oracle SQL из-за внутреннего преобразования SYS_OP_C2C

Ответ 1

SYS_OP_C2C - это internal function, который выполняет implicit conversion от varchar2 до national character set с помощью функции TO_NCHAR. Таким образом, фильтр полностью изменяется по сравнению с фильтром, используя нормальное сравнение.

Я не уверен, почему количество строк меньше, но я могу гарантировать, что это может быть больше. Оценка стоимости не будет затронута.

Попробуйте увидеть шаг за шагом в тестовом примере.

SQL> CREATE TABLE t AS SELECT 'a'||LEVEL col FROM dual CONNECT BY LEVEL < 1000;

Table created.

SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE col = 'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("COL"='a10')

13 rows selected.

SQL>

Пока все хорошо. Поскольку существует только одна строка со значением "a10", оптимизатор оценивает одну строку.

Посмотрите с преобразованием национальных символов.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE col = N'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    50 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    10 |    50 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter(SYS_OP_C2C("COL")=U'a10')

13 rows selected.

SQL>

Что здесь произошло? Мы можем видеть filter(SYS_OP_C2C("COL")=U'a10'), что означает, что применяется внутренняя функция, и она преобразует значение varchar2 в nvarchar2. Фильтр теперь нашел 10 строк.

Это также подавляет любое использование индекса, так как теперь функция применяется к столбцу. Мы можем настроить его, создав function-based index, чтобы избежать full table scan.

SQL> create index nchar_indx on t(to_nchar(col));

Index created.

SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE to_nchar(col) = N'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1400144832

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |    10 |    50 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |    10 |    50 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NCHAR_INDX |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access(SYS_OP_C2C("COL")=U'a10')

14 rows selected.

SQL>

Однако будет ли подобное выполнение планов выполнения? Нет. Я думаю, что с двумя разными символами фильтр не будет применяться одинаково. Таким образом, разница лежит.

В моих исследованиях говорится,

Обычно такие сценарии возникают, когда данные, поступающие через приложение Тип nvarchar2, но столбец таблицы varchar2. Таким образом, Oracle применяет внутреннюю функцию в работе фильтра. Мое предложение чтобы хорошо знать ваши данные, чтобы вы использовали похожие типы данных во время этап проектирования.

Ответ 2

Когда вы беспокоитесь о планах объяснения, важно, есть ли текущая статистика по таблицам. Если статистика не отражает фактические данные достаточно хорошо, то оптимизатор ошибочно ошибочно и оценивает мощность.

Вы можете проверить, как давно статистика была собрана, запросив словарь данных:

select table_name, last_analyzed
  from user_tables
 where table_name in ('ADDRESS','NAME');

Вы можете собрать статистику для оптимизатора для использования, вызывая DBMS_STATS:

begin
   dbms_stats.gather_table_stats(user, 'ADDRESS');
   dbms_stats.gather_table_stats(user, 'NAME');
end;

Итак, возможно, после сбора статистики вы получите разные планы объяснений. Возможно, нет.

Разница в ваших планах объясняется прежде всего тем, что оптимизатор оценивает, сколько строк он найдет в адресной таблице по-разному в двух случаях.

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

Во втором случае к столбцу применяется функция - это часто бывает плохо (если у вас нет индексов, основанных на функциях), и это заставит оптимизатор задуматься. Эта дикая просьба будет отличаться в разных версиях Oracle, поскольку разработчики оптимизатора пытаются ее улучшить. В некоторых версиях дикая догадка просто будет выглядеть примерно так: "Я думаю, 5% от количества строк в таблице".

При сравнении разных типов данных лучше избегать неявных преобразований, особенно когда подобный случай неявное преобразование делает функцию в столбце, а не в буквальном. Если у вас есть случаи, когда вы получаете значение типа данных NVARCHAR2 и должны использовать его в предикате, как указано выше, может быть хорошей идеей явно преобразовать значение в тип данных столбца.

select * from address a
left join name n on n.adress_id=a.id
where a.street = CAST( N'01' AS VARCHAR2(255));

В этом случае с литералом это, конечно, не имеет смысла. Здесь вы просто используете свой первый запрос. Но если это параметр переменной или функции, возможно, у вас могут быть варианты использования для этого.

Ответ 3

Как я вижу, первый запрос возвращает 3591 строк, второй возвращает 347 строк. Поэтому Oracle требует меньше операций ввода-вывода, поэтому стоимость меньше.

Не путайте с

N'01 'также нуждается в преобразовании varchar в nvarchar

Oracle делает один синтаксический анализ, а затем использует мягкий синтаксический анализ для тех же запросов. Таким образом, чем дольше ваш оракул работает, тем быстрее он становится.