RE: 'Theoretical' SQL question

[Kevin Wright <Kevin.Wright@xxxxxxxxxxxx>]

>From what I can work out it does even worse for WHERE RRN(CUSTOMERS) = 12345
... can you pronounce full table scan from rrn 1 to rrn last, not stopping
at 12345 ... and apparently IBM is not going to improve it. 

Kevin Wright 

> -----Original Message-----
> From: Hall, Philip [mailto:phall@xxxxxxxx]
> 
> The 'theoretical' SQL question is as follows;
> 
> Can one assume 100% that the optimiser/SQL engine will 
> 'break' from processing when performing a SELECT * FROM 
> CUSTOMERS WHERE ID = '123456' after hitting the record with 
> the matching ID when ID is defined as a unique key field?
> 
> I'm in the camp that the software is 'smart enough' (or was 
> written smart enough) to realise that once that value is 
> found, and since the key is unique there shouldn't/won't be 
> any other records to find that will match the WHERE clause, 
> that the processing of the query will terminate right there 
> and then and return the found record with no further record searches.
> 
> Is this how everyone else would expect the processing to occur?
> 
> Also, would it also be expected that the search algorithm 
> (with or without the use of indices or access paths) would 
> also be smart enough in this instance (selecting on a unique 
> key) that it wouldn't be a traversal search but something 
> faster e.g. a binary tree/chop search?
>