| Records/Bulk Collects Problem [message #190658] |
Thu, 31 August 2006 10:06  |
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
|
Senior Member |
|
|
Hi
My first post here 
I need some advise concerning bulk and the forall process
Im joining 2 tables to get some data, which is supposed to update or insert a third table(cant write it in a singel statment, since I have to change the data quiet alot).
And Im not to keen on using temp tables either..
Everthing goes "fast" and smooth until the last bit....need to do an insert in the third table if it doesnt match the update clause.
--Have done some dummy code to make it clearer
--Declartion
TYPE parent_rec IS RECORD (
r1 DBMS_SQL.number_table,
r2 DBMS_SQL.varchar2_table)
p_rec parent_rec;
--select from tables which will be populate the parent_rec
CURSOR c
IS
SELECT a.id, u.money
FROM idtable a, moneytable u
WHERE ........
l_done BOOLEAN;
BEGIN
OPEN c;
--collect and modfie
LOOP
FETCH c
BULK COLLECT INTO p_rec.r1,p_rec.r2
l_done := c%NOTFOUND;
FOR i IN 1 .. p_rec.id.COUNT
LOOP
--modifie the data
END LOOP;
--Do the update
FORALL i IN 1 .. p_rec.id.COUNT
UPDATE tablex
SET money :=money+p_rec+money
--
-- Here I'll like something similar to this
--FOR i IN 1 .. p_rec.id.COUNT
--UPDATE tablex
--IF SQL%ROWCOUNT < 1 THEN
--INSERT INTO tablex
--But thats way to slow, compared to FORALL
--
EXIT WHEN (l_done);
END LOOP;
CLOSE c;
I processing about 120 000 rows(from the inital select), the table that is to be updated
contains about 180 000 rows.
Any help is appreciated, is there any other nice actions I can take
|
|
|
|
| Re: Records/Bulk Collects Problem [message #190717 is a reply to message #190658] |
Thu, 31 August 2006 23:34  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I know you don't like Temp Tables, but they would probably be faster in this case. But I'll work with you here and respect your preference.
First, I don't like your data structure. A record of collections is counter intuitive. Far better and more obvious is a collection of records.
TYPE c1_tab IS TABLE OF c1%rowtype;
c1_arr C1_TAB;
That's not your problem though, it's just a better style.
Now you can BULK COLLECT INTO c1_arr. But you should use the LIMIT clause to fetch only (say) 1000 rows at a time. This will use less memory and eliminate the possibility of the data structure paging to disk (sloooooow).
Now we're up to your main question, how to handle the UPDATE ELSE INSERT. Two methods come to mind:
1. You could create the Record and Nested Table types as physical database TYPEs rather than PL/SQL Types. This would allow you to cast the Nested Table using the TABLE() function, and use it in a MERGE statement.
This is not especially good style. If you want to use a collection in a SQL, far better to have built it as a GLOBAL TEMPORARY TABLE in the first place.
2. You could use the bulk exception handling option of FORALL. This has been demonstrated a few times in this forum. You would have to perform an INSERT inside the FORALL, and capture unique/PK violation in the exception clause. You could then pass those exceptions to another FORALL for the UPDATE.
If you are determined to use BULK COLLECT, I would go with the second method, although I suspect it could be made somewhat quicker (measurably, but not orders of magnitude) using a GTT.
Sorry I can't be more helpful with syntax. I've got a pre-schooler bouncing around me at the moment, so concentration and time are at a premium. Just do a search on the forum for BULK COLLECT EXCEPTION to get some syntax examples.
Ross Leishman
|
|
|
|
| Re: Records/Bulk Collects Problem [message #190730 is a reply to message #190658] |
Fri, 01 September 2006 01:06 |
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
|
Senior Member |
|
|
Hi
Thnx for the reply.
Im using the limit 1000 , just forgot to put it in the limit code in the "dummy" version.
1.The goal here is actually to improve some very very slow runnig code. Where the orginal record looks like
TYPE typ_rec IS RECORD(
id INTEGER,
lan CHAR(2),
loc CHAR(10),
projectcode descion.projectkod%TYPE,
projectl descion.projectl%TYPE,
classifie descion.classifie%TYPE,
a_type SMALLINT,
ekod SMALLINT,
days SMALLINT,
sum INTEGER,
pay_fromdate DATE,
pay_duedate DATE,
weeks SMALLINT,
date_dm DATE,
sqnr INT,
pdate DATE,
b_typ CHAR(1),
bus VARCHAR(10),
finance VARCHAR(10)
);
And the logic is
OPEN cursor;
LOOP
FETCH cursor INTO record;
EXIT WHEN c_rfvin%NOTFOUND;
Modifie the data, call a function which does an update or a insert.
close cursor
The update/insert func is gonna be called like 120 000 times
Slow so Slow
Back to the orginal discussion
1.It's not possible to %rowtype any particulary table,thou but cursor%rowtype would probably work. My record declartion might be ugly is hell, but hey ' it looks more "describing" in that way.
(or is there any other drawback ?)
2.Gonna have a look at GTT anyway(must be a drawback thou, having no stats ?), but im very curious to get my orignal thought working as well.
3. Capture unique/PK violation looks very intresting, gonna try that one. Im just wondering how to "capture" all the records(and all the modfied data within it)and pass it on to a FORALL Update.
Since I cant catch the failing array(i)data.To bad you cant use SAVE EXCEPTIONS and pass the data as well error index and error code.
4. Browsed the forum, havent seen any examples of this in particulary, so it's probably not the "normal" way to do this.
Too bad...it's very fast
//
Any Advice/Help appreciated
Have a Nice Weekend in the PL/SQL swamp 
Cheers
[Updated on: Fri, 01 September 2006 03:52] Report message to a moderator |
|
|
|
|
|
| Re: Records/Bulk Collects Problem [message #190893 is a reply to message #190817] |
Fri, 01 September 2006 22:35 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I was thinking you could use the SQL%BULK_EXCEPTIONS array to populate a second nested table which you would then pass on to the next FORALL.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
