8 Replies Latest reply: Apr 14, 2008 1:51 AM by 361642 RSS

    What are deterministic functions in oracle.??

    612260
      Hi,

      Can anyone give me any information regarding deterministic functions?

      Thanks
        • 1. Re: What are deterministic functions in oracle.??
          575729
          a deterministic function return a pre-determine result for a specific value. Its mostly used with sql. like

          select deterministic_fnc(empno) from emp;

          here for a specific value oracle can determine its corresponding value. if our result changes for same specfic value each time we query its not deterministic.
          • 2. Re: What are deterministic functions in oracle.??
            sgalaxy
            For example the length built-in function is a deterministic function , as it returns the same value when it takes the same input as parameter...
            SQL> SELECT LENGTH('sgalaxy') from dual;

            LENGTH('SGALAXY')
            -----------------
                            7

            whereas the function value of dbms_random built-in package is not since it may not return the same value when it gets the same parameters....
            SQL> select dbms_random.value(1,2) from dual;

            DBMS_RANDOM.VALUE(1,2)
            ----------------------
                  1,66658146766042

            SQL> select dbms_random.value(1,2) from dual;

            DBMS_RANDOM.VALUE(1,2)
            ----------------------
                  1,28069699278695

            SQL> select dbms_random.value(1,2) from dual;

            DBMS_RANDOM.VALUE(1,2)
            ----------------------
                  1,53762206451598
            Greetings...
            Sim
            • 3. Re: What are deterministic functions in oracle.??
              Frank Kulash
              In addition to what the others have said:

              You are never required to declare a function as DETERMINISTIC, but you should (when applicable), because Oracle can cache the results of the function calls, and only call the function when necessary.

              For example:
              SELECT f (cd), ...
              FROM table_x;
              Suppose table_x has 1,000,000 rows, and 50 distinct values for cd. If function f is declared as DETERMINISTIC, then Oracle can do this query with only 50 function calls. If f is not declared as DETERMINISTIC, Oracle has no choice: it must call the function 1,000,000 times. No matter how trivial the function is, the mere act of calling PL/SQL that many times can slow down your query.
              • 4. Re: What are deterministic functions in oracle.??
                sgalaxy
                "You are never required to declare a function as DETERMINISTIC , but you should (when applicable), "
                One case is when you want to declare your own function and used it in a function-based index.....

                Greetings...
                Sim
                • 5. Re: What are deterministic functions in oracle.??
                  612260
                  thank u all for the replies.
                  • 6. Re: What are deterministic functions in oracle.??
                    361642
                    I just run into a bug using the "deterministic" keyword (10gR2). It is like the following:

                    SELECT id, value, function_value FROM vw_test;

                    Where "function_value" is the result of a deterministic function. id and value are from the same table, but it is joined with another one so they appear more than once. The result set is something like

                    1, a, yes
                    1, a, yes
                    1, NULL, no

                    The table values are

                    1, a

                    If I don't use the function or delete the "deterministic" keyword everything works fine. Anyway, I just found out the the database can save function calls, but it does not!

                    So I am thinking about using "deterministic" in the future...

                    Regards,
                    Torsten
                    • 7. Re: What are deterministic functions in oracle.??
                      John Spencer
                      I am not sure I understand what you are saying here. If you declare a function to be deterministic, then yes, Oracle may cache the results of the call and reuse the cached result when it sees another call to the function with the same parameters.

                      However, given that value is different in your third rowm, it appears that you may have two different function calls here.

                      Another thing to note is that you can declare any function to be deterministic, and Oracle will believe you. It does not make any attempt to decide whether you are correct or not.
                      SQL> CREATE FUNCTION determ (p_num IN NUMBER) RETURN NUMBER
                        2     DETERMINISTIC AS
                        3  BEGIN
                        4     RETURN p_num * DBMS_RANDOM.VALUE();
                        5  END;
                        6  /

                      Function created.

                      SQL> CREATE FUNCTION nondeterm (p_num IN NUMBER) RETURN NUMBER AS
                        2  BEGIN
                        3     RETURN p_num * DBMS_RANDOM.VALUE();
                        4  END;
                        5  /

                      Function created.

                      SQL> SELECT num, determ(num)
                        2  FROM (SELECT rownum num FROM all_objects
                        3        WHERE rownum <= 5
                        4        UNION ALL
                        5        SELECT rownum num FROM all_objects
                        6        WHERE rownum <= 5);

                             NUM DETERM(NUM)
                      ---------- -----------
                               1  .808346096
                               2  1.24224706
                               3  2.55670491
                               4  .814681413
                               5  3.10128137
                               1  .117673406
                               2  1.24224706
                               3  2.55670491
                               4  .814681413
                               5  3.10128137

                      10 rows selected.

                      SQL> SELECT num, nondeterm(num)
                        2  FROM (SELECT rownum num FROM all_objects
                        3        WHERE rownum <= 5
                        4        UNION ALL
                        5        SELECT rownum num FROM all_objects
                        6        WHERE rownum <= 5);

                             NUM NONDETERM(NUM)
                      ---------- --------------
                               1     .536325578
                               2      1.6236528
                               3     .628491757
                               4      2.6261965
                               5     4.09006466
                               1     .419011544
                               2     1.01745468
                               3     2.76301772
                               4     2.32793484
                               5     3.72438691
                      So, the deterministic version did cache the function result (I'm not sure why it did not cache 1), but the non-deterministic version was called once for each row.

                      John
                      • 8. Re: What are deterministic functions in oracle.??
                        361642
                        Hi John,

                        thanks for your reply. I know what deterministic functions are and my function is something like

                        ...if input_parameter = 'A' then return 'B' else return 'C'...

                        The function call is only in the third column and it depends on the second column. The second column is like the first one from a table. But when using the deterministic function the table column will bring a wrong value. Definitly a bug.

                        That is one reason not to use the deterministic keyword at the moment. The second reason is, that oracle may save function calls (of course with the same parameter). but it does never.

                        Regards,
                        Torsten