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?
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.