RE: 'Theoretical' SQL question

["Hall, Philip" <phall@xxxxxxxx>]

> What would you expect?  Making the presumption that the optimizer has to
> perform a table scan, it doesn't know how many, or where, any matching
> records might be.  Resulting in the entire table being read.  It's no
> different than reading through an unsorted file with a program looking for
> records with a specific key.  I don't think it has anything to do with
> which database product you are using.

What would I expect? What I originally asked: if the database was smart enough.

In both Kevin's and my cases (albeit simple cases) there is a point where the 
record is found and the processing could theoretically stop.

In my case, it's when the unique ID has been found, since if the column 
constraint is set as a unique key how can there be other records to find?

In Kevin's case, it's simply when the relative record number is reached.

Granted, in both examples if the case you've chosen to select is the highest 
unique item in the table or in Kevin's example last one in the table it will 
result in a full table read. Which is why I asked the second question in my 
email (you did read it, right? And in case you didn't I'll repeat myself again) 
about whether the search algorithm would be smart enough to 'switch' search 
methods.

In real world situations with tens or hundreds on millions of records 'smart' 
stuff like that saves hours.

--phil