Best Method To Select One Row From Small Table – Solution (Revolution 1)

OK, time for some answers, although of course regular readers of this blog will already know the answer 🙂

When selecting one row from the small table as in the quiz in my previous post, the correct order is as follows:

1) PK access of an Index Organized Table.

This option only requires just the 1 consistent get (as the IOT only consists of just 1 block) and this consistent get is a “cheaper” consistent get examination, which in turn only requires the 1 latch. So 1 consistent get and 1 latch. For more info on this, see: https://richardfoote.wordpress.com/2009/05/27/indexes-and-small-tables-part-vii-cluster-one/.

2) Use of Unique Index With a Heap Table.

This option only requires 2 consistent gets (one to read the index leaf block and one to read the table block). There can only be a maximum of one row when a single equality predicate is specified as the index is Unique, which means that 2 consistent gets is the maximum necessary (there may only be the 1 consistent get if there are either no rows to be returned or if all required columns can be found in the index). Additionally, because the index is Unique, both consistent gets are the cheaper, 1 latch consistent get examinations. So that’s 2 consistent gets and 2 latches. For more information, see: https://richardfoote.wordpress.com/2009/05/13/indexes-and-small-tables-part-v-its-no-game/.

3) Use of Non-Unique Index With a Heap Table

This option requires at most 3 consistent gets (one to read the leaf block, one to access the table and possibly one more to perform an additional fetch operation and checking the leaf block again in case of more rows, possibly necessary as the index is Non-Unique and there could be more than one row that matches an equality predicate). Unfortunately, as the index is Non-Unique, the consistent gets are the full-blown consistent gets which requires the buffer block to be pinned/unpinned via 2 latch calls. If the last value is specified, then the additional fetch may be unnecessary and if the index contains all the necessary columns, then just 1 consistent get would be necessary. But in the example provided, we’re looking at typically 3 consistent gets and 6 latches. For more information, see: https://richardfoote.wordpress.com/2009/05/05/indexes-and-small-tables-part-iv-treefingers/.

4) Use of a Full Table Scan

This option requires as a minimum 4 consistent gets (as Oracle needs to not only access the one block containing the 42 rows, but additionally the table segment header multiple times in order to determine the extent map, segment HWM etc.). These additional consistent gets are not generally an issue as these overheads are negligible for a typical FTS of a typical table. But in this example, with a tiny table, these consistent gets makes the difference. Note also that all 4 consistent gets require the block to be pinned/unpinned and so require 2 latch gets each. So that’s 4 consistent gets and 8 latches, minimum, even if all the rows can fit in 1 table block. On its own, no big deal, but if this small lookup table is accessed 10,000 times a minute, that’s potentially a lot of extra CPU and contention when performing a FTS over the above options. For more info, see: https://richardfoote.wordpress.com/2009/04/16/indexes-on-small-tables-part-i-one-of-the-few/ and the other articles on Indexes on Small Tables.

To those that got it right, well done. To those that didn’t, hopefully you’ve learnt something useful.

The moral of the story, no table is too small to potentially benefit from an index, even if the table is only one block in size 🙂

New question and discussion tomorrow.