Oracle Scratchpad

March 29, 2011

Repetition

Filed under: Oracle,Performance,Troubleshooting — Jonathan Lewis @ 9:42 pm GMT Mar 29,2011

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. It’s often enough to write a simple pl/sql loop 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 intereseting pattern of names made by five 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
               5 File(s)            850 bytes
               0 Dir(s)  82,803,621,888 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 actully 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.

3 Comments »

  1. 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 | Reply

  2. […] 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 | Reply

  3. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers