Whether the Oracle function call occurred before or during the SQL call

For the dbms_random described in the previous article. In the case of the value function, it is obvious that the call to the function occurred during the execution of the SQL statement. However, if you look at the processing of constants in SQL statements, you can see that for function calls like to_date, when the input parameter is constant, Oracle will treat it as a constant and invoke it before the SQL statement executes.

The same functions, all with constants as arguments, are not dependent on the table column, why some functions run before the SQL call, and some functions execute in the SQL call. Is it just because one is a normal function and the other is a function in a package?

The problem obviously has nothing to do with whether the function is stored in the package, which is actually a feature of the function that controls the time of the function call. If a function is deterministic, the result is also constant for the input of a constant, so Oracle invokes the function before the SQL runs. And if the function is not deterministic, Oracle cannot guarantee the certainty of the function output, so it can only be executed at run time of SQL.

Sql> CREATE TABLE T (ID number);

Table created.

Sql> INSERT into T SELECT rownum to DUAL CONNECT by level <= 10000;

10000 rows created.

Sql> COMMIT;

Commit complete.

sql> CREATE OR REPLACE FUNCTION f_test_deter

2 return number deterministic as

3 BEGIN

4 Dbms_lock. Sleep (0.01);

5 return 1;

6 end;

7/

Function created.

sql> CREATE OR REPLACE FUNCTION f_test_nodeter

2 return number as

3 BEGIN

4 Dbms_lock. Sleep (0.01);

5 return 1;

6 end;

7/

Function created.

Sql> SELECT object_name, deterministic

2 from User_procedures

3 WHERE object_name like ' f_test_% ';

object_name DET

------------------------------ ---

F_test_nodeter NO

F_test_deter YES

Sql> SET TIMING on

Sql> SELECT * from T WHERE ID = f_test_deter;

Id

----------

1

elapsed:00:00:00.02

Sql> SELECT * from T WHERE ID = f_test_nodeter;

Id

----------

1

elapsed:00:01:49.99

Depending on the elapsed time, the deterministic function runs only once before the SQL call, and the Nondeterministic function runs once for each record of the T table.

If the dbms_random in the previous article. The value package is encapsulated and set to a deterministic function, the query results from the previous article change:

Sql> SET TIMING off

sql> CREATE OR REPLACE FUNCTION f_random

2 return number deterministic as

3 BEGIN

4 return ROUND (dbms_random. VALUE (1, 10000));

5 end;

6/

Function created.

Sql> SELECT * from t_id WHERE ID = f_random;

Id

----------

9548

Sql> SELECT * from t_id WHERE ID = f_random;

Id

----------

6925

Sql> SELECT * from t_id WHERE ID = f_random;

Id

----------

7783

Sql> SELECT * from t_id WHERE ID = f_random;

Id

----------

7302

Sql> SELECT * from t_id WHERE ID = ROUND (dbms_random. VALUE (1, 10000));

Id

----------

2730

Sql> SELECT * from t_id WHERE ID = ROUND (dbms_random. VALUE (1, 10000));

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45452.htm