Insert-Select: reading data from other table when 2 Attributes r not the same (performance)

Question

Hey there

I’ve got a table like this:

Create Table PersonAgent (
    PersonID    varchar2(10) not null,
    AgentID     varchar2(10) not null,
    Address     varchar2(50),
    City        varchar2(50),
    Country     varchar2(50)
)

Well I need to generate this table new, coze some data are incorrect.

If PersonID and AgentID are the same then I can take the other attributes as they are but if they are not the same then I need to read from another table.

Little Example:

INSERT INTO PersonAgent_copy(PersonID, AgentID, Address, City, Country)
Select Pa.Persid, Pa.Agentid, 
    (Case
      When Pa.Personid = Pa.Agentid
      Then pa.Address
      ELSE (SELECT p.Address
          FROM Person p
          Where Pa.Agentid = P.Personid)),
    (Case
      When Pa.Personid = Pa.Agentid
      Then pa.City
      ELSE (SELECT p.City
          FROM Person p
          Where Pa.Agentid = P.Personid)),
    (Case
      When Pa.Personid = Pa.Agentid
      Then pa.Country
      ELSE (SELECT p.Country
          From Person P
          Where Pa.Agentid = P.Personid))
FROM PersonAgent pa

(There are some more attributes that works the same)

What is the fastest way to do this?

Answer 1

You can try this

INSERT INTO PersonAgent_copy(PersonID, AgentID, Address, City, Country)
Select Pa.Persid, Pa.Agentid, pa.Address, pa.City, pa.Country
FROM PersonAgent pa
where Pa.Personid = Pa.Agentid 
union all 
Select Pa.Persid, Pa.Agentid, p.Address, p.City, p.Country
FROM PersonAgent pa, Person p 
where Pa.Personid <> Pa.Agentid and Pa.Agentid = P.Personid

I am not tested this, but you can try it.

Answer 2

You'd be a lot better off doing it this way:

INSERT INTO PersonAgent_copy(PersonID, AgentID, Address, City, Country)
select pa.persid, pa.agentid,
    Case
      When Pa.Personid = Pa.Agentid
      Then pa.Address
      ELSE p.Address
      END,
    Case
      When Pa.Personid = Pa.Agentid
      Then pa.City
      ELSE p.City
      END,
    Case
      When Pa.Personid = Pa.Agentid
      Then pa.Country
      ELSE p.Country
      END
from
   PersonAgent pa 
   left outer join
   Person p
   on pa.agent_id = p.person_id

The reason for this is that, with your method, every time personid and agentid are the same three queries have to be run against the Person table. This can add up very quickly. With my method, the Person table is queried once.