Избегайте многократных поисков и повышайте производительность

У меня есть случай, когда мне нужно выполнить несколько соединений (поиск), как показано ниже. Был приведен пример сценария.

У меня около 200 CAT_CODE. Я подумал о нескольких решениях и перечислил их как случаи. Есть ли другой способ написать SQL-запрос для повышения производительности? или какой-либо лучший подход в инструменте ETL?

Основная таблица (PRIM):

NUM     CAT1_CODE   CAT2_CODE   CAT3_CODE
A          1           y           q     
B          2           e           a     
C          3           s           z

Дополнительная таблица (LOV):

CATEGORY    COLUMN_LKP        EXT_CODE
CAT1_CODE       1                AB
CAT1_CODE       2                CD
CAT1_CODE       3                HI
CAT2_CODE       y                JL
CAT2_CODE       e                QD
CAT2_CODE       s                AH
CAT3_CODE       q                CD
CAT3_CODE       a                MS
CAT3_CODE       z                EJ

СЛУЧАЙ 1. Через SQL:

Я написал простой запрос для выполнения этой задачи. Как вы думаете, это был бы правильный подход? Любые другие способы улучшить этот запрос? Сейчас я использую и Oracle, и Postgres.

SELECT 
NUM,
(SELECT EXT_CODE FROM TEST_LOV 
WHERE CATEGRY='CAT1_CODE' AND COLUMN_LKP=A.CAT1_CODE) CAT1,
(SELECT EXT_CODE FROM TEST_LOV 
WHERE CATEGRY='CAT2_CODE' AND COLUMN_LKP=A.CAT2_CODE) CAT2,
(SELECT EXT_CODE FROM TEST_LOV 
WHERE CATEGRY='CAT3_CODE' AND COLUMN_LKP=A.CAT3_CODE) CAT3 
FROM 
TEST_PRIM A

НЕОБХОДИМЫЕ ВЫВОДЫ:

NUM CAT1    CAT2    CAT3
A    AB      JL      CD
B    CD      QD      MS
C    HI      AH      EJ

СЛУЧАЙ 2: ETL:

Тот же случай может быть выполнен через ETL. Нам нужно использовать поиск, чтобы сделать это.

Сценарий 1:

       LOV(CAT1_CODE)  LOV(CAT2_CODE)   LOV(CAT3_CODE)
           |                |                  |
           |                |                  |
PRIM---->LOOKUP---------->LOOKUP------------>LOOKUP-------->TARGET

Не думаю, что это был бы правильный подход. У нас есть 200 кодов, мы не можем использовать поиск по 200. Есть ли лучший способ справиться с этим в ETL (Datastage, Talend, BODS) с лучшей производительностью?

Сценарий 2:

Поворот PRIM (преобразование столбцов CAT1_CODE, CAT2_CODE, CAT3_CODE в строки), как показано ниже, и выполнение одного поиска. Но поворот займет много времени, потому что у нас есть данные около 600 миллионов и 200 столбцов.

NUM     CATGRY           CODE
A       CAT1_CODE          1
A       CAT1_CODE          y
A       CAT1_CODE          q
B       CAT2_CODE          2
B       CAT2_CODE          e
B       CAT2_CODE          a 
C       CAT3_CODE          3
C       CAT3_CODE          s
C       CAT3_CODE          z

Пожалуйста, предложите мне лучший способ справиться с этим подходом. Это может быть через ETL или через sql. Заранее спасибо.


postgresql oracle talend datastage sql-data-services
person Raja    schedule 01.04.2016    source источник
comment
Пожалуйста, отметьте свой вопрос соответствующим образом. Какую базу данных вы используете? Postgres или Oracle (или что-то еще)?   -  person Gordon Linoff    schedule 01.04.2016
comment
Нам нужно реализовать этот случай как в оракуле, так и в postgres (другое приложение).   -  person Raja    schedule 01.04.2016

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете использовать LATERAL ключевое слово, чтобы сделать волшебство, которое вы ищете.

Следующий код может помочь:

SELECT 
  NUM, 
  MAX(ext_code) FILTER (WHERE c.CATEGORY='CAT1_CODE') AS CAT1,
  MAX(ext_code) FILTER (WHERE c.CATEGORY='CAT2_CODE') AS CAT2,
  MAX(ext_code) FILTER (WHERE c.CATEGORY='CAT3_CODE') AS CAT3
FROM TEST_PRIM a
  CROSS JOIN LATERAL (
    SELECT * 
    FROM TEST_LOV b 
    WHERE 
      (a.CAT1_CODE=b.COLUMN_LKP AND B.CATEGORY = 'CAT1_CODE')
      OR (a.CAT2_CODE=b.COLUMN_LKP AND B.CATEGORY = 'CAT2_CODE')
      OR (a.CAT3_CODE=b.COLUMN_LKP AND B.CATEGORY = 'CAT3_CODE')
    ) c
 GROUP BY NUM
 ORDER BY NUM;

Вывод

 num | cat1 | cat2 | cat3
-----+------+------+------
 A   | AB   | JL   | CD
 B   | CD   | QD   | MS
 C   | HI   | AH   | EJ
person Robins Tharakan    schedule 01.04.2016
comment
Спасибо, Робин. Однако ваш код будет полезен для создания запроса к n ​​столбцам. Но мой вопрос в другом. Мой запрос даст лучшую производительность? или любой другой способ, чтобы сделать это быстрее? - person Raja; 03.04.2016
comment
Обновленный ответ с требуемым SQL. Функция, которую вы ищете, называется LATERAL запросы, поддерживаемые в Postgres v9.4+. 9 - person Robins Tharakan; 05.04.2016
comment
Подробнее о LATERAL здесь (depesz.com/2013/07/23/) - person Robins Tharakan; 05.04.2016
comment
Спасибо, Робин. Хотя Pivot хорош. Но наш исходный объем огромен. Так что мы планируем придерживаться сценария-1 - person Raja; 05.04.2016
comment
Предоставленное решение не является запросом PIVOT. - person Robins Tharakan; 05.04.2016