Проблема производительности с встроенным представлением в Oracle

Question

У меня есть запрос, который выглядит как ниже, и таблицы A,T,S имеют около 1 миллиона строк, тогда как P имеет более 100 миллионов строк. Я недавно ввел встроенное представление "temp" в этот запрос, и это вызвало резкое снижение производительности. Данные, извлеченные для temp, едва ли составляют 50 строк, и этот встроенный запрос выполняется в одно мгновение при выполнении в одиночку.

Статистика автотрассировки показывает огромный рост числа "последовательных получателей" от 6-значного числа до введения временного числа в 9-значное число после добавления этого!! Кроме того, более 90% LAST_CR_BUFFER_GETS учитываются для "временного" представления. Если я извлекаю данные из этого представления во временную таблицу и использую эту таблицу как часть объединений, производительность будет очень хорошей, но это решение для меня не реально.

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

   select t.id, 
          a.date
     from A a,
          T t,
          P p,
          S s,
          (select id 
             from S, 
                  R 
            where s.id = r.id 
              and r.code  = 10
                  r.code1 = 20
                  r.name  = 'string1' ) temp
    where ...cond1
          ...cond2
          ...cond2
    s.id = temp.id

Answer 1

Насколько я понимаю, исходя из моего опыта настройки, он, вероятно, оценивает встроенное представление "temp" один раз для каждой записи, совпадающей с результирующим набором, полученным путем объединения A, t, p, s. Лучшее решение здесь - переписать это так. Помните, что подсказка ORDERED предполагает, что вы предоставляете таблицы в предложении FROM в том порядке, в котором вы хотите, чтобы они присоединились. Сначала я перечислил temp и s, потому что это единственное условие соединения, которое вы перечислили temp.id = s.id. Также я предполагаю, что у вас есть индексы для всех остальных столбцов, которые являются частью критериев объединения. Позвольте мне знать, если у вас есть еще вопросы.

select  /*+ ordered use_nl(a t p s) */
    t.id,  a.date
from    (
    select  id 
    from    S, 
        R
    where   s.id = r.id and r.code  = 10 r.code1 = 20 r.name  = 'string1' 
    ) temp,
    S s,
    A a,
    T t,
    P p
where   ...cond1 ...cond2 ...cond2 and s.id = temp.id

Answer 2

WITH TEMP AS
(select id 
         from S, 
              R 
        where s.id = r.id 
          and r.code  = 10
              r.code1 = 20
              r.name  = 'string1' )
select t.id, 
      a.date
 from A a,
      T t,
      P p,
      S 
where ...cond1
      ...cond2
      ...cond2
 s.id = temp.id;

попробуйте выполнить этот запрос, и, пожалуйста, предоставьте план объяснения для этого запроса