Virtual Column using deterministic user defined function

Question

I am using a virtual column (oracle 11g) with a deterministic user defined function which takes primary key of the row as parameter and returns a simple scalar value. the virtual columns updates without any problem but when i update the table it throws error:- ora-00054 resource busy and acquire with nowait specified or timeout expired in oracle. my table structure and the function, are as follows:-

 -----------------------------------------------------------------------
   id   employee_name    employee_dept  employee_leaves (vir column)
 -----------------------------------------------------------------------
   2     patrick           mgmt         getEmpLeaves(id)  
   3      jack             sales            "
 -----------------------------------------------------------------------

     create or replace function getEmpLeaves(empId number) 
        return number 
          DETERMINISTIC
       is
           emp_leaves number;
       begin
           select leaves into emp_leaves from tbl_emp_leaves 
           where tbl_emp_leaves.id = empId;
           return emp_leaves;
      end ;
    -------------------------------------------------------------

How to overcome this error?

Can you show the table creation statements for both tables, and the statements you issue to get that exception? It might be helpful to say what version (release 1 or release 2) and the patch level you are on. I can't immediately replicate in 11.2.0.4.7, but I'm making some assumptions. Is there an index involved too? — Alex Poole, Apr 12 '16 at 12:48

score 2 · Accepted Answer · answered Apr 12 '16 at 19:00

I am not going to look for a reason of this error.
A short answer is: remove this virtual colum from the table, and create a view instead:

create view vw_employees AS
SELECT t.id, t.employee_name, t.employee_dept, x.leaves As employee_leaves
FROM tbl_employees t
JOIN tbl_emp_leaves x
ON t.id = x.id;

A long answer: Please take a look at the below simple test case:

create table tbl_emp_leaves as
select object_id as id, trunc(dbms_random.value(0,100)) as leaves
from all_objects;
alter table tbl_emp_leaves add primary key( id );

create or replace function getEmpLeaves(empId number) 
        return number 
          DETERMINISTIC
       is
           emp_leaves number;
       begin
           select leaves into emp_leaves from tbl_emp_leaves 
           where tbl_emp_leaves.id = empId;
           return emp_leaves;
      end ;
      /

create table tbl_employees as
select object_id as id, object_name as employee_name, object_type as employee_dept
from all_objects;

alter table tbl_employees 
add employee_leaves as ( getEmpLeaves(id)); 

create view vw_employees AS
SELECT t.id, t.employee_name, t.employee_dept, x.leaves As employee_leaves
FROM tbl_employees t
JOIN tbl_emp_leaves x
ON t.id = x.id;

And now compare a performance of two simple queries:

SQL> set timing on;
SQL> select sum(employee_leaves) from vw_employees;

SUM(EMPLOYEE_LEAVES)
--------------------
             3675425

Elapsed: 00:00:00.07
SQL> select sum(employee_leaves) from tbl_employees;

SUM(EMPLOYEE_LEAVES)
--------------------
             3675425

Elapsed: 00:00:03.09

3.09 second vs. 0.07 second - you see that the virtual column based on the function is 44 times ( that is: 4400%) slower than a simple join.

so the answer to my question is, there is not way virtual column can be used the way i used it. Actually data is coming from like 4 tables and accumulate in one column in 5th table. i though of view (it would be slow) and materialized view (it would not be real time, will require refreshes) and triggers will have to be applied on 4 tables (add, update and delete in order to draw exact picture). virtual column was the easiest and cleanest way to do it but i has technical limitations till Oracle solves this issue in some release. — jaykio77, Apr 13 '16 at 13:50

score 1 · Answer 2 · answered Apr 12 '16 at 20:46

I had a similar problem when I created a function which select another table.

Keyword DETERMINISTIC means the function always returns the same value for a given input parameter. It is in the responsibility of the developer to ensure this.

Oracle allows a function like this:

create or replace function getNumber(x number) 
    return number DETERMINISTIC is
begin
  return DBMS_RANDOM.RANDOM;
end;

Although this is really the opposite of a deterministic function.

Return value of your function changes after an UPDATE tbl_emp_leaves SET leaves = ... statement.

So, I have to agree with kordirko's answer: remove this virtual column and create a view or use a trigger to set value of column employee_leaves.

Virtual Column using deterministic user defined function

2 Answers2

Linked