PL/SQL FOR loop vs. sqlldr performance.

From: Morten <morten_at_kikobu.com>
Date: Thu, 07 Feb 2002 14:19:12 +0100
Message-ID: <3C627ED0.8010302_at_kikobu.com>


[Quoted] Hi. I have a lot of data on the form

n,'foo','bar'
n+1,'foo','bar'
.
.

n+x,'foo','bar' --x large, > 2000
n+x+1,'bee','bop'
.
.

A total of 300k rows with this pattern. For each row, I need to call a PL/SQL function to get a mapped ID, so I cannot use direct mode with the loader. My question is, which would be faster, loading all lines through the loader, or use an abbreviated format, eg.

n,n+x,'foo','bar'
n+x+1,n+x+y,'bee','bop'

Load that into a fake table with an INSTEAD OF INSERT trigger, that does:

FOR n IN :new.low .. :new.high LOOP

   INSERT INTO table (id, a, b) VALUES (getMappedID($n), :new.a, :new.b); END LOOP; Any insight appreciated,

Morten Received on Thu Feb 07 2002 - 14:19:12 CET

Original text of this message