RE: SQL question

>Is this true on all platform implementations of SQL?

Pretty much. The index is built on the underlying data, and you're
trying to use the results of a function. A Materialized Query Table
might help you here -- but they're not really useful yet. You can build
them, but the optimizer won't look at them automatically.

>What about those "deterministic" attributes of the sql function, 
>do they enable SQL to reuse and cache function results?

Sure, but it probably wouldn't help you here, what results would it
cache. It would still have to read the row to know what data to send
into the function.

>I am curious about this because the large i5 
>where I am at is being brought to its knees by 
>sql coded by people who dont have an as400 background.

My guess is that it's not that they don't have an as/400 background, but
rather that they don't have a large-table background. You can get away
w/just about anything on a table w/50,000 rows. <G> Also, has your DBA
looked at the issues. And don't say "the as/400 doesn't need a dba."
Those queries might run perfectly fine if there was a good index for
them to use.

Finally, if there are a couple of queries that are bringing the i5 to
its knees, have a good sql developer look at them. For example, we
recently removed a function call from a select and added a join and
moved a query from ~7 seconds to too quick to measure w/o tools. 

Remember, the beauty of SQL is that it will get you the data you ask for
in the order you want regardless of how you've indexed your tables. But
remember, the curse of SQL is that it will get you the data you ask for
in the order you want regardless of how you've indexed your tables. <G>

-Walden

------------
Walden H Leverich III
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com
 Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)