Re: 2 Oracle doubts

Hi "Tanel Poder" <tanel@@peldik.com>, thanks for writing this:
> One more reason that came into my mind, is that keys in index leaf block are
> always ordered. That makes scanning faster, for exaple when the wanted value
> doesn't exist in table...
>
> Tanel.
>
> > Why such difference? One reason is that with full table scan segment
> header block (or more in case of big segment) has to be visited, to find out
> where the extents of segment are, but for indexes no such operation is done.
> You can verify it with 10046 trace, with index access you see one sequential
> read, with table you see one sequential and one scattered read...
>

You make some very valid points ... and points that should be considered in any design decision. However, suppose I know that the vast majority of hits will be for NY (I picked that only because it's somewhere near the middle of the sorted set of state abbreviations, whether I use ascending or descending in my index), followed by IL. Because I "know" my data, I can put NY and IL at the beginning of the States table and find it on the first couple of rows most of the time. If I made an index, they would be in the middle.

Of course, in the case you mention ... of not finding the abbreviation in the table would be better served by including an index. However, since the state (and other lookup codes) - in our particular application - are in drop-down lists, users can only choose items that are in the list.

Again, you have to look at your particular situation and design accordingly. But it's always good to consider the points you made in any case, so thanks for reminding me.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]