The use of functions - a function with other selects (eg. calculate availability of a part) - is slowing down our system when we do a select over our product file.
Is there some kind of rule when functions should be used or when we should try to create a more complex - combined - SQL that does not use the function.
Can functions be used in the where clause without loosing a lot of speed?
Speed of sql is related to complexity of sql which in turn depends on logical IOs and waits.
Select 'fast sql' from dual;
would be faster than
Select (some_function_calling_10s_of_big_tables) from dual;
Analyze what the function is returning and why it is needed. Tune the function or better yet, include the logic in sql.
Olivejuice, good question, so I've jotted down a few comments at the following URL: