]]>By: sulehttps://jonathanlewis.wordpress.com/2014/01/08/cr-trivia/#comment-78252
Tue, 06 Jan 2015 10:38:09 +0000http://jonathanlewis.wordpress.com/?p=12133#comment-78252how can we check the number of formatted blocks?
]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2014/01/08/cr-trivia/#comment-62201
Wed, 08 Jan 2014 09:19:00 +0000http://jonathanlewis.wordpress.com/?p=12133#comment-62201Arguably there’s a slightly better option for the stated requirement though (as in many of these edge cases) the benefit is minimal, and addressing the wrong problem.

The best implementation for the requirement stated in that quiz is probably a single table hash cluster with a size of “one block”, the “hash is” option identifying the primary key as the hash key, and a hashkeys values greater than the number of rows. You’d still have to include the primary key constraint, but Oracle wouldn’t use the index to access the table, instead you’d get the data at one row per block, with a direct hash access:

The benefit is that concurrent access to different keys would use be using different buffers and (more importantly) “cache buffers chains” latches; the drawback is that you have one buffer used per key value, rather than one buffer for the entire set of values.

(There was a time when the hash table approach was a clear winner because the get was a “consistent get – examination”, but Oracle’s been able to do those on index root blocks for a very long time now.)

The approach is still the winner if the size of the IOT grows beyond the single root/leaf block for the index, of course – but then you’ve probably got a lot more rows (hence blocks for the hash table) and you MIGHT end up having to worry about hash collisions if your ID values are not sequential or are not numeric.

At the PL/SQL Challenge quiz on Database Designvery recently we had a quiz on why IOT’s or even indexed tables may be a better choice for very small lookup tables, than thinking a full table scan of a one-block table will be good:

There was a bit of discussion among players afterwards whether the full table scan sometimes could be best option. I would guess from your post here that the best you can get is if you do the “alter table move” to bring a full table scan down to three gets. (Or possible achieve the same effect by setting some parameters at table creation to force just one block to be formatted?) And even then (if I compare the tests shown in the quiz) and IOT for the lookup table can use just a single get?

Anyway, I just learned that it might not always be a good idea to assume that full table scan of a very small lookup table will be the minimum number of gets possible ;-)