Need a better option - outer joining 32 times to same table

Question

I have a nasty SQL query problem and I'd love help with an elegant solution. I'm trying to avoid 32 left outer joins to the same table.

The database is Teradata.

I have a table with 14 million records and 33 columns. The primary key (let's call it Trans_Id), and 32 encoded fields (let's call them encoded_1 ... encoded_32). Something like this:

CREATE SET TABLE BigTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
     (
      TRANS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      ENCODED_1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ENCODED_2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ENCODED_3 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      ...
      ENCODED_32 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC )
PRIMARY INDEX ( TRANS_ID );

I also have a single table with the coded / decoded values. Let's say there are 100 records in this table.

CREATE SET TABLE LookupTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
     (
      UNIQ_PK { just random numbers }
      ENCODED_VAR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      DECODED_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( UNIQ_PK );

I want to avoid a nasty join like this (I used ellipses instead of showing all 32 outer joins):

SELECT 
TRANS_ID
, a.ENCODED_1
, b1.DECODED_DESC DECODED_DESC_1
, a.ENCODED_2
, b2.DECODED_DESC DECODED_DESC_2
...
, a.ENCODED_31
, b31.DECODED_DESC DECODED_DESC_31
, a.ENCODED_32
, b32.DECODED_DESC DECODED_DESC_32
FROM BigTable a
LEFT OUTER JOIN LookupTable b1 ON a.ENCODED_1 = b1.ENCODED
LEFT OUTER JOIN LookupTable b2 ON a.ENCODED_2 = b1.ENCODED
...
LEFT OUTER JOIN LookupTable b31 ON a.ENCODED_31 = b31.ENCODED
LEFT OUTER JOIN LookupTable b32 ON a.ENCODED_32 = b32.ENCODED

Any help would be appreciated. I have a feeling outer joining 14M records 32 times is not the efficient way to do this!

" have a feeling outer joining 14M records 32 times is not the efficient way to do this!" - is it slow? — Mitch Wheat, May 12 '11 at 05:45
If the second table is so small (100 records) that the whole table can be held in memory, then I can't see a way to make this better. It looks nasty but is it really slow? — ypercubeᵀᴹ, May 12 '11 at 05:48
Could you elaborate on what you are trying to achieve? Are you really going to return 14M records? You are "LEFT JOIN"-ing because some fields in your BigTable have values that are not your LookupTable? — Jacco, May 12 '11 at 05:49
Why would you query it all? I mean, do you want to have all 14M records seen together? — shahkalpesh, May 12 '11 at 05:50
It's not particularly slow (but it is expensive - 32 all amps, all-rows scans). I'm not as concerned about the speed of the query as I am just how nasty it is to hammer out. The columns each have unique names with no pattern, and I don't have an intern to do it. I need the decoded text for reported and analysis - samples (and resamples) from the 14M will be taken regularly and distributed. — LukeK, May 12 '11 at 06:15
If this is a stable set of data, why not create a result table once with the Lookup Table merged into the big table, and query that when you want samples/resamples ? — Steve De Caux, May 12 '11 at 06:53

score 3 · Answer 1 · answered May 12 '11 at 06:27

3

You could create a function that takes as a parameter the VARCHAR(10) encoded_var and return the VARCHAR(50) decoded_desc, then your select would be something like this:

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

Depending on the number of rows you are planning on returning at a time, this would be doable.

answered May 12 '11 at 06:27

user749976

31
2

Oh I like this, but I'm not sure how to write UDFs in Teradata. – LukeK May 12 '11 at 07:08

score 2 · Answer 2 · answered May 12 '11 at 06:18

If encoded_1, encoded_2, etc are all being used as look up keys to the same table, it sounds like they are all the "same idea". But my first thought is that a better design in this case would be:

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

Then the query just becomes:

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

I don't know if this is the real field name or if you're just trying to leave out irrelevant details. You may be leaving out relevant details here. What's the difference between encoded_1 and encoded_2, etc? If they are interchangeable, there is no reason to have separate fields for them. Indeed, it causes a lot of problems. Even if there is a semantic difference, if they all use the same lookup table, they must all be coming from the same domain.

For example, a few years ago I worked on a system to manage technical manuals that our organization produced and used. Each manual had 3 managers. (An administrative manager who handled budgets and schedules, a stock manager who kept track of who needed copies and made sure they got them, and a content manager responsible for the actual text.) But they were all drawn from the same list of people, as often the same person would have more than one of these roles or could have different roles for different manuals. So we made a table of "people" with an id, name, email address, etc, and then in the basic manual record I created 3 columns, one for each manager type.

This was a huge mistake. What I should have done was create a separate table with manual id, manager type id, and person id, and then have 3 RECORDS for the 3 manager types rather than 3 fields within one record.

Why? With three columns, I ran into the same problem you are describing, though on a smaller scale: I had to join from the manual table to the person table three times. A query like "what books is Bob Smith responsible for?" required a surprising complex query, something like

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'

With a single column it would have been simply

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'"

With all the repetition, it was not surprising that there were a couple of times that a programmer accidentally only checked 2 of the fields instead of all 3. With 1 field this error wouldn't be possible. With 3 fields, if we added a 4th type of manager, we would have had to add another column, and then change every query that looked at these fields. With 1 field, we probably wouldn't. Etc.

With 3 fields we needed 3 indexes, and there are other performance implications.

I suspect the same sort of thinking applies to you.

If your 32 fields are all completely interchangeable, then the table would only need a sequence number to make a unique pk. If there is some difference between them, then you could create a code to distinguish them.

not to say that repeated (group of) fields violate normal form 1. — Victor Sergienko, May 12 '11 at 06:26
Yes. I was going to suggest he read my book, "A Sane Approach to Database Design", which explains normalization and lots of other things. But making such a shameless plug on a forum like this would be too mercenary. :-) — Jay, May 12 '11 at 15:56

score 1 · Answer 3 · answered May 12 '11 at 14:15

I would change the PI on the LookUp table to the Encoded_Var for starters. You already have to redistribute the big table on each of the Encoded_Var columns in order to join to the LookUp table. Why bother having to redistribute the LookUp table each time as well.

Is there a reason why your table design isn't something closer to

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 );

This would build a more appropriate 1:M relationship between a trans_id and encoded_var. Unless there are pertinent details that have been left out that would explain why this would not work. In fact, if necessary you could build this table as relationship table and have another table that looks like:

    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 );

Hope that helps.

score 1 · Answer 4 · answered May 13 '11 at 06:15

If you don't want to repeatedly write the same query, I'd suggest putting it in a view.

For performance, I'd suggest the following:

As Rob Paller recommended, change primary index on LookupTable to ENCODED_VAR.
In LookupTable, add a new record with DECODED_DESC = null and ENCODED_VAR = some value you will never use. Update BigTable to replace all null ENCODED_* with this value. You can then change your query to use all inner joins and get the same result.

score 0 · Answer 5 · answered May 13 '11 at 05:09

Couldn't you have it like this:

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
 )

?

I might also be tempted to rewrite the joining condition as follows:

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

But I'm not really sure if it can't be slower than the former version. Actually, my guess is, it would indeed turn out slower, but I would still verify that to be sure.

score 0 · Answer 6 · answered Jul 29 '11 at 01:56

I have encountered the same problem, also on Teradata. A colleague has informed me of an elegant solution for you using a single LEFT OUTER JOIN, and a number of CASE statements.

However, your example is a little confusing, because you are joining on a column the doesn't exist ("ENCODED" column in "LookupTable", which I assume should be "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

This does rely on there being a 1:1 relationship between ENCODED_n in BigTable and ENCODED_VAR in LookupTable.

Also, as an aside, you shouldn't use a random number as the PRIMARY INDEX in a Teradata table. While this will give you great table distribution, it will be completely useless when doing a table lookup. If you use a commonly joined field for the PI, the database can go directly to the AMP on which the data is stored. However, without this, the DBMS must do a full-table scan every time. You could likely use ENCODED_VAR as your PRIMARY INDEX and see much improved performance, as long as the distribution was still reasonable.

I hope this works. I think this will work in your case. I haven't verified my code is correct, but it's very similar to my own solution.

Need a better option - outer joining 32 times to same table

6 Answers6