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.
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:
- 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.
- 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:
- The development database (Oracle default values) had:
optimizer_index_caching = 95
optimizer_index_cost_adj = 10
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…