Originally posted by pando
in Chris post, if your table is big then second solution is always better, the consistent gets is much much smaller.
Never say always (and never say never). Sometimes it is much better, sometimes it is worse. There appear to be a lot of variables and I haven't been able to pin them all down yet. The MIN/MAX optimiser step is very fast, so if a good index exists, it is hard to beat.

Originally posted by gandolf989
If this value doesn't change during the day then why keep performing queries to find a value that doesn't change. If it does change during the day the then some type of correlated subquery is needed. Which takes more effort a function that returns one value from a table with one record, or a range scan on an index for a table that could be quite large?
Unfortunately, you are mixing solutions. Yes, if they keep querying the exact same value throughout the day (which rarely happens in the real world), then storing it somewhere (most likely as a de-normalized column in another table) would likely be more efficient. In either case, however, SELECTing that value from whichever table *directly in the sub-select* would be faster than having that same SELECT in a function. The function solution causes the function's statement to be parsed, etc. for every row as well as a context switch for every row. This is an awful lot of overhead. While using PL/SQL functions in SQL is necessary for certain situations, it should be avoided where possible.

- Chris