RE: INDEX - row selectivity Vs block selectivity.

If I recall correctly, Cary's was primarily an explanation of why it is not even theoretically plausible to have a general thumbrule of the form

Use an index if it selects less than x% of the rows

(Since Oracle reads blocks, not rows, predictions of how many blocks you will read based on row selectivity are impossible except for special conditions:

1. It selects all the rows (you're going to read all the blocks - silly degenerate case in which you'd not want to use the index unless it contained all the columns you needed or you needed the sorted result very badly and could prevent hash resolutions of the result set.
2. Rows tend to be one per block or one per many blocks (very long rows) - for example if every row takes two blocks and you know which columns you are reading you might be able to predict that row selectivity divided by 2 is the block selectivity. These are unusual cases and the number of sub examples is on the order of infinite.
3. Probably a bunch of cases I don't know about or which don't come to mind at the moment.
4. If it is a unique index and you're looking for one row.
5. Drumroll: the one that is frequently important to real live installations: When the physical order of the rows in blocks is well corellated with the key order of the index.)

Now, what do you do about it?

1. Don't rely on a general thumbrule to make decisions about using an index. With appropriate statistics the CBO will probably do much better (and if it doesn't please document it and submit it so Oracle will know they have something to improve.
2. Don't worry about it unless:
3. You have a real process that is annoying people already, in which case read Cary's book or send a bunch of money to me, Mogens, or the like and we'll fix it for you.
4. You are implementing a packaged application where the history of many users shows that it is important and valuable to maintain physical order now in order to avoid future problems.

Now, and maybe more to the point of your actual question:

The way to use this method to evaluate whether to use an index or not is to run the query with and without the index and see which one is either faster or less consumptive of a resource you expect to consume at near capacity in full production (the only reason not to go with the fastest one that occurs to me.) The relevant statistics other than elapsed time might include cpu, lio, and pio, but if your production system will have lots of head room on any of those you shouldn't worry about it (unless you're trying to figure a way to squeeze into a smaller box, or your guesses about future capacity and growth are uncertain.) Remember that selectivity may vary by the predicate values, especially when the table was populated from occasional batches where some of the batches were ordered with respect to the index in questions and other batches were in some other or no particular order. The relative selectivity of an index with respect to rows and blocks can vary wildly as the contents of the table change and for predicate values. A well informed CBO should make the correct choices for you, as long as you don't do something to prevent it from using the index when it is good to use the index. If you believe the wrong choice is being made, then you trace an execution with and without the index for truth, and do a Wolfgang on the query to find out why the CBO is making the choice it is making.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jaffar_DBA Sent: Monday, December 13, 2004 10:19 AM To: oracle-l_at_freelists.org
Subject: INDEX - row selectivity Vs block selectivity.

Hello list,

I was reading an excellent paper written by Mr. Carry Millsap about when to use indexes and I was very much interested to know more about block selectivity. Could any one tell me more about how can we estimate block selectivity for the index. It was there in the paper, but, still I am confused. How can I used this method to evaluate to use an index or not. At the beginning, table may contain little data (few data blocks), later on, it could grow heavily. Then, do we have to recalculate the formula again to make sure to use an index or not?

Thanking you in advance.

--

Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 13 2004 - 11:17:01 CST