3 Replies Latest reply: Jul 7, 2007 3:37 PM by 299178 RSS

    Use of functions

    435564
      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?
        • 1. Re: Use of functions
          299178
          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.
          • 2. Re: Use of functions
            Jonathan Lewis
            Olivejuice, good question, so I've jotted down a few comments at the following URL:
            http://jonathanlewis.wordpress.com/2007/07/07/functions/

            Regards
            Jonathan Lewis
            http://jonathanlewis.wordpress.com
            http://www.jlcomp.demon.co.uk
            • 3. Re: Use of functions
              299178
              Jonathan,
              That was so quick and awesome. No wonder you are an Oracle Guru.

              Thanks and Regards,

              Seenshoo.