One of the most common questions you'll have when trying to tune SQL queries is:
Why the @#!% *&@# *$%! is the database not using my index?! As far as you're concerned there's
a perfectly good index available. The query returns few rows. All the columns
in the index are in your where clause. And there's no functions on them. But the
database is decided to go with a full table scan instead! Why?
In this episode we'll investigate one of the most important but also most
overlooked factors behind this decision: the clustering factor.
Let's start with a
quick recap of how the database finds rows using an index or a full table scan.
With a full table scan the database will pick up all the blocks in the table
finding rows matching your search criteria, keeping those that do and
discarding those that don't. With an index range scan on the other hand the
database first goes to the index to find entries matching your search. It will
then only access those blocks containing rows matching your search criteria. How
many blocks the database accesses when using an index depends on where those
rows are physically located in the table. For example I've got my table here with
five blocks in it, and when I'm doing a full table scan I'm always going to read
and pick up all five of these blocks. But when using an index there are two
possible extremes. All the rows I want might be located in just one block. So I
can get everything in one IO. Or there might be one row I want from each
block. Meaning I need to pick each of the blocks once to get the information, giving
to five IOs, the same amount of work as the full table scan. So the amount of
work we do with an index varies depending on where those rows are
physically located. What happens now if I've got a query that is finding 10 rows
from the table? Well again there's two possible extremes. Everything I
want might be located in just one block. So I can get 10 rows with one IO. Or
there might be two rows I want from each block. And I might have to go through the
table once picking up each block and getting just one row from each. Then I
have to go through the table again to get the second row from each block. So
here to get the ten rows the amount of work I do varies from less than half the
work of a full table scan to more than double. So how does the database know how
many blocks it's going to access using the index before running the query?
Well the short answer is it doesn't! So it estimates. Via the clustering factor.
So what is this clustering factor and how does it help? The clustering factor
is a counter that the database calculates when gathering statistics on
a table. For each index it walks down the entries and for each entry asks: is this
in the same block as the previous entry? If the answer is no it increments the
counter. If it's the same then it leaves it untouched. So we end up with a value
which has a lower bound of the number of blocks in the table and an upper bound
of the number of rows in a table. So the higher the clustering factor is the more
scattered throughout the table the rows are relative to the order in the index.
And the less effective the index is likely to be. Now at this point you may
be a bit skeptical and say "does it really make that much difference?" Let's
run a quick demo to find out. I have a table here which stores 10,000 rows and
these are stored in just over a hundred blocks. There are currently three indexes
on this table. On the primary key column, and this index is perfectly clustered
because we can see its value is less than the number of blocks; an index on
the weight column which is almost perfectly unclustered because it's
clustering factor is close to the number of rows; and an index on the insert
date column which is somewhere between the two. The weight values are evenly spread
between 1 and 1000. I'm going to search for weights between 1 and 100. So this
should return about 10 percent of the rows from the table. We look at the
execution plan we find that the database does a full table scan to access it.
Which is what we would expect because that weight index is very poorly
clustered. And we get a thousand rows - just over - so about 10% to the table. And
it took a 130 buffer gets to do this. Let's see what happens if we now
force the database to use the index. We get the execution plan and we can see
that the database does indeed use the index returns the same number of rows.
But now the number of gets - the amount of work or I/O it does - has jumped
considerably to nearly a thousand. As there's just over a hundred blocks in the table
we must have accessed each one nearly 10 times. So the database was right to use
the full table scan in this scenario. A quick note. I used an index hint in the
previous query. This sounds like I'm giving the optimizer a suggestion:
"Optimizer; if you wouldn't mind. would you perhaps consider, maybe, using this index?"
I'm not. I'm giving the optimizer a directive. An order. "Optimizer: you MUST
use this index - if it's possible to do so." This is useful for demos and training
purposes. But in real production SQL it can mean that the optimizer is unable
to find the best plan. Particularly as your data changes. So you should avoid
using hints in your production code. I repeat: avoid hints in your application
code! Returning to the demo I'm now going to search the bricks table for primary key
values between 1 and 1000. Again, about 10% of the rows in the table. The index
on this column is perfectly clustered. So we hope that the database will use the
index. And when we look at the execution plan we find that - indeed - this is what it
does. It gets a thousand rows in just over 30 gets.
Roughly a quarter of the work of the full table scan. So this is the correct
choice. In fact we can get as many as 20% of the
rows from the table using this perfectly clustered primary key index and when we
look at the execution plan we get 20% of the rows in just under 70 gets. Roughly
half the work of the full table scan. So how effective an index is really depends
on where those rows off physically stored in the table relative to their
position in the index. The more closely aligned they are the more effective that
index will be. So as the clustering factor increases the less effective the
index is likely to be and the more likely the database is to use a table
scan. This is obvious at the extremes. But what about our partially clustered
indexes such as the insert date? This is particularly interesting because the
values for it will always increase. So new entries are going to go on the
right-hand edge of the index. And as we add new rows to the table assuming it's
a standard heap table they will typically go at the end of the table. But
they're not always going to go in exactly the last block. They're likely to
be spread over the last couple of blocks. So if we're searching for recent rows
all the information is going to be located in just a couple of locations. So
will the database use this index or not? This query searches the bricks table for
rows inserted in February. Looking at the execution plan we can
find then it returns 600 rows - about 6 to 7% of the total in the table. And it does
this using a full table scan. If we now see what happens when we force the
database to use an index we see that it fetches the same number of rows. But it
does so using 70 gets! About half the work of the full table scan. So the
database got it wrong here. Why is this and what can we do about it? The problem
is the clustering factor is pessimistic. Luckily Oracle Database 12c
added a workaround. This included the preference TABLE_CACHED_BLOCKS. This
changes how the clustering factor calculation works. Instead of asking is
the current block for the current index entry the same as the previous index
entry, it asks: "has the current block appeared in the past N TABLE_CACHED_BLOCKS
index entries?" So the counter only increases if we haven't seen the current
block recently. This defaults to 1. So what happens if we increase it? If we
look at the TABLE_CACHED_BLOCKS preference for the bricks table we find
is currently at the default of one. If we increase this up to 16 and regather
stats on the table, when we look at the clustering factor for this index we see
it is now dropped down to the same value as the primary key index. When we now
rerun our query searching for bricks added in February, we find that the
database chooses the index as we hoped it would. One of the first things we're
taught when learning how to tune SQL queries is: the database should use an
index if the query returns few rows. But I contend this is bad advice. OK sure, if
you know your query is going to return one or two rows at most, then yes, you
basically always want to use an index. But what if you're going to get 10 rows
from the table? A hundred? A thousand? Knowing this doesn't help you understand
whether an index will be effective or not. What you need to look at instead is
how many blocks from the table will your query access using that index. And how
does that compare to the total number of blocks in the table. Provided the index
enables you to read fewer blocks from the table than there are in total then
it's the right way to go. As soon as that index means you have to reread many
blocks, then a full table scan is the right method. Sadly there is no way to
know this for sure other than to run the query and find out! The
clustering factor does give an indication of how likely the index is to
be effective, with higher values meaning it's going to be less effective. But by
default this is pessimistic. So setting the TABLE_CACHED_BLOCKS to a small value
such as 10 enables the database come up with better estimates for mostly
clustered data. Allowing you to get good performance from your SQL statements
without having to rely on clumsy hacks such as hints! Thanks for watching!
Still got questions about indexes? Ask us in the comments. And subscribe for more
SQL tips