RE: SQL question

On Sat, 2005-10-29 at 20:24 -0500, Vernon Hamberg wrote:
> One of the replies used the DIGITS() function and still ran a table 
> scan. This illustrates a basic rule - avoid expressions that use 
> functions in the WHERE clause and in JOIN specifications, because it 
> will ALWAYS result in a table scan (or probably at least 99% of the 
> time). This is because it cannot eliminate anything without first 
> running the calculation, so it has to run that calculation on ALL the records.
> 
> Make sense?
Oh yeah.  

However, I figured there must be a way to hint to the optimizer that
indeed the index can be used because the expression is so simple.

A colleague mentioned that the table scan could be avoided using OPNQRYF
with mapfld, but I never was able to get it to work.  In my tests, it
resulted in the system building a new access path even when a supposedly
suitable index exists, or if no key was specified, then a table scan.


> 
> The solution offered here is least likely to use a table scan, as it 
> has no functions in it.
Yes, it works great.  Since there is an index by DATE_YY, DATE_MM,
DATE_DD, no table scan takes place.

> 
> HTH
> Vern
> 
> At 03:29 PM 10/28/2005, you wrote:
> 
> >How about:
> >(DATE_YY > SelectedYear or
> >  (DATE_YY = SelectedYear and
> >   (DATE_MM > SelectedMonth or
> >    DATE_MM = SelectedMonth and
> >    DATE_DD >= SelectedDay))) and
> >(DATE_YY < Selected Year or
> >  (DATE_YY = SelectedYear and
> >   (DATE_MM < SelectedMonth or
> >    DATE_MM = Selected Month and
> >    DATE_DD <= SelectedDay)))
> >
> >Out of curiosity, why do you want to use SQL if the RPG code can easily
> >select the records?
> >
> >Donald R. Fisher, III
> >Project Manager
> >Roomstore Furniture Company
> >(804) 784-7600 extension 2124
> >DFisher@xxxxxxxxxxxxx
> >
> ><clip>
> >What I can't seem to figure out is how to use SQL to select a date range
> >that doesn't result in a table scan.
> >
> >These result in a table scan:
> >SELECT DATE_YY * 10000 + DATE_MM * 100 + DATE_DD FROM TABLE WHERE
> >        (DATE_YY * 10000 + DATE_MM * 100 + DATE_DD) >= 20051001 AND
> >        (DATE_YY * 10000 + DATE_MM * 100 + DATE_DD) <= 20051028
> >
> >SELECT DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) FROM TABLE
> >WHERE (DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) >=
> >'20051001' AND
> >(DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) <= '20051028'
> >
> >
> >This one doesn't, but it's problematic:
> >
> >SELECT DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) FROM TABLE
> >WHERE
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 1) OR
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 2) OR
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 3) OR
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 4) OR
> >       ...
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 25) OR
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 26) OR
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 27) OR
> >     (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 28)
> ><clip>
> >--
> >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.
>