Oracle Scratchpad

September 12, 2009


Filed under: Indexing — Jonathan Lewis @ 5:39 pm GMT Sep 12,2009

At present I have 2,130 SQL scripts on my laptop, of which about 130 are tools that I use for trouble-shooting. The rest are models of problems I have come across at client sites, demonstrations of bugs that I’ve sent in to Oracle, or test cases that I’ve used to investigate the way some feature of Oracle works.

Of the 2,000 scripts that aren’t aids to trouble-shooting, there are 123 scripts with the word “index” in their name – in second place are the scripts with “pt” (for partitioning) in their name with 72 scripts.

The numbers may not give you the same shock that they gave me, so you may have to take my word for it that if 6% of my scripts are about indexing that’s a pretty stark indication of the degree to which indexes mess up people and people mess up indexes.

It doesn’t quite make it to the “Philosophy” list, but it’s almost a truism for any badly performing system: “You need to review your indexing strategy.”

Update Jan 2016

I’ve just rediscovered this note and thought I’d check the numbers.  It’s currently: 3,958 scripts of which 322 are about indexing and 240 are about partitioning.  (There’s a little overlap, with about 20 scripts that are explicitly named for partitioned indexes.)



  1. or your own personal indexing strategy of your script names is so bad that you duplicate test cases ;o) – but then I guess that still shows indexing strategies can cause issues via the ‘meta’ area of things ;o)
    Just a joke that – I have ‘loadsa’ ‘spect’ for you JL ;o)

    Comment by kent — September 13, 2009 @ 8:53 am GMT Sep 13,2009 | Reply

  2. Oh, tell me about it :) I often forget that I have written a script and start writing it again – but instead of long script names I use as short ones as possible (for the everyday work/troubleshooting ones), so when I try to save my script I have a “collision” with existing one and when I look into it I see “oh I’ve just wasted x minutes”). By now I am smarter, before writing anything, I ask myself what would I name it and then look for that. Also a grep for the view names I would use helps too.

    Comment by Tanel Poder — September 14, 2009 @ 1:35 am GMT Sep 14,2009 | Reply

    • Short names ? Never !

      The worst thing about UNIX is that every single typing mistake is a UNIX command you didn’t know about. I’ve never lost at Scrabble because I can stick any high-scoring letters on any high-scoring squares and claim the result is a well-known UNIX command.

      Comment by Jonathan Lewis — September 14, 2009 @ 7:01 am GMT Sep 14,2009 | Reply

  3. our instance names for Dev Test UAT and Prod are different only by D T U & P. I believe this is a serious mistake for the same reason.

    Comment by Mark Brady — September 14, 2009 @ 4:12 pm GMT Sep 14,2009 | Reply

  4. UNIX: I always, always, make sure that the deadlier commands are aliased to reference prompted commands. I do this as I still shudder whenever I remember issuing rm -fr * in an Oracle Home, thinking I was in my own /tmp/blah directory! Luckily, it was a RAC setup so I was able to copy and rename the password file, etc. across to the afflicted node, but lesson learned!

    It’s WELL worth the effort of doing this sort of aliasing.

    Comment by Nigel — September 14, 2009 @ 5:10 pm GMT Sep 14,2009 | 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: Logo

You are commenting using your 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

Blog at