Избегание коррелированных подзапросов в Oracle

В Oracle 9.2.0.8 мне нужно вернуть набор записей, в котором конкретное поле (LAB_SEQ) находится на максимуме (это последовательный массив VARCHAR '0001', '0002' и т. Д.) Для каждого другого поля (WO_NUM) . Чтобы выбрать максимум, я пытаюсь упорядочить по убыванию и выбрать первую строку. Все, что я могу найти на StackOverflow, предполагает, что единственный способ сделать это - использовать коррелированный подзапрос. Затем я использую этот максимум в предложении WHERE внешнего запроса, чтобы получить строку, которую я хочу для каждого WO_NUM:

SELECT lt.WO_NUM, lt.EMP_NUM, lt.LAB_END_DATE, lt.LAB_END_TIME
FROM LAB_TIM lt WHERE lt.LAB_SEQ = (
   SELECT LAB_SEQ FROM (
      SELECT lab.LAB_SEQ FROM LAB_TIM lab WHERE lab.CCN='1' AND MAS_LOC='1'
          AND lt.WO_NUM = lab.WO_NUM ORDER BY ROWNUM DESC
   ) WHERE ROWNUM=1
)

Однако это возвращает недопустимый идентификатор ошибки lt.WO_NUM. Исследования показывают, что ORacle 8 допускает коррелированные подзапросы только на один уровень в глубину, и предлагает переписать, чтобы избежать подзапроса - то, что, как предполагает обсуждение выбора максимумов, невозможно. Любая помощь в выполнении этого оператора будет принята с благодарностью.


sql database oracle subquery oracle9i
person user1412922    schedule 23.05.2012    source источник
comment
Заказ по ROWNUM здесь не особо полезен.   -  person DCookie    schedule 23.05.2012

Ответы (2)


arrow_upward
3
arrow_downward

Ваш коррелированный подзапрос должен иметь вид

SELECT lt.WO_NUM, lt.EMP_NUM, lt.LAB_END_DATE, lt.LAB_END_TIME
FROM LAB_TIM lt WHERE lt.LAB_SEQ = (
   SELECT max(lab.LAB_SEQ)
     FROM LAB_TIM lab 
    WHERE lab.CCN='1' AND MAS_LOC='1'
      AND lt.WO_NUM = lab.WO_NUM 
  )

Поскольку вы используете Oracle 9.2, вероятно, будет более эффективно использовать коррелированный подзапрос. Я не уверен, что делают предикаты lab.CCN='1' AND MAS_LOC='1' в вашем текущем запросе, поэтому я не совсем уверен, как преобразовать их в подход аналитических функций. Не является ли комбинация LAB_SEQ и WO_NUM уникальной в LAB_TIM? Вам нужно добавить предикаты для CCN и MAS_LOC, чтобы получить единственную уникальную строку для каждого WO_NUM? Или вы используете эти предикаты для уменьшения количества строк в выводе? Базовый подход будет примерно таким:

SELECT *
  FROM (SELECT lt.WO_NUM, 
               lt.EMP_NUM, 
               lt.LAB_END_DATE, 
               lt.LAB_END_TIME,
               rank() over (partition by wo_num
                                order by lab_seq desc) rnk
          FROM LAB_TIM lt)
   WHERE rnk = 1

но мне не ясно, нужно ли добавлять CCN и MAS_LOC в предложение ORDER BY в аналитической функции или их нужно добавлять в предложение WHERE.

person Justin Cave    schedule 23.05.2012
comment
+1, хотя в 8i уже была аналитика :) - person Vincent Malgrat; 23.05.2012
comment
@VincentMalgrat - Вы правы - похоже, это было введено в 8.1.6. - person Justin Cave; 23.05.2012
comment
Спасибо, Джастин. Хотя это и достигает моей цели, оно сильно неэффективно. Поскольку функция max () будет перебирать каждую запись в таблице (которая имеет большой размер), я хотел бы воспользоваться тем фактом, что на самом деле строки расположены по порядку. Я бы подумал, что сортировка по ROWNUM будет более эффективной, чем LAB_SEQ, поскольку сравнение строк отнимает много времени. Предложенный вами запрос выполняется уже около 5 минут, и конца ему не видно. - person user1412922; 23.05.2012
comment
@ user1412922 - Строки в таблице, организованной в виде кучи, по своей сути не расположены в беспорядке, пока вы не укажете предложение ORDER BY. Упорядочивание по ROWNUM эквивалентно опусканию предложения ORDER BY и возвращает данные в произвольном порядке. В очень контролируемых средах (данные никогда не удаляются и не обновляются, только один сеанс вставляет данные за раз, данные никогда не экспортируются, объекты никогда не реорганизуются, параллельный запрос никогда не используется) данные обычно будут возвращаться в том порядке, в котором они были вставленные таким поведением не следует полагаться. Если проблема в производительности, какие существуют индексы? - person Justin Cave; 23.05.2012
comment
Я беру это обратно, ваш последующий запрос работал хорошо, менее чем через минуту на выполнение. Спасибо за помощь! Кстати, я проверил, и наша СУБД на самом деле 9i версии 9.2.0.8. Приходят ли тогда на ум какие-то дополнительные повышения эффективности? - person user1412922; 23.05.2012
comment
@ user1412922 - Аналитические запросы, вероятно, по-прежнему будут наиболее эффективным подходом в Oracle 9.2. Я обновил свой ответ с некоторыми оговорками относительно предикатов CCN и MAS_LOC - person Justin Cave; 23.05.2012

arrow_upward
0
arrow_downward

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

Я думаю, что следующее эквивалентно без коррелированного подзапроса:

SELECT lt.WO_NUM, lt.EMP_NUM, lt.LAB_END_DATE, lt.LAB_END_TIME
FROM (select *, rownum as r
      from LAB_TIM lt
     ) lt join
     (select wo_num, max(r) as maxrownum
      from (select LAB_SEQ, wo_num, rownum as r
            from LAB_TIM lt
            where lab.CCN = '1' AND MAS_LOC = '1'
           ) 
     ) ltsum
     on lt.wo_num = ltsum.wo_num and
        lt.r = ltsum.maxrownum

Я немного не уверен в том, как Oracle работает с rownums в таких вещах, как ORDER BY.

person Gordon Linoff    schedule 23.05.2012