RE: 'Theoretical' SQL question

Hi Rick,

Similarly to Phil's presumption that a unique index (or primary key) on a
file would give the information that if you have found one like this there
won't be any others, I would assume that the use of equals and the RRN
function would be alike, and that the optimiser or engine would be able to
use the information to shortcut processing in 2 ways:

1. Retrieve the row "directly" via the rrn
2. Not look for any other rows 

Regards,

Kevin Wright.

> -----Original Message-----
> From: Rick.Chevalier@xxxxxxxxxxxxxxx
> [mailto:Rick.Chevalier@xxxxxxxxxxxxxxx]
> Sent: Friday, 21 January 2005 9:46 AM
> To: midrange-l@xxxxxxxxxxxx
> 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.
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Hall, Philip
> Sent: Thursday, January 20, 2005 3:41 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: 'Theoretical' SQL question
> 
> 
> 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
> 
> 
> Privileged and Confidential.  This e-mail, and any 
> attachments there to, is intended only for use by the 
> addressee(s) named herein and may contain legally privileged 
> or confidential information.  If you have received this 
> e-mail in error, please notify me immediately by a return 
> e-mail and delete this e-mail.  You are hereby notified that 
> any dissemination, distribution or copying of this e-mail 
> and/or any attachments thereto, is strictly prohibited.
> 
> -- 
> 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.
>