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