Best Method To Select One Row From Small Table Quiz (Each Small Candle) September 5, 2011

Assume you have a tiny little table with just 42 rows (naturally) that all fit in one table block. Order the following options in order of “efficiency” (most efficient option first) when accessing just one of these rows:

1) Full Table Scan of Heap Table

2) PK access of an Index Organised Table

3) Index access of Heap Table via a Unique Index

4) Index access of Heap Table via a Non-Unique Index

If you think any of the options are the same, then you can order them as follows (example only):

1) Option 1

2) Option 2

2) Option 3

4) Option 4

Answer in the next few days …

UPDATE: just to clarify based on comments already made.

Yes, any index must visit the table as there are required columns within the table that are not stored in the index (this is implied by the above options). The table has only ever contained 42 rows and the are no additional table blocks below the table HWM (not that this really makes a difference to the answer). To keep it simple, the column being queried has a NOT NULL constraint (although it doesn’t really matter, except for when you want to put a PK constraint on it such as with the IOT option).

I dont feel other options will valid here if you have to access your data block , if goes with index, a root(hope root and branch leaf blocks will be only one) the cost may will be 1+1 =2 (index block + datablock)blocks for index access where in full tablescan will be as 1 block.

I should have thought a little more carefully about this question before posting my previous comment.

My first question would be, how can you create a table that contains a single database block? There must be a minimum size that a table with a single column can be. However, block size is configurable, so, you could create a several tables all one block but each a different size.

Also, is it not true that an IOT is not treated like a table and does not have any blocks associated with it?

A guess now with deferred segment creation, the minimum size of a table can be 0 blocks (ie. there is no segment until the first row is inserted). However, if you have inserted a row, then the smallest table you can have is one with just the one block below the high water mark.

So this table in question is effectively as small as you can get with a table that actually contains rows. Assume the same block size for both table and indexes.

An IOT is treated like a table in that you can select from it and perform DML with it but it’s structurally an index. But an index that doesn’t point to any rows in another table segment.

So the question is not meant to asked in a manner in which the answer depends. The 4 options can be listed in a consistent order based on accessing 1 row from a 42 row table in which all rows reside in the 1 table block.

I think from what I have read the IOT and unique index are functionally similar, the difference being the IOT does not need to access the table data. The non unique index might have nulls and same column values and I think this index has to walk along the leaf blocks to the index the required row. The full table scan can read the data in a single block read as the whole table fits in a single block.

So, my guess would be option 2) followed by 3) followed by 1) followed by 4).

To check, I conducted a test, and the results were quite revealing. I’ll not post them unless they are different to your answers Richard when you post them.

Difference between a PK and an IOT is that the PK indeed refers to a table segment via the associated rowids while an IOT has the additional columns co-located within the index structure and so doesn’t need a table segment to reference (although you can have an overflow segment to cater for columns you may not want within the index).

Note that the Non-Unique index can’t have NULLs the same as with the Unique index if the entire index entry consists of NULLs.

With such a tiny table, the index is likewise tiny and consists of just one block as well so there’s no index leaf blocks it needs to walk along, there’s just the one.

Hopefully I’ve helped explain why the FTS is the most expensive of the options :)

Note during most FTS, the extra few consistent gets to access the segment header matters little (a FTS scan of a 500M table for example).

However, for an index scan, a few extra consistent gets could potentially double the overall costs of using the index. That’s why Oracle is far more cleverer in determining the location of the index root block, which is where all the action begins, without having to access the index segment header.