Оптимизатор Oracle намекает на функцию xmlagg

Question

У меня есть функция, которая вызывает несколько таблиц / представлений и т. Д. С несколькими xmlaggs данных.

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

Я запустил tkprof для быстрых и медленных, и я вижу несколько проблем - во-первых, медленный запрос пропускает при разборе и выполнении, тогда как быстрый не делает.

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

Я попытался вставить подсказку:

SELECT /*+ index(view_alias,table1_index, table2_index, table3_index) */     
XMLCONCAT (...

Однако он все еще выполняет полное сканирование таблицы. Я помещаю подсказку оптимизатора в неправильное место или использую неправильный синтаксис для этого?

Редактировать - я проводил более тщательное расследование, и кажется, что это может быть попытка Oracle выполнить хеш-соединение, а не вложенный цикл, однако мой выбор сделан из нескольких таблиц - могу ли я принудительно использовать USE_NL для всех 3? Как я узнаю, какая область pl/sql вызывает это, так как она вызывается несколько раз.

Обновление 28/08 - Добавлена ​​награда. Дайте мне знать, если что-нибудь потребуется.

Обновление 01/09 -

> SELECT XMLCONCAT (  XMLELEMENT (  "1",  (SELECT XMLCONCAT(  XMLELEMENT
> (  "2",  XMLELEMENT (  "3",  XMLFOREST (  )),  CASE  WHEN   THEN    
> XMLELEMENT (  "3",  XMLFOREST (  ))  END),  /*   (SELECT XMLELEMENT ( 
> "4",  XMLAGG (XMLELEMENT ("5")))  FROM TABLE t1,  t2  WHERE t1.col1 =
> t2.col2)  ,*/    CASE  WHEN   THEN  (SELECT XMLAGG (  XMLELEMENT ( 
> "5", */(SELECT col1  FROM TABLE t1,  t2  WHERE t1.col1 = t2.col2),*/ 
> XMLFOREST ( ....

Есть два закомментированных выбора, которые, когда ЛИБО, не комментируются, превращают его в более быстрый запрос. t1 и t2 НЕ используются в другом месте запроса вообще.

Обновление 01/09 Вот планы выполнения: быстро http://pastebin.com/pbJMSxrB медленно http://pastebin.com/zt3eUYNd

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

Answer 1

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

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

select /*+parallel(table_a)*/ ...
from (
      select ...
      from table_a
      ...
      )
...

не будет работать

но

select  ...
from (
      select /*+parallel(table_a)*/ ...
      from table_a
      ...
      )
...

буду работать.

Answer 2

Небольшое изменение в запросе может потенциально повлиять на совсем другую часть запроса. Здесь изменение повлияло на то, как вид VIP_CODES_VW объединяется (это делается в двух местах, но второе оказывает большее влияние на производительность): в быстром запросе это делается с использованием NESTED LOOPS (строка 79), а в медленном - HASH JOIN (строка 75). Рассказать оптимизатору использовать NESTED LOOPSВы можете добавить подсказку /*+ USE_NL(VIP_CODES_VW) */ после SELECT в котором VIP_CODES_VW запрашивается.