Нуждаете се от по-добра опция - външно свързване 32 пъти към една и съща таблица

Бихте могли да създадете функция, която приема като параметър VARCHAR(10) encoded_var и връща VARCHAR(50) decoded_desc, тогава вашият избор ще бъде нещо подобно:

SELECT TRANS_ID,
     ENCODED_1, somefunc(ENCODED_1) AS DECODED_DESC_1,
     ENCODED_2, somefunc(ENCODED_2) AS DECODED_DESC_2,
     etc.

В зависимост от броя редове, които планирате да върнете наведнъж, това би било изпълнимо.

Ако encoded_1, encoded_2 и т.н. всички се използват като ключове за търсене към една и съща таблица, изглежда, че всички те са „една и съща идея“. Но първата ми мисъл е, че по-добър дизайн в този случай би бил:

big_table (trans_id, var_id, encoded_var)
lookup_table (encoded_var, decoded_desc)

Тогава заявката просто става:

select trans_id, var_id, encoded_var, decoded_desc
from big_table
join lookup_table on lookup_table.encoded_var=big_table.encoded_var

Не знам дали това е истинското име на полето или просто се опитвате да пропуснете неуместни подробности. Може да пропускате съответните подробности тук. Каква е разликата между encoded_1 и encoded_2 и т.н.? Ако те са взаимозаменяеми, няма причина да има отделни полета за тях. Наистина създава много проблеми. Дори и да има семантична разлика, ако всички те използват една и съща справочна таблица, всички те трябва да идват от един и същи домейн.

Например, преди няколко години работих върху система за управление на технически ръководства, които нашата организация създава и използва. Всеки наръчник имаше 3 ръководители. (Административен мениджър, който се занимаваше с бюджети и графици, мениджър на склад, който следеше кой има нужда от копия и се уверяваше, че ги получава, и мениджър на съдържанието, отговорен за действителния текст.) Но всички те бяха избрани от един и същ списък с хора, тъй като често едно и също лице ще има повече от една от тези роли или може да има различни роли за различни ръководства. Така че направихме таблица с „хора“ с идентификатор, име, имейл адрес и т.н., а след това в основния ръчен запис създадох 3 колони, по една за всеки тип мениджър.

Това беше огромна грешка. Това, което трябваше да направя, беше да създам отделна таблица с ръчен идентификатор, идентификатор на тип мениджър и идентификатор на лице и след това да имам 3 ЗАПИСА за 3 типа мениджър, а не 3 полета в един запис.

Защо? С три колони се натъкнах на същия проблем, който описвате, макар и в по-малък мащаб: трябваше да се присъединя от ръчната таблица към таблицата на хората три пъти. Запитване като "за какви книги отговаря Боб Смит?" изискваше изненадващо сложна заявка, нещо като

select ... whatever ...
from manual
join person p1 on p1.person_id=manual.admin_id
join person p2 on p2.person_id=manual.stockmanager_id
join person p3 on p3.person_id=manual.contentmanager_id
where p1.name='Bob Smith'
 or p2.name='Bob Smith'
 or p3.name='Bob Smith'

С една колона щеше да е просто

select ... whatever ...
from manual
join manual_manager on manual_manager.manual_id=manual.manual_id
join person on person.person_id=manual_manager.person_id
where person.name='Bob Smith'"

С цялото повторение не беше изненадващо, че имаше няколко пъти, когато програмист случайно провери само 2 от полетата вместо всичките 3. С 1 поле тази грешка не би била възможна. С 3 полета, ако добавим 4-ти тип мениджър, ще трябва да добавим още една колона и след това да променим всяка заявка, която разглежда тези полета. С 1 поле вероятно не бихме го направили. и т.н.

С 3 полета имахме нужда от 3 индекса и има други последици за производителността.

Подозирам, че същото мислене важи и за вас.

Ако вашите 32 полета са напълно взаимозаменяеми, тогава таблицата ще се нуждае само от пореден номер, за да направи уникален pk. Ако има някаква разлика между тях, можете да създадете код, който да ги различава.

Бих променил PI на таблицата LookUp на Encoded_Var като начало. Вече трябва да преразпределите голямата таблица във всяка от колоните Encoded_Var, за да се присъедините към таблицата LookUp. Защо да си правите труда да преразпределяте таблицата LookUp всеки път.

Има ли причина дизайнът на вашата маса да не е нещо по-близко до

CREATE SET TABLE BigTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
     (
      TRANS_ID    VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ENCODED_VAR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
UNIQUE PRIMARY INDEX ( TRANS_ID, ENCODED_VAR );

Това би изградило по-подходяща връзка 1:M между trans_id и encoded_var. Освен ако няма уместни подробности, които са пропуснати, които биха обяснили защо това няма да работи. Всъщност, ако е необходимо, можете да създадете тази таблица като таблица на релации и да имате друга таблица, която изглежда така:

    CREATE SET TABLE BigTable2 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
         (
          TRANS_ID    VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
          OtherData1  VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
          OtherData2  SMALLINT NOT NULL,
          ....,
          OtherDataN  VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
   UNIQUE PRIMARY INDEX ( TRANS_ID );

Надявам се това да помогне.

Ако не искате да пишете многократно една и съща заявка, предлагам да я поставите в изглед.

За производителност бих предложил следното:

• Както препоръчва Роб Палер, променете основния индекс на LookupTable на ENCODED_VAR.
• В LookupTable добавете нов запис с DECODED_DESC = null и ENCODED_VAR = някаква стойност, която никога няма да използвате. Актуализирайте BigTable, за да замените всички null ENCODED_* с тази стойност. След това можете да промените заявката си, за да използвате всички вътрешни съединения и да получите същия резултат.

Не може ли да го имаш така:

SELECT 
TRANS_ID
, a.ENCODED_1
, CASE a.ENCODED_1 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_1
, a.ENCODED_2
, CASE a.ENCODED_2 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_2
...
, a.ENCODED_31
, CASE a.ENCODED_31 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_31
, a.ENCODED_32
, CASE a.ENCODED_32 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_32
FROM BigTable a
 LEFT JOIN LookupTable b ON (
   a.ENCODED_1 = b.ENCODED OR
   a.ENCODED_2 = b.ENCODED OR
   ...
   a.ENCODED_31 = b.ENCODED OR
   a.ENCODED_32 = b.ENCODED
 )

?

Може също да се изкуша да пренапиша условието за присъединяване, както следва:

...ON b.ENCODED IN (a.ENCODED_1, a.ENCODED_2, ... a.ENCODED_31, a.ENCODED_32)

Но не съм много сигурен дали не може да бъде по-бавен от предишната версия. Всъщност, моето предположение е, че наистина ще стане по-бавно, но все пак бих проверил това, за да съм сигурен.

Срещнах същия проблем, също и на Teradata. Един колега ме информира за елегантно решение за вас, като използвате единичен LEFT OUTER JOIN и няколко оператора CASE.

Вашият пример обаче е малко объркващ, защото се присъединявате към колона, която не съществува (колона „ENCODED“ в „LookupTable“, която предполагам, че трябва да бъде „ENCODED_VAR“?).

SELECT     TRANS_ID    
           , a.ENCODED_1    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_1
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_1    
           , a.ENCODED_2    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_2
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_2    
           ...    
           , a.ENCODED_31    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_31
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_31    
           , a.ENCODED_32    
           , MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_32
                 THEN b.DECODED_DESC
                 ELSE NULL
             END) DECODED_DESC_32    
FROM BigTable a
LEFT OUTER JOIN LookupTable b 
    ON a.ENCODED_1 = b.ENCODED_VAR
    OR a.ENCODED_2 = b.ENCODED_VAR
    ...
    OR a.ENCODED_31 = b.ENCODED_VAR
    OR a.ENCODED_32 = b.ENCODED_VAR
GROUP BY a.TRANS_ID 

Това разчита на наличието на връзка 1:1 между ENCODED_n в BigTable и ENCODED_VAR в LookupTable.

Освен това, настрана, не трябва да използвате произволно число като ОСНОВЕН ИНДЕКС в таблица Teradata. Докато това ще ви даде страхотно разпределение на таблицата, ще бъде напълно безполезно, когато правите търсене в таблица. Ако използвате често съединено поле за PI, базата данни може да отиде директно към AMP, на който се съхраняват данните. Без това обаче СУБД трябва да извършва сканиране на пълна таблица всеки път. Вероятно бихте могли да използвате ENCODED_VAR като ваш ОСНОВЕН ИНДЕКС и да видите много подобрена производителност, стига разпределението все още да е разумно.

Надявам се това да работи. Мисля, че това ще свърши работа във вашия случай. Не съм проверил, че кодът ми е правилен, но е много подобен на собственото ми решение.