Home » SQL & PL/SQL » SQL & PL/SQL » Records/Bulk Collects Problem
Records/Bulk Collects Problem [message #190658] Thu, 31 August 2006 10:06 Go to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hi

My first post here Razz

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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Cool
Cheers

[Updated on: Fri, 01 September 2006 03:52]

Report message to a moderator

Re: Records/Bulk Collects Problem [message #190817 is a reply to message #190730] Fri, 01 September 2006 06:54 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Got it working
1.First i do the update
BEGIN
FORALL i IN 1 .. p_rec.id.COUNT SAVE EXCEPTIONS
UPDATE tablex
SET money = money + p_rec.money (i)
WHERE ......
EXCEPTION
WHEN dml_errors
THEN
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;

END;
Then do the insert
BEGIN
-- if key violation just continue
FORALL i IN 1 .. p_rec.id.COUNT SAVE EXCEPTIONS
INSERT INTO tablex
(id,moneyVALUES (p_rec.id(i),p_rec.money (i) );

EXCEPTION
WHEN dml_errors
THEN
same handling as above
END;

So it takes about 7min to process 120 000 records with logic then upd/ins into a table with 160 000 rows.

The drawback here is that i have to loop thrue all the update rows once more when im doing the insert :/
Re: Records/Bulk Collects Problem [message #190893 is a reply to message #190817] Fri, 01 September 2006 22:35 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Records/Bulk Collects Problem [message #190974 is a reply to message #190893] Sun, 03 September 2006 12:33 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Ok , thnx ill have a go with that and see if i get any improvments

Cheers
Previous Topic: Getting erroneous record from Bulk insert
Next Topic: Encountered the symbol "FETCH" when expecting one of the following: begin function pac
Goto Forum:
  


Current Time: Sat Dec 10 05:11:06 CST 2016

Total time taken to generate the page: 0.21551 seconds