Re: query performance question

Eric Spear wrote in message <34C64CFF.F72A8BC6_at_deans.umd.edu>...
>
>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."

My first suggestion will be to do a CREATE TABLE using UNRECOVERABLE AS SELECT. This method beats any other method of creating a new table from existing tables ito performance.

>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.

You can also create a FIFO pipe first, fire-up SQL*Loader loading from the pipe and then start the SQL*Plus to spool to the pipe. You can also run SQL*Loader in direct and parallel modes to increase load speed.

>2) I could use a simple select on TRANS to put the data into a flat
>file.

<snipped the stuff about using sql updates to update the data>

Bad Thing (tm). Stay away from SQL updates and PL/SQL as far possible when creating tables containing a lot of data. The only time we ever use transaction based processing is to append data to a large table using INSERT SELECTs. And then the number of rows that's appended is usually less than 1/2 million.

Another useful function is DECODE. If the number of values that you want to decode is limited (e.g. the GENDER column contains M, F or X) then decode instead of using a join to the lookup table (e.g. DECODE(gender, 'M','male', 'F','female', 'X','see alt.aliens', 'unknown') ).

regards,
Billy Received on Mon Jan 26 1998 - 00:00:00 CST