Oracle Scratchpad

May 11, 2010

Philosophy – 10

Filed under: Philosophy — Jonathan Lewis @ 6:56 pm BST May 11,2010

The most significant question to ask when thinking about adding a new index:

“Will the index eliminate significantly more work than it introduces (at the moments when it really matters) ?”

A few examples of “moments that matter”:

  • Bulk housekeeping
  • Highly concurrent OLTP activity
  • Frequent high-precision reporting
  • Acceptance testing for side effects

[The Philosophy Series]


  1. Hi Jonathan – Why don’t you put up a link on the right menu for all the philosophy items? That way I will not have to send people individual links to your useful philosophical postings!

    Comment by Nitin — May 12, 2010 @ 7:15 am BST May 12,2010 | Reply

  2. Jonathan,

    I would have asked the question with an extra word which, I believe, have a great importance here

    “Will this type of index eliminate significantly more work than it introduces (at the moments when it really matters)?”

    I think (but I am not absolutely sure) that I read this in “Practical Oracle 8i” when it comes to evaluate the side effect of creating a new index

    “Remember that you insert once and you select several times”

    According to my little knowledge of Oracle Data base, I think that a b-tree index is the type of index that has a great chance to be the number one in generating fewer side effects than other type of indexes when not suitably designed would do. Of course there is the overhead of not reused space when the index is direct path inserted and there is a daily delete of this index.
    I am thinking of the bitmap indexes and the severe deadlocks consequences they will generated in a highly concurrent OLTP activity.
    I am thinking of a locally partitioned index of the type (pk_id, dat) where dat is the partition key and queries like
    select * from t1 where id = pk_id?

    Where there will be N index range scans (where N = number of local index partitions) instead of 1 index range scan if the type of index has been designed as global

    and so on …



    Comment by Mohamed Houri — May 12, 2010 @ 7:27 am BST May 12,2010 | Reply

  3. I’ve just had an uncomfortable moment of clarity in which I realise that I judge the quality of people’s writings by the extent to which they mirror my previous experiences. Despite what that says about my character flaws it does make this statement of philosophy — “… at the moments when it really matters” — a little gem indeed.

    When considering pros and cons of a new index (or partitioning shceme, or PCTFREE setting etc) it’s absolutely not enough to say that “it will slow 30,000 inserts per day by 0.01 seconds each” and “speed two selects per day by 35 seconds each” and just net the resultant times. Is it worth sacrificing 300 seconds on inserts and for the gain of 70 seconds on the selects? The arithmetic (300 seconds lost vs 70 seconds gained) suggests “no”, but arithmetic does not take into account whether the 0.01 seconds per insert really is a problem or not, and whether 70 seconds is a significant benefit.

    I’m pretty sure that given a mathematical model that encompasses queueing theory, SLA’s, performance costing etc it would be possible to truly net out the costs and benefits in an objective manner, possibly in terms of the probability of breaching various SLA’s. Maybe in Oracle 14s (although I’m pretty sure it would be a pricey option).

    Maybe it’s an interesting mirror to hold up to your own organisation to see which of the following levels they are at:

    1: “Indexes are great! Let’s index everything!”
    2: “Indexes make some things slower. Do we really need them?”
    3: “Additon of an index will make this thing faster, but this other thing slower. I can’t decide!”
    4: “Additon of an index will make this thing faster by X, but this other thing slower by Y. Therefore we should …”
    5: “Additon of an index will make this thing faster by X, but this other thing slower by Y. How do these net out as solutions to or causes of problems?”
    6: etc…

    I think I’ve worked at all of these, except 6.

    Comment by David Aldridge — May 12, 2010 @ 7:38 am BST May 12,2010 | Reply

  4. Deeply agree. Most time,when I add a new index,I will ask myself “does the new index can get more to deserve the cost it introduced”.

    Comment by jametong — May 12, 2010 @ 9:53 am BST May 12,2010 | Reply

  5. Hi Jonathan

    A warning I give people is to be very careful when you create a new index in a database, make sure it really does have some real benefit. Because a philosophy I have on indexes is that it’s relative easy to add a new index to database but it can be really really difficult to drop an index “safely”, in the knowledge doing so won’t make break something, somewhere, sometime.

    Comment by Richard Foote — May 13, 2010 @ 10:02 pm BST May 13,2010 | Reply

    • Richard,

      That’s what makes the “inivisible index” feature of 11g so useful – although that still has limitations, and still leaves you open to problems with the “foreign key locking” problem.

      Comment by Jonathan Lewis — May 15, 2010 @ 10:49 am BST May 15,2010 | Reply

  6. I was pondering this recently when I was modifying a new report after some vague user feedback. There are numerous dates that propagate through some tables, and of course they want to filter by date ranges. Naturally, some of these are indexed and some not, and these tables get joined. So there are obvious unnecessary full scan situations, which weren’t all so obvious until I started run testing. Sometimes you just have to mumble about can’t do it for technical reasons when people want things every which way for no determinate reason. Let ’em play with the results in a spreadsheet. This hit all four examples.

    ++ to Richard’s warning, too. “How to figure out which indices are used” seems to be a modern myth attractor.

    Comment by joel garry — May 14, 2010 @ 6:27 pm BST May 14,2010 | Reply

RSS feed for comments on this post.

Comments and related questions are welcome.

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

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

Google+ photo

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


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by