Re: query performance question

This is screaming for a nice parallel query, depending on your box, processors and datafile structures. A good parallel query can make a mole hill out of a mountain.

On Wed, 21 Jan 1998 14:31:11 -0500, Eric Spear <espear_at_deans.umd.edu> wrote:

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