Skip to Main Content
  • Questions

  • Bulk bind help with multiple keys and mutiple statements

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kenton.

Asked: March 10, 2008 - 8:39 pm UTC

Last updated: May 12, 2008 - 12:04 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hello

I am trying to gain some performance on a DW ETL process and would like to use Bulk Collect and/or FORALL.

I have spent quite some hours reading up on this here and on other sites, but I am totally lost.

What I'm trying to do is, using multiple keys from a cursor across a DB link, age off the old record in the DW and add the new.
(The cursor is actually across mutiple tables, but I assume this makes no difference for this example)
DECLARE
   CURSOR c1 IS SELECT key1, key2, value1 FROM table1@db1;
   asofdt     NUMBER := 20080103;
   v_key1     NUMBER;
   v_key2     NUMBER;
   v_value1   VARCHAR2 (10);
BEGIN
   OPEN c1;

   LOOP
      FETCH c1
       INTO v_key1, v_key2,v_value1;

      EXIT WHEN c1%NOTFOUND;

      UPDATE table2 t2
         SET t2.enddate = to_char(to_date(asofdt,'YYYYMMDD') - 1,'YYYYMMDD')--Age off open records one day prior
       WHERE t2.key1 = v_key1 AND t2.key2 = v_key2 AND t2.enddate = 99991231;

      INSERT INTO table2 (key1, key2, value1, startdate, enddate)
           VALUES (v_key1, v_key2, v_value1, asofdt, 99991231); -- Insert new records
   END LOOP;

   CLOSE c1;
END;

commit;

Do I use BULK COLLECT for the cursor or do I use FORALL or do I use both?

Thanks in advance
Kenton

and Tom said...



update t2 set enddate = to_char(....)
where endate = 99991231 -- oh, i hate that
and (key1,key2) in (select key1, key2 from table1@db1);


insert into tables (key1,key2,value1,startdate,enddate)
select key1,key2,value1, asofdate, 99991231 -- oh, i hate that
from table1@db1;


you cannot get bulkier than that!

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I'm sorry, but I've been brought in to support the mess. Not fix it.

Kenton, March 11, 2008 - 11:21 pm UTC

I could spend days posting the rubbish code, rubbish structure and rubbish data that this system has been left with.

In fact, I have found possibly the most convoluted and impossible to understand package ever. It even includes a function called QUOTE that appends quotes to the left and right of a text string. But I digress.

I am stuck with dates stored as numberic.
I am stuck with end dates as 99991231 (failing a rewrite of the whole system).
I am stuck with a system that is so bloated I want to just rip my hair out.

However, as the original developers have all been terminated, I am stuck with it. As it basically works, I am not authorised to change it (well the bits that do work anyway).

So, the fact that you hate it too is of no real interest to me. I am after constructive help.
Tom Kyte
March 12, 2008 - 5:25 pm UTC

not sure where you are going with this - you asked how to bulk up the above code.

I responded, the code you gave needs nothing other than those two statements.


If there were other relevant facts you did not supply - well, all I can say is "sorry". You asked a question, I answered in the context of the question. Don't know what you expected.

As for your comment about "that you hate it too is of no real interest to me." baffles me, I'm at a complete loss as to why you said it - seriously

You asked how to bulk something up.

I answered.

I did nothing else.

So, your comment are of no interest to me either.


If this is true:

.... As it basically works, I am not authorised to change it (well the bits that
do work anyway).
...

go home, you obviously cannot do bulk operations using bulk collect and forall insert - since you are not authorized to change it. (and frankly, if you don't know how to bulk collect and forall insert - and you are changing bad code in the first place - you best leave it alone, you'll really break it as you are not experienced with the technique - not a good time to learn)


For some reason my posting's dissapeared

Kenton, March 12, 2008 - 12:13 am UTC

I could spend days posting the rubbish code, rubbish structure and rubbish data that this system
has been left with.

In fact, I have found possibly the most convoluted and impossible to understand package ever. It
even includes a function called QUOTE that appends quotes to the left and right of a text string.
But I digress.

I am stuck with dates stored as numberic.
I am stuck with end dates as 99991231 (failing a rewrite of the whole system).
I am stuck with a system that is so bloated I want to just rip my hair out.

However, as the original developers have all been terminated, I am stuck with it. As it basically
works, I am not authorised to change it (well the bits that do work anyway).

So, the fact that you hate it too is of no real interest to me. I am after constructive help.

Got your answer

Andy, March 12, 2008 - 8:35 am UTC

Kenton,

See Tom's response. He was not ignoring you; he gave you a two-line replacement for your 21 lines of PL/SQL, and his 2 lines will run a whole lot faster, too.

Cursors are slow in Oracle; use an UPDATE to update all records at once, and an INSERT to insert all records at once, instead of the cursor that you're trying to replace.

A reader, March 12, 2008 - 11:38 am UTC

Do it in simple SQLs if possible which is faster than bulk processing in loop

If loop is a must then do that in bulk processing

Got your answer

Duke Ganote, March 12, 2008 - 12:44 pm UTC

Kenton-- The code you've been stuck with is wrong on many levels. For example, it uses implicit cursors
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688
which are ugly and slow.

And since it has no error trapping requirements, it should be replaced with the two line SQLs Tom showed. (Nuts, even if error trapping were required, there are better ways than this to do it.)

As you find errors or unbearably slow processes, you replace them with simple SQL... and throughput spikes upward.

Mis-interpretation of your original response

Kenton, March 12, 2008 - 6:05 pm UTC

OK, sorry.

Everyone here (DBA, other developers etc) interpreted your original response as dismissive. No one could believe that such an authoratative site as your own would be so terse!

It sounded (to us) like you only made 3 comments
1) oh, I hate that
2) oh, I hate that
3) you cannot get bulkier than that! (read as you cannot code any more bloated than that)

So it was (interpeted as) 3 derrogatory statements that were no help at all. Thus the flabergasted response.

Now I understand that what you were saying was to reduce the PL/SQL to the 2 SQL statements.

Once again, I'm sorry for the mis-interpretation by all here. Perhaps you could remove my response and maybe add something like "Replace all of your code with:" to your original response so that no one else mis-interprets it.





Tom Kyte
March 13, 2008 - 8:32 am UTC

ahhh, the i hate it was in regards to that enddate

a) it is not a date - that is so sad

b) it is using a funky date in the future - that is so so so very sad, just messes the optimizer up completely


it was a side comment about the date - that could have been more clear. But, the code itself - that should have been obvious - an update and an insert, done, rewritten, bulked up. I don't see how that could have been overlooked (that the answer was - an answer)

sorry about the lack of clarity on the -- i hate that bit - that was just about the way the dates are (actually the way the dates are not). I hate that, and I see it sooooooooooo many times a day, I just make comments on it - forgetting that others see it as a "good idea", or don't see it as being an issue at all.

Original response

Duke Ganote, March 12, 2008 - 9:27 pm UTC

Kenton-- At least to me it was "obvious" that Tom was groaning over
endate = 99991231
which he has lamented elsewhere because it confuses the optimizer:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:229614022562#tom54428543366943

But admittedly I've been "hanging out at asktom" a while -- apparently since at least Feb 16, 2004
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14039738984108#15675746031368

enddate -- enforced "datification" of number

Duke Ganote, March 13, 2008 - 9:23 am UTC

I'm not immediately sure that it will help the optimizer, but Kenton could set function-based indexes on enddate, startdate, etc. to ensure valid dates. Something like this:

CREATE TABLE t ( startdate number, enddate number primary key );
CREATE INDEX t_startdate_is_date ON
t(to_date(
to_char(startdate
)
,'YYYYMMDD')
);
CREATE INDEX t_enddate_is_date ON
t(to_date(
to_char(enddate
)
,'YYYYMMDD')
);
INSERT INTO t VALUES ( null, 99991231 )
/
INSERT INTO t VALUES ( null, 20081332 )
/
*
ERROR at line 1:
ORA-01843: not a valid month

bulkier---good or bad:)

suresh, March 14, 2008 - 3:56 am UTC

Dear Tom,
Your response
'you cannot get bulkier than that' is probably another cause for confusion.
You probably meant a simple SQL is 'even better' than a 'bulk collect' (more efficient) code. But, bulkier means
'clumsy/difficult to manage' exact opposite of 'efficient'
so, your suggestion of single SQL code is not really 'bulkier' but better!

Dear Duke Ganote,
You probably meant 'explicit cursors' when you responded.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:689240000346704229

Kenton had coded with explicit cursors and not implicit cursors. Tom 'hates' explicit cursors not implicit!
Tom Kyte
March 15, 2008 - 9:34 am UTC

large bulk operations are good in this context. "bulk collect", "single BULK sql statement".

It is bulkier...

bulkier does not mean "clumsy/difficult to manage"

http://www.merriam-webster.com/dictionary/bulkier

it means "bigger, having great volume"


I do not 'hate' explicit cursors, I say "use explicit cursors when you have to - and you have to if you have to use ref cursors OR if you need to use bulk collect"

I say:

a) do it in sql if you can
b) do it in as little plsql as possible if you cannot do a)
....


that does not mean "i hate plsql", it means "do it this way, and if you cannot do it that way, then do it this other way"


Same with explicit/implicit cursors

a) use implicit cursors
b) if you cannot - because you need a feature/function not available with them, use explicit cursors


meaning - use the easier, less code, safer approach (implicit cursors) over explicit cursors whenever you can.

bulkier

Duke Ganote, March 14, 2008 - 11:54 am UTC

Suresh-- correct, I mistyped 'implicit' when I meant 'explicit' cursor.

But 'bulkier' is a comparative adjective of 'bulky'. A single SQL is does everything in one bulk, so it's bulkier than BULK COLLECT (which is intended for bulks of, say, 50-100 records).

Funky date in the future

A reader, March 19, 2008 - 8:44 am UTC

>> it is using a funky date in the future - that is so so so very sad, just messes the optimizer up completely...

Tom:

Agreed... date as a number is a bad idea. But can you elaborate on why the future date is a bad idea? With proper statistics gathered on this date, would the optimizer not know about the cardinality and data skew if present? It seems to me that histograms would fit the bill, but that may be an unnecessary step if something else would do.

For an enddate, are you instead recommending a NULL value?

Thanks.
Tom Kyte
March 24, 2008 - 9:45 am UTC

what about when there are lots of dates, more than 254 and histograms are imperfect? That last bucket is way whacked out.

what about when the optimizer doesn't have histograms - just high value, low value, number of distinct values - and it extrapolates.


Why not null? It is not like you cannot use an index or anything to find values "where enddate is <not> null", they are as retrievable via an index as any other data is.

How about very large tables?

Sarb, May 09, 2008 - 3:05 pm UTC

I have used your bulkier SQL as you suggested. However, I tried it with really large source data sets. Say 10 million+ rows. It sometimes ends up chocking on Undo tablespace. Does your recommendation apply to all situations? If yes, then what are the things to keep in mind and how to design the system so it does not break and runs optimally. If not, then what is the break even point, or how should one calcuate it for their system?

Looking forward to your insight, as always.
Tom Kyte
May 12, 2008 - 12:04 pm UTC

it would be my response that "your undo tablespace is not configured correctly for the amount of work you do, you need to allocate more space - it should never get "chocked up" unless the DBA has not done what they are supposed to.

How to 'calculate'? I use 'testing and measuring'
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.