Virtual Column - Using it in view (Oracle)

Question

I have made a virtual column in Oracle, using the following code.

CREATE OR REPLACE function email_address (ID_ varchar2) 
return varchar2 
deterministic 
as 

lname varchar2 (256); 
snumber varchar2 (256); 
 email varchar2 (256); 
    BEGIN 
    select substr( p.name, instr( p.name, ' ', -1 ) + 1 ) into lname 
    from person p where p.id = id_; 

    SELECT regexp_replace(p.service_no, '[^0-9]*', '') into snumber 
    FROM person p where p.ID = id_; 

  email:= snumber||lname||'@met.af'; 

return email; 
end email_address; 

The virtual column works fine and it does populate what i want to acheive in the virtual column. But the problem arises when i make a view using virtual column's table; population of view takes hell lot of time (max 5 mins). Here, i would like to mention that the view works perfectly fine if i don't use the function (i.e empty column of email). The code for view is as under

select  distinct 
    person.SERVICE_NO as Service_No, 
    person.CNIC_NO as CNIC, person.NAME as NAME , 
    card.CPLC_SERIAL_NO as Card_Number, 
    child_dc.NAME as Child_DC, 
    root_dc.NAME as Root_DC, person.OU as OU, 
    person.EMAIL as Email 
from 

 person_card inner join person 
 on person_card.PERSON_ID = person.ID 
 inner join card 
 on person_card.CARD_ID = card.ID 
    left outer join child_dc 
 on person.CHILD_DC_ID = child_dc.ID 
    left outer join root_dc 
 on child_dc.ID = root_dc.ID;  

What i presume is that when i make a virtual column, oracle forcibly asks me to keep the datatype length to 4000, therby, making it too large or heavy to populate. What should i do to make the view populate. I need to have a virtual column as email is not being entered by the application. Help required.

Answer 1

I don't think it's the size of the column (btw: don't "presume", test).

My bet is that the function gets called over and over again for each row from the result. So for each row in the result you make two(!) selects on the person table.

Edit: I was wrong with that above statement. The function for a virtual column is only called when the column it is based on is updated.

You can slightly improve that by only doing a single SELECT in your function:

select substr( p.name, instr( p.name, ' ', -1 ) + 1 ), 
       regexp_replace(p.service_no, '[^0-9]*', '')
   into lname, snumber
from person p where p.id = id_;

But I would recommend to simply put that logic into the view and construct the email string inside the view:

select  distinct
        person.SERVICE_NO as Service_No,
        person.CNIC_NO as CNIC, person.NAME as NAME ,
        card.CPLC_SERIAL_NO as Card_Number,
        child_dc.NAME as Child_DC,
        root_dc.NAME as Root_DC, person.OU as OU,
        regexp_replace(p.service_no, '[^0-9]*', '')||substr( p.name, instr( p.name, ' ', -1 ) + 1 )||'@met.af' as email
from person_card 
inner join person
 on person_card.PERSON_ID = person.ID
inner join card
 on person_card.CARD_ID = card.ID
    left outer join child_dc
 on person.CHILD_DC_ID = child_dc.ID
    left outer join root_dc
 on child_dc.ID = root_dc.ID; 

That way no additional select is necessary and it should run just fine.