Найти последнюю дату в базе данных оракула

Question

У меня большие проблемы с запросом в базе данных оракула версии 10. Я хочу найти последнее свидание dateofstat Я пробовал много решений, но это работает, но это занимает слишком много времени. - Использование rownum
- Использование row_number ()
- используя rank()

Есть мои попытки:
1. rownum

select dateofstat from (
select  stat.dateofstat from dhg.statistics stat
join (
  select distinct assetid from dhg.relatedasset
     where (`CONDITION1`)
  MINUS
  select distinct assetid from dhg.relatedasset
 where (`CONDITION2`)
) grs 
on stat.assetid = grs.assetid
order by stat.dateofstat desc
)where rownum = 1

Объясните план:

row_number ()

select dateofstat from (
  select stat.dateofstat,
    row_number() over (order by stat.dateofstat desc) rnumber
  from dhg.statistics stat
  join (
    select distinct assetid from dhg.relatedasset
    where (`CONDITION1`)
    MINUS
    select distinct assetid from dhg.relatedasset
    where (`CONDITION2`)
  ) grs 
  on stat.assetid = grs.assetid
) where rnumber = 1

Объясните план:

rank(): это решение я пробовал, но оно дает повторяющийся номер ранга, поэтому я не думаю, что мне следует использовать это решение, чтобы найти лучшее.

Я не знаю, что мне теперь делать, очень нужна помощь. Для тестирования я использую sqlplus на emacs, без rownum Я получаю менее 3 секунд, чтобы получить первые строки этого запроса.

select  stat.dateofstat from dhg.statistics stat
join (
  select distinct assetid from dhg.relatedasset
     where (`CONDITION1`)
  MINUS
  select distinct assetid from dhg.relatedasset
 where (`CONDITION2`)
) grs 
on stat.assetid = grs.assetid
order by stat.dateofstat desc

Интересно, что я мог бы найти решение этой проблемы.

ОБНОВЛЕНИЕ РЕШЕНИЯ СТАТУС ОТ @ANTON

select  max(stat.dateofstat) from dhg.statistics stat
join (
 select distinct assetid from dhg.relatedasset
     where relatedasset.assetid in (191759.0, 3.0, 5.0, 98.0, 99.0)
   or relatedasset.linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0)
 MINUS
 select distinct assetid from dhg.relatedasset
     where relatedasset.assetid in (192106.0, 1014.0, 10302.0)
   or relatedasset.linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0)
 ) grs 
 on stat.assetid = grs.assetid

Я должен сказать, что я работал на этот раз, очень странное поведение. При первом выполнении у меня уходит всего 3 секунды, но во втором - гораздо больше времени (я не считал). Вот объяснение плана:

Кроме того, я также попробовал второе решение, которое использует exist а также not existsОднако, это не работает хорошо.

select  max(stat.dateofstat)
from dhg.statistics stat
where exists(select *
           from dhg.relatedasset rasset
           where stat.assetid = rasset.assetid
           and rasset.assetid in (191759.0, 3.0, 5.0, 98.0, 99.0)
           or rasset.linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0)
            )
and not exists (select *
           from dhg.relatedasset rasset2
           where stat.assetid = rasset2.assetid
           and rasset2.assetid in (192106.0, 1014.0, 10302.0)
           or rasset2.linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0)
            )

Этот запрос, план дают мне болезненный результат.

Answer 1

Из-за различных условий OR (IN и OR) на relatedasset СУБД решает выполнить полное сканирование таблицы. Это кажется разумным. Так что мы можем оптимизировать? Мы видим, что полное сканирование таблицы выполняется только один раз. Запросить таблицу, сгруппировать по assetid и проверьте с HAVING, было ли первое условие истинным для любой записи, а второе - ни для одного.

Вы также можете использовать параллельную подсказку, чтобы заставить Oracle выполнять полное сканирование таблицы параллельно, если это возможно.

select  max(dateofstat)
from dhg.statistics
where assetid in
(
  select /*+ parallel(relatedasset,4) */ assetid
  from dhg.relatedasset 
  group by assetid
  having 
    max( case when assetid in (191759.0, 3.0, 5.0, 98.0, 99.0) 
                or linkid in (3232.0, 1049.0, 1057.0, 1067.0, 102.0, 1032.0, 104.0, 105.0, 1051.0) 
         then 1 else 0 end ) = 1
  and
    max( case when assetid in (192106.0, 1014.0, 10302.0)
                or linkid in (210.0, 737.0, 126.0, 1053.0, 1054.0, 119.0, 3133.0) 
         then 1 else 0 end ) = 0
);

Answer 2

Почему так сложно?
если вам нужна только последняя дата, вы можете использовать функцию max():

select  max(stat.dateofstat)
from dhg.statistics stat
join (
  select distinct assetid from dhg.relatedasset
     where (`CONDITION1`)
  MINUS
  select distinct assetid from dhg.relatedasset
 where (`CONDITION2`)
) grs 
on stat.assetid = grs.assetid

Если таблица dhg.statistics не слишком велика, и вы можете предположить, что вам нужно проверить только несколько записей с самым высоким значением dateofstat, чтобы найти ту, которая удовлетворяет вашим требованиям к связанному активу, тогда вы можете переписать запрос следующим образом:

select  max(stat.dateofstat)
from dhg.statistics stat
where exists(select *
               from dhg.relatedasset asset1
              where (`CONDITION1`)
                and stat.assetid = asset1.assetid)
 and not exists (select *
               from dhg.relatedasset asset2
              where (`CONDITION2`)
                and stat.assetid = asset2.assetid)

Но если вам нужно выполнить слишком много проверок в связанной таблице активов, чтобы найти нужную статистику, производительность может снизиться.
ОБНОВЛЕНИЕ С УЧЕТОМ НОВЫХ ПЛАНОВ
Сстан прав, как statistics таблица большая (71M), а минус результат маленький (5), вам просто нужно правильно проиндексировать таблицу связанных активов
Я бы предложил индекс по relatedasset.assetid и по (relatedasset.linkid,relatedasset.assetid) чтобы избежать сканирования таблицы вообще.

Кстати, вы пропустили скобки во втором запросе
(как AND имеет более высокий приоритет, чем OR так принимая CONDITION1 а также CONDITION2 в скобках необходимо добиться правильного ГДЕ условия),
так что вы получаете план намного хуже, чем мог бы быть. Но в любом случае при таком распределении данных даже правильная версия будет работать медленнее, чем ваш вариант с правильной индексацией.

Answer 3

Похоже, вы просто не хватает индексов. Убедитесь, что у вас есть индексы на:

• relatedasset.assetid
• relatedasset.linkid

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