According TheFreeDictionary.com, the word “deterministic” means…
| deterministic |
de·ter min·is tic adj. |
an inevitable consequence of antecedent sufficient causes |
According to Wikipedia, the explanation of deterministic algorithm is…
In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.
In the Oracle PL/SQL Language documentation, it is used as a keyword, as follows…
DETERMINISTIC
Indicates that the function returns the same result value whenever it is called with the same values for its parameters.
You must specify this keyword if you intend to invoke the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When the database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than re-executing the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.
Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so are not captured if the database chooses not to re-execute the function.
These semantic rules govern the use of the DETERMINISTIC clause:
-
You can declare a schema-level subprogram
DETERMINISTIC. -
You can declare a package-level subprogram
DETERMINISTICin the package specification but not in the package body. -
You cannot declare
DETERMINISTICa private subprogram (declared inside another subprogram or inside a package body). -
A
DETERMINISTICsubprogram can invoke another subprogram whether the called program is declaredDETERMINISTICor not.
There is a subtle twist about this explanation. It states that the keyword “indicates that the function returns the same result value whenever it is called with the same values for its parameters“, but if you think about the use of the verb indicates, you realize that they are conceding that the keyword itself doesn’t enforce the behavior. Instead, it is curiously carefully-chosen language to sidestep the important fact that the PL/SQL language compiler does not actually enforce the necessary behavior.
So as a result, it is possible to write the following function…
SQL> create or replace function test_func(in_col1 in number) 2 return number deterministic 3 is 4 v_col1 number; 5 begin 6 select col1 7 into v_col1 8 from test_tbl2 9 where col1 = in_col1; 10 return(v_col1); 11 end test_func; 12 / SQL> show errors No errors.
Is this function really deterministic? No, of course not. Anyone else changing data in the TEST_TBL2 table can change the outcome of this function.
Yet, the DETERMINISTIC keyword did not cause compilation of the function to fail, as it should have. Only the use of the pragma restrict_references using the qualifiers RNDS (i.e. read no database state), RNPS (i.e. read no package state), WNDS (i.e. write no database state), and WNPS (i.e. write no package state) would do that…
SQL> create or replace package test_pkg 2 as 3 function test_func(in_col1 in number) 4 return number; 5 pragma restrict_references(test_func,RNPS,WNPS,RNDS,WNDS); 6 end test_pkg; 7 / SQL> show errors No errors. SQL> create or replace package body test_pkg 2 as 3 function test_func(in_col1 in number) 4 return number 5 is 6 v_col1 number; 7 begin 8 select col1 9 into v_col1 10 from test_tbl2 11 where col1 = in_col1; 12 return(v_col1); 13 end test_func; 14 end test_pkg; 15 / Warning: Package Body created with compilation errors. SQL> show errors Errors for PACKAGE BODY TEST_PKG: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/2 PLS-00452: Subprogram 'TEST_FUNC' violates its associated pragma
Notice that this pragma can only be used within a function declared within a PL/SQL package; this pragma cannot be used within a standalone function. But it proves that the PL/SQL compiler is capable of detecting the problem, and failing the compilation. They have the technology.
Further, it is now possible to create a function-based index using this function…
SQL> create index test_tbl1_fbi on test_tbl1(test_func(col1)) 2 tablespace users compute statistics; Index created.
…and that function-based index will be used by the Oracle optimizer for queries, after all, why shouldn’t it?
SQL> select t1.col1 t1_col1, test_func(t1.col1) t2_ool1 2 from test_tbl1 t1 where test_func(t1.col1) = 170; T1_COL1 T2_OOL1 -------------------- -------------------- 170 170 Execution Plan ---------------------------------------------------------- Plan hash value: 357717947 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 170 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL1 | 10 | 170 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_TBL1_FBI01 | 4 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- SQL> select /*+ full(t1) */ t1.col1 t1_col1, test_func(t1.col1) t2_ool1 2 from test_tbl1 t1 where test_func(t1.col1) = 170; T1_COL1 T2_OOL1 -------------------- -------------------- 170 170 Execution Plan ---------------------------------------------------------- Plan hash value: 1370928414 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 170 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_TBL1 | 10 | 170 | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------- SQL> select t1.col1 t1_col1, t2.col1 t2_ool1 2 from test_tbl1 t1, test_tbl2 t2 3 where t2.col1 = t1.col1 4 and t1.col1 = 170; T1_COL1 T2_OOL1 -------------------- -------------------- 170 170 Execution Plan ---------------------------------------------------------- Plan hash value: 2884964714 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 8 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| TEST_TBL2_PK | 1 | 4 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| TEST_TBL1_PK | 1 | 4 | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------
So, whether the query uses the function-based index, or whether it performs a simple FULL table scan, or whether the function-based index isn’t used at all, the results are the same.
But, now suppose another session changes that row in the TEST_TBL2?
SQL> update test_tbl2 2 set col1 = 1700 3 where col1 = 170; 1 row updated. SQL> commit; Commit complete.
…and now someone performs a query using the function-based index?
SQL> select t1.col1 t1_col1, test_func(t1.col1) t2_ool1 2 from test_tbl1 t1 where test_func(t1.col1) = 170; T1_COL1 T2_OOL1 -------------------- -------------------- 170 170 Execution Plan ---------------------------------------------------------- Plan hash value: 357717947 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 170 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL1 | 10 | 170 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_TBL1_FBI01 | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------
How can that be? We know that the UPDATE changed this data? And here is proof obtained by bypassing the function-based index during the WHERE clause by forcing a FULL table scan…
SQL> select /*+ full(t1) */ t1.col1 t1_col1, test_func(t1.col1) t2_ool1 2 from test_tbl1 t1 where test_func(t1.col1) = 170; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1370928414 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 170 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_TBL1 | 10 | 170 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------------
And here is further proof obtained by completely eliminating the function from the SELECT list and instead performing a simple inner-join…
SQL> select t1.col1 t1_col1, t2.col1 t2_ool1 2 from test_tbl1 t1, test_tbl2 t2 3 where t2.col1 = t1.col1 4 and t1.col1 = 170; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2884964714 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 8 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| TEST_TBL2_PK | 1 | 4 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| TEST_TBL1_PK | 1 | 4 | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------
So, what PL/SQL has permitted us to do is create a situation where it would be reasonable for end-users to conclude that the database has corrupted data. In a sense, it does — the corrupted data is within the function-based index, where deterministic data is expected.
I found this very situation on the loose in the wild; that is, within a production application in use at a healthcare company. Think about that.
I didn’t find it because someone complained about possible data corruption. I found it because an AWR report pointed me at the SQL statement within the function, which was being executed 1.3 billion times over a 5 day period. Each execution was quite fast, but if you do 1.3 billion of anything over less than a lifetime, someone will eventually notice.
If you consider that 1.3 billion executions over a 5 day period implies a average rate of about 3,000 executions per second, sustained, for every second of those 5 days, then you start to get an idea of the problem. Especially when you consider that there were peaks and valleys in that activity.
So, I have raised the issue with the affected healthcare organization, and the problem is worming its way through change management. In the meantime, this application continues to return incorrect information, over and over and over again.
Are you sure that none of your function-based indexes were built this way?
