header image
« gzip madness
Data Recovery - Your data is in Danger »
[ # ] Why not the primary key?
April 23rd, 2008 under Computers, Devel, rengolin

Recently I came to an amusing situation with Oracle (again) where the primary key was not used when explicitly requested…

The query was:

select name from table where table_id = 1;

Of course, table_id was the primary key. Astonishingly Oracle performed a FULL_TABLE_SCAN.

WTF?

When things like that happen you think there’s something quite wrong with the database, so I decided to ask the DBAs what was going on. The answer was something like:

“It may happen if Oracle decides to. Even though you have created an index, there is no guaranteed they will be used for all queries and the optimizer will decide which one is the best path”.

Seriously, if Oracle decides NOT to use the primary key for that query, there is something really wrong with the whole thing. I couldn’t think of a situation where that might be even close to valid! A friend who knows Oracle much better than me pictured two extreme cases why it could happen:

  1. There are just very few records in that table -> table data = 1 data block, reading the index root block (1 data block) and then accessing the one table data block is certainly more expensive than just read that one table data block.
  2. The index is in a Tablespace with different block size which resides on very slow disks. The buffer cache for the non-default block size is hugely under-sized. So the cost to read the index and the table data might be higher than just reading the table data. It’s a bit unrealistic, but I’ve witnessed stupid things like this.

Let’s face it, the first scenario is just too extreme to be true. If you have only one data block on your table you better use email rather than databases. And the second scenario, why would anyone put indexes on a much slower disk? Also, if the index is too big the data will be proportionally big too, so there is no gain in doing a full table scan anyway.

Later on he found out what the problem was by hacking into the configuration parameters:

  • The production database (working fine) had:

    • optimizer_index_caching = 95
    optimizer_index_cost_adj = 10

  • The development database (Oracle default values) had:

    • optimizer_index_caching = 0
    optimizer_index_cost_adj = 100

I don’t quite understand what they really mean to Oracle but index_caching = 0 seems a bit too radical for me to make it default.

At the end (and after more iterations than I’d like) it was fixed but what really pissed me off was to get the pre-formatted answer that “Oracle knows better which path to take” without even look on how stupid was that decision in the first place. This extreme confidence in Oracle drives me nuts…

Read the Comments

[ # 162 ] Comment from Rodrigo Castro [April 23, 2008, 7:21 PM]

Actually you can get this kind of answer all the time, because people blindly believe that some application/companies have always some good reason to do their products in a given way. From my experience (and probably from yours too), we know that sometimes things do not have any reason to be done that way or maybe the reason is completely unrelated to what one would expect, so being skeptical (and never religious about products/companies) sounds as the best way to me.

[ # 163 ] Comment from Bide [April 28, 2008, 8:39 AM]

> “Let’s face it, the first scenario is just too extreme to be true”

I don’t think scenario one is that uncommon, think of a small look-up/CV table. In a Tablespace with 8k block size and the avg row length of a 100 bytes you could fit in about 80 records in one data block.

> “I don’t quite understand what they really mean to Oracle but index_caching = 0 seems a bit too radical for me to make it default.”

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1137.htm#1021404
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1138.htm#1021444
Of course there is much more that factors in for the Optimizer’s decision, most importantly object statistics.
I agree that the default values for these two parameters are badly chosen (I’d say for 99% of all Oracle shops). But why don’t they gather system statistics?

[ # 164 ] Comment from rengolin [April 28, 2008, 1:23 PM]

> In a Tablespace with 8k block size and the avg row length of a 100 bytes you could fit in about 80 records in one data block.

If you have a few rows it shouldn’t matter anyway (both index and data are small enough to fit in a few cache pages) and if you have millions of rows, dividing the scan by 80 won’t make much difference over a binary search (ie. millions/80 is still *much* bigger than log(millions) ).

My table had 74 millions of rows and only 74 had the ID I wanted (nice coincidence). ;)
> But why don’t they gather system statistics?

Good question…

Write a comment