Re: query performance question

From: Karl E. Jørgensen <kjorg_at_msn*DOT*com>
Date: 1998/01/22
Message-ID: <uhese8xJ9GA.185@upnetnews04>#1/1

You forgot about the PL/SQL approach e.g.

declare

    Cursor Trans_Curs is

        Select ....
        From Trans;

begin

    for Trans_Rec in Trans_Curs
    loop

        lookup-lots-of-codes-left-right-and-center
        Insert into Big (...) values (...)

    end loop;
    commit;
end;

If you're working over SQL*Net, you may consider modifying the cursor to join in a couple of your code reference tables. This will reduce the number of times you need to talk to the RDBMS.

Depending on the size of your rollback segments, you should consider e.g. committing every 1000 rows or so. Which may give you a problem if other people are inserting/updating TRANS, possibilities of restart, locking strategy issues and the like.

If you only need to do this once, don't spend too much time writing an elaborate solution - unless of course that the whole thing has to be done during "off-line" hours and be ready before people start using your application again. If the table contains currency amounts, you may have to please please some auditors too.

Whatever solution you choose, you're probably better off having full table scans on TRANS, and adequate indexing on your code reference tables.

--
Hope this helps

Karl
Oracle Certified DBA

Eric Spear wrote in message <34C64CFF.F72A8BC6_at_deans.umd.edu>...


>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 Thu Jan 22 1998 - 00:00:00 CST