RE: 'Theoretical' SQL question

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

Thanks for the info Kevin.

It's a sad state of affairs, and no wonder why SQL Server simply out performs 
both DB2 and Oracle...

--phil

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of Kevin Wright
> Sent: Thursday, January 20, 2005 3:14 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: 'Theoretical' SQL question
> 
> >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?
> >
> --
> 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.