query performance question

I have a general question.

I have a table TRANS with 1.5M records that I got from a transactional system. The table has a lot of "codes" in it, like eeo_code, race_code, and entry_status_code. I have a second, empty, table "BIG" that I'm going to put all of these 1.5M into. In addition, using a translation table LOOKUP, I'm going to put the translations for each of these codes for each record, for example, "Secretarial," "White," and "Freshman."

I'm trying to figure out what is the best approach. I only need to do this once.

1. I can use a giant select on TRANS that would join n-ways to the table LOOKUP (one join for each code) to produce a flat-file. Then, I would use the sqlldr to directly put this flat file into the table BIG. This is nice because I avoid logging.
2. I could use a simple select on TRANS to put the data into a flat file. Then, I would use sqlldr to directly put this flat file into the table BIG. Then, I would either
3. Use one UPDATE statement to go through the table and, for each code in the table, join with the LOOKUP table and store its translation. There would be n-joins in the whole UPDATE statement, but only one join for each SET.

or b) Use separate UPDATE statements, one for each code that I need to translate. Perhaps I would COMMIT after each successful update.

Perhaps there are other approaches as well?

Thanks in advance for any help! Received on Wed Jan 21 1998 - 00:00:00 CST