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.

]]>By: joel garryhttps://jonathanlewis.wordpress.com/2010/05/11/philosophy-10/#comment-36211
Fri, 14 May 2010 18:27:27 +0000http://jonathanlewis.wordpress.com/?p=3640#comment-36211I 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.

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.

Done – it’s a link which lists all the posts that I’ve put into a “philosophy” category.

]]>By: jametonghttps://jonathanlewis.wordpress.com/2010/05/11/philosophy-10/#comment-36202
Wed, 12 May 2010 09:53:17 +0000http://jonathanlewis.wordpress.com/?p=3640#comment-36202Deeply 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”.
]]>By: David Aldridgehttps://jonathanlewis.wordpress.com/2010/05/11/philosophy-10/#comment-36201
Wed, 12 May 2010 07:38:03 +0000http://jonathanlewis.wordpress.com/?p=3640#comment-36201I’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 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 …

Regards

Mohamed

]]>By: Nitinhttps://jonathanlewis.wordpress.com/2010/05/11/philosophy-10/#comment-36199
Wed, 12 May 2010 07:15:36 +0000http://jonathanlewis.wordpress.com/?p=3640#comment-36199Hi 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!
]]>