Oracle 12c Факторинг подзапросов Inline View теперь имеет плохой план?

Question

Обновление 11/2

После некоторого дополнительного устранения неполадок моя команда смогла связать эту ошибку Oracle непосредственно с изменением параметра, которое было сделано в 12c базы данных за ночь до того, как запрос перестал работать. После некоторых проблем с производительностью приложения, связанного с этой базой данных, моя команда попросила нашего администратора базы данных изменить OPTIMIZER_FEATURES_ENABLE параметр из 12.1.02 в 11.2.0.4, Это устранило проблему производительности для проблемного приложения, но вызвало ошибку, которую я описал выше. Чтобы проверить, я смог повторить эту же проблему в отдельной среде, изменив этот параметр. Мой администратор базы данных подал заявку в Oracle, чтобы посмотреть на это.

В качестве обходного пути я смог немного изменить свой запрос, чтобы получить ожидаемые результаты. Конкретно я объединил Subquery1 с Subquery2 и я переместил несколько предикатов в Subquery1 от WHERE пункт к JOIN (где они более правильно принадлежат). Это изменение отредактировало мой план выполнения (оно несколько менее эффективно, чем то, что было перечислено ранее), но оказалось достаточным для устранения исходной проблемы.

---

Оригинальный пост

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

Фон

у меня есть Oracle запрос, который я ввел в производство давным-давно, который недавно прекратил давать ожидаемые результаты по совпадению после обновления с 11g в 12c, Насколько мне известно (и моей команде поддержки производства), этот запрос работал хорошо более года назад.

подробности

Запрос является слишком сложным и не очень эффективным, но это в значительной степени связано с тем, что я имею дело с ненормализованными таблицами (исторически смоделированными после мэйнфрейма) и плохим вводом данных из вышестоящих систем. Чтобы справиться со сложной бизнес-ситуацией, я использовал несколько уровней факторинга подзапросов (WITH заявление), а затем мое последнее утверждение объединяет два встроенных представления. Основная структура запроса без всех сложных предикатов выглядит следующим образом:

У меня 3 стола Table1, Table2, Table3, Table1 таблица обработки, составленная из записей Table2,

--This grabs a subset from Table1
WITH Subquery1 as (
   SELECT FROM Table1),

--This eliminates certain records from the first subset based on sister records 
--from the original source table 
Subquery2 as (
   SELECT FROM Subquery1
   WHERE NOT EXISTS FROM (SELECT from Table2)),

--This ties the records from Subquery2 to Table3
Subquery3 as (
   SELECT FROM Table3
   JOIN (SELECT Max(Date) FROM Table3)
   JOIN Subquery2)

--This final query evaluates subquery3 in two different ways and 
--only takes those records which fit the criteria items from both sets
SELECT FROM 
(SELECT FROM Subquery3)             -- Call this Inline View A
JOIN (SELECT FROM Subquery3)        -- Call this Inline View B

Последний запрос довольно прост:

   SELECT A.Group_No, B.Sub_Group, B.Key, B.Lob               
   FROM   (SELECT Group_No, Lob, COUNT(Sub_Group) 
           FROM   Subquery3 
           GROUP BY Group_No, Lob
           HAVING COUNT(Sub_Group) = 1) A 
   JOIN (SELECT Group_No, Sub_Group, Key, Lob
         FROM   Subquery3 
         WHERE  Sub_Group LIKE '0000%') B 
   ON A.Group_No = B.Group_No
   AND A.Lob = B.Lob

проблема

Если я отредактирую последний запрос, чтобы удалить второе встроенное представление и оценим выходные данные A встроенный просмотр, я ухожу с 0 возвращенными строками. Я вручную оценил записи для каждого отдельного подзапроса и могу подтвердить, что это ожидаемый результат.

Аналогичным образом, если я отредактирую окончательный запрос, чтобы получить вывод только встроенного представления "B", я получу 6 возвращенных строк. Опять же, я вручную оценил данные, и это точно так, как и ожидалось.

Теперь при объединении этих двух подмножеств (Inline View A и встроенный вид B) вместе, я ожидаю, что конечный результат запроса будет 0 строк (так как внутреннее соединение между полным набором и пустым набором не дает совпадений). Однако, когда я выполняю весь запрос с внутренним объединением, как описано выше, я получаю 1158 строк!

Я пересмотрел план выполнения, но у меня ничего не выскакивает:

Вопросы

Понятно, что я сделал что-то, чтобы сбить с толку Oracle Optimizer, и обновленный план запросов откатывает совершенно другой запрос, чем тот, который я отправил. Мое лучшее предположение состоит в том, что из-за того, что все эти временные представления перемещаются внутри одного и того же запроса, я запутал Oracle в оценке некоторого набора перед тем, от которого он зависит.

На сегодняшний день мне не удалось найти официальную документацию Oracle по WITH заявление, поэтому я никогда не был полностью уверен в порядке, что подзапросы оцениваются. Я заметил, что при поиске SO (не могу найти сейчас) кто-то упомянул, что факторизованный подзапрос не может ссылаться на другой факторизованный запрос. Я никогда не знал, что это правда, но причудливый вывод, приведенный выше, заставляет меня задуматься, повезло ли мне раньше только с этим запросом?

Кто-нибудь может объяснить поведение, которое я вижу? Я пытаюсь сделать что-то явно неправильное с этим планом запроса? Или альтернативно, есть ли вероятность, что что-то изменилось между 11g и 12c, что могло бы объяснить, почему поведение этого запроса могло измениться?

Answer 1

Это звучит как ошибка "неправильных результатов" в Oracle. Эти ошибки обычно очень специфичны для версии и функций, которые вы используете. Нет ничего явно плохого в размещенных вами запросах или плане выполнения.

У вас есть два способа справиться с этим:

1. Попробуйте найти точную ошибку. То, что вы делаете с обычными табличными выражениями, выглядит хорошо. Есть несколько редких случаев, когда ваш запрос технически недействителен, вам повезло в одной версии, и он работает, и когда вы обновляете его, это не удается. Но когда это происходит, новая версия обычно выдает ошибку, а не возвращает неправильные результаты. Вероятно, есть какая-то очень странная, специфическая комбинация функций, которые вы используете, которые вызывают проблему. Чтобы найти реальную проблему, вам нужно значительно упростить запрос, пока вы не сможете внести наименьшее возможное изменение и увидеть, как проблема появляется и исчезает. Вы также хотите удалить все объекты и использовать только DUAL, Этот процесс может занять несколько часов. В конце, когда у вас осталось всего несколько строк кода, либо разместите их здесь, посмотрите в службу поддержки Oracle или создайте запрос на обслуживание.
2. Избегайте ошибок. Даже если вы пройдете через описанные выше шаги, в любом случае исправления не будет. Иногда лучший обходной путь - сделать что-то по-другому. Приятно разобраться во всех проблемах, но у вас не всегда есть время. Вместо этого попробуйте переписать запрос синтаксически различными, но логически эквивалентными способами. Удалите некоторые или все общие табличные выражения, возможно, даже повторите некоторые SQL. Но обязательно оставьте комментарий, предупреждающий будущих программистов о том, почему вы делаете что-то странным образом.