RE: 'Theoretical' SQL question

If the field is a unique key, it got to be an index.
If it is an index, then table scan is not needed - so there is no issue.

Do I miss something here?

    Alexei Pytel
always speaking for myself




"Hall, Philip" <phall@xxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/20/2005 07:09 PM
Please respond to
Midrange Systems Technical Discussion


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: 'Theoretical' SQL question






> 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


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.