One of the problems of building models of Oracle activity is that it’s easy to build the wrong model. One of the commonest issues appears with repetitive actions – how do you write code that repeats a simple action many times in a row. Writing a simple PL/SQL loop is often an adequate approach, but there are cases where a PL/SQL loop behaves very differently from a long list of individual SQL statements – which is why I’ve occasionally used a very simple-minded approach to avoid that particular trap.
If you checked the directory of my database work area, you’d find an interesting pattern of names made by five (subsequently grown to 7) very short scripts:
q:> dir start*.sql Volume in drive Q has no label. Volume Serial Number is A8BA-79AA Directory of q:\ 28/03/2011 08:56 350 start_1.sql 13/01/2005 13:38 110 start_10.sql 13/01/2005 13:39 120 start_100.sql 13/01/2005 13:39 130 start_1000.sql 13/01/2005 13:40 140 start_10000.sql 11/02/2012 01:01 190 start_100000.sql 11/02/2012 01:01 202 start_1000000.sql 7 File(s) 930 bytes 0 Dir(s) 8,238,260,224 bytes free
The script start_10000.sql contains just 10 lines:
@ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000
and you won’t be surprised to find that the script start_1000.sql also contains just 10 lines:
@ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100
and so on down the chain, until the script start_1.sql contains the piece of code that I actually want to run many times.
Obviously there are cases where you need a more subtle framework – but it’s very convenient to be able to write a small script holding the piece of code that you want to keep repeating and then run it 10,000 times with just a single call to: @start_10000.sql.
I wonder why I did not think about this approach before :)
thanks for saving big loads of time, this is really helpful
Comment by coskan — March 30, 2011 @ 10:57 am GMT Mar 30,2011 |
[…] cycle as a completely independant SQL script using the “start_1000.sql” script from my previous note […]
Pingback by ASSM wreck « Oracle Scratchpad — March 30, 2011 @ 9:06 pm GMT Mar 30,2011 |
Thanks Jonathan for such a brilliant idea to run some piece of code again and again.
Cheers!!!
Jagdeep Sangwan
Comment by jagdeepsangwan — January 14, 2014 @ 11:59 am GMT Jan 14,2014 |
[…] comparison here’s the effect of the same data load when operated as 10,000 separate insert statements called from […]
Pingback by ASSM Help | Oracle Scratchpad — February 1, 2017 @ 10:16 am GMT Feb 1,2017 |
[…] mechanism of the script start_1000 is something I published a few years ago, and essentially it executes a script called start_1.sql 1,000 times which, for this test, contains […]
Pingback by Histogram Threat | Oracle Scratchpad — January 30, 2018 @ 8:07 am GMT Jan 30,2018 |