Блог

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

Позвольте мне рассказать вам о своих требованиях :

У нас есть таблица UNIQUEREFERENCE, в которую мы вставляем entitykey (из 3 основных объектов, которые у нас есть, скажем, A, B и C) в сочетании с хешем ссылочных полей (Varchar), поскольку столбцы такие, как показано ниже :

 UNIQUEREFERENCEKEY REFERENCENAME REFERENCE IDENTIFIER1 IDENTIFIER2 SEQUENCE ORIGINALAKEY ORIGINALBKEY  ORIGINALCKEY ORIGINALENTITYTYPE ORIGINALDISPLAYENTITYID REFERENCETIME ID3 
  

Теперь мне нужно написать запрос, который не должен приводить ни к одной строке из приведенной выше таблицы, убедившись, что ни один из 3 объектов не имеет статуса «Отклонено» или «Отменено». Если возвращается какая-то строка, мы рассматриваем ее как дубликат A, B или C, основываясь на том, что было ORIGINALENTITYTYPE .

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

Моя попытка до сих пор :

  select
            REFERENCENAME as referenceName,
            REFERENCE as reference,
            IDENTIFIER1 as Identifier1,
            IDENTIFIER2 as Identifier2,
            max(SEQUENCE) as maxSequence,
            count(1) as totalCount,
            min(ORIGINALDISPLAYENTITYID) keep (dense_rank first order by sequence ) as firstDisplayId,
            min(ID3) keep (dense_rank first order by sequence ) as firstId3,
            min(case
                    when 'com.example.domain.A' = :checkEntityName and ORIGINALENTITYTYPE = :checkEntityName and ORIGINALAKEY = :checkEntityKey
                        then ORIGINALAKEY
                    when 'com.example.domain.B' = :checkEntityName and ORIGINALENTITYTYPE = :checkEntityName and ORIGINALBKEY = :checkEntityKey
                        then ORIGINALBKEY
                    when 'com.example.domain.C' = :checkEntityName and ORIGINALENTITYTYPE = :checkEntityName and ORIGINALCKEY = :checkEntityKey
                        then ORIGINALCKEY
                    else null
                end) as entityKey
        from UNIQUEREFERENCE
        where REFERENCENAME = :referenceName
            and REFERENCE = :reference
            and NVL(IDENTIFIER2, 'N/A') = NVL(:Identifier2, 'N/A')
            and NVL(IDENTIFIER1, 'N/A') = NVL(:Identifier1, 'N/A')
        group by REFERENCENAME, REFERENCE, IDENTIFIER1, IDENTIFIER2 
  

Как вы можете видеть, параметры checkEntityName и checkEntityKey будут заменены во время выполнения в этом общем запросе для всех 3 объектов.

Теперь мне просто нужно сделать соединение с 3 EntityKey из entity table, чтобы убедиться, что мы не рассматриваем те объекты (A, B и C), статус которых («Отклонен», «Отменен»), и я пока не могу придумать идеальный оптимизированный запрос.

Любая помощь была бы очень признательна или любой лучший способ решить эту проблему с помощью одного SQL-запроса.

Спасибо.

Обновление: добавление образцов данных по запросу.

Таблица уникальных ссылок

 1 TEST1 XYZ1234 null ABCD SEQ12345 1231 null null com.example.domain.A null  
2 TEST2 XYZ4567 null ABCD SEQ12346 null 2341 null com.example.domain.B null  
3 TEST3 XYZ8910 null ABCD SEQ12347 null null 5671 com.example.domain.C null  
  

А затем таблица сущностей A

 s.no  reference   status  
1     XYZ1234     Rejected  
2     XYZ4561     Processed  
3     XYZ7891     Cancelled  
  

А затем таблица сущностей B

 s.no  reference   status  
1     XYZ4567     Processed
2     XYZ6561     Processed  
3     XYZ8891     Cancelled  
  

А затем таблица сущностей C

 s.no  reference   status  
1     XYZ8910     Cancelled  
2     XYZ8562     Processed  
3     XYZ1789     Cancelled  
  

Из приведенных выше данных uniquereference я не ожидаю, что строка 1 или 3 будет возвращена в случае, если EntityType равен A или C, потому что соответствующая ссылка (XYZ1234,XYZ8910) для A и C имеет статус Отклонено / Отменено соответственно.
В то время как строка 2 uniquereference будет возвращена как объект, B не отклоняется или не отменяется, и, следовательно, ссылка не может быть использована повторно, и она будет дублироваться для этого варианта использования.

Мне не совсем понятны некоторые детали вашего использования, но этот подход может помочь, если я правильно понимаю:

 SELECT UR.reference
     , UR.fieldA
     , UR.fieldB
     , UR.fieldN
     , COUNT(EntityA.sno)  
       COUNT(EntityB.sno)  
       COUNT(EntityC.sno) as subrecords
FROM UniqueReference UR
LEFT JOIN EntityA A
    ON UR.reference = A.reference
    AND A.status = 'PROCESSED'
LEFT JOIN EntityB B
    ON UR.reference = B.reference
    AND B.status = 'PROCESSED'
LEFT JOIN EntityC C
    ON UR.reference = C.reference
    AND C.status = 'PROCESSED'
GROUP BY UR.reference
HAVING subrecords > 0
-- this may need to be: HAVING COUNT(EntityA.sno)   COUNT(EntityB.sno)   COUNT(EntityC.sno) > 0