RE: INDEX - row selectivity Vs block selectivity.
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <sjaffarhussain@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Dec 2004 12:18:45 -0500
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:
a) 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.
b) 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jaffar_DBA
Sent: Monday, December 13, 2004 10:19 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
