oracle-databaseoracle11gcalculated-columnsvirtual-column

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?


Solution

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