insert into e_rel_customer(
CUSTOM_ID
,REL_CUSTOM_ID
,REL_ID
,CUSTOM_NAME
,idtype_id
)
select
fun_getcustid(CUSTOMERID) as CUSTOMERID
,fun_getcustid(RELATIVEID) as RELATIVEID
,RELATIONSHIP
, CUSTOMERNAME
,CERTTYPE
from xd_CUSTOMER_RELATIVE;
function :
fun_getcustid 为
select nvl(MFCUSTOMERID,'') into refstr from SOR_XD_CUSTOMER_INFO where MFCUSTOMERID is not null and CUSTOMERID=trim(var) and rownum=1;
表xd_CUSTOMER_RELATIVE 有108029条记录
表SOR_XD_CUSTOMER_INFO 73047条记录
这个语句执行下来要半个小时，为什么啊。有人救命吗？？
------解决方案--------------------
试试这个，function执行次数太多了，大概这个意思
- SQL code
insert into e_rel_customer(CUSTOM_ID ,REL_CUSTOM_ID ,REL_ID ,CUSTOM_NAME ,idtype_id )SELECT T1.MFCUSTOMERID AS CUSTOMERID, T2.MFCUSTOMERID AS RELATIVEID, RELATIONSHIP, CUSTOMERNAME, CERTTYPE FROM XD_CUSTOMER_RELATIVE, LEFT JOIN (SELECT NVL(MAX(MFCUSTOMERID), '') MFCUSTOMERID, CUSTOMERID FROM SOR_XD_CUSTOMER_INFO WHERE MFCUSTOMERID IS NOT NULL GROUP BY CUSTOMERID) T1 ON T1.CUSTOMERID = XD_CUSTOMER_RELATIVE.CUSTOMERID LEFT JOIN (SELECT NVL(MAX(MFCUSTOMERID), '') MFCUSTOMERID, CUSTOMERID FROM SOR_XD_CUSTOMER_INFO WHERE MFCUSTOMERID IS NOT NULL GROUP BY CUSTOMERID) T2 ON T2.CUSTOMERID = XD_CUSTOMER_RELATIVE.RELATIVEID