Re: SELECT: How to re-use result of complicated function ?

What kind of functions do you have? Do their results depend solely on the input parameters and not on any session variables or database objects which might change in between calls?

If yes, then you could also try declaring your functions as DETERMINISTIC. If this is feasible, then Oracle does not re-execute the same function with the same parameters within the same statement.

See the PL/SQL manual for details on this. HTH.

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<4132ec84$1_at_olaf.komtel.net>...
> Hi,
>
> this rather looks like a SQL question but I am in an Oracle setup, so if
> there is an Oracle-specific solution to that I wouldn't mind, that's why I
> am posting here.
>
> I need to select from a table and perform some complicated operations of
> the (numerical) values in the table which will be the result of my select,
> for example:
>
> SELECT Id, some_complicated_function(field1), field2
> FROM my_table;
>
> So far so good. Now I need to select another column that also has a
> complicated calculation and I would like to re-use the result of
> some_complicated_function(field1) because it is part of the calculationt
> here. I would like to do this without calling
> some_complicated_function(field1) AGAIN because I am afraid that might slow
> down my performance significantly. Here is a bad example:
>
> SELECT Id, some_complicated_function(field1),
> some_other_complicated_function(field3, some_complicated_function(field1)),
> field2
> FROM my_table;
>
> I consider this example bad because some_complicated_function(...) will
> get called twice.
>
> I know that under certain circumstances it is possible to refer to a
> column that got selected just by its position, in ORDER BY for example:
> SELECT field1, field2 FROM my_table ORDER BY 2 ----> this will actually
> order by field2. So now I would like to know if there is a mechanism to
> refer to the result of my some_complicated_function(field1) column when
> calculating my other complicated column... obviously I can't use the
> positiob because it would be interpreted as an ordinary integer.
>
> Thanks a lot,
>
> André Hartmann
> :)
Received on Mon Aug 30 2004 - 09:50:32 CDT