CODEWITHSUNDEEP
oracle-databaseoracle11gcalculated-columnsvirtual-column
jaykio77
jaykio77

Reputation: 391

Virtual Column using deterministic user defined function

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?

Upvotes: 0

Views: 2309

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

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.

Upvotes: 2

Related Questions