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条记录
这个语句执行下来要半个小时,为什么啊。有人救命吗??
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条记录
这个语句执行下来要半个小时,为什么啊。有人救命吗??
解决方案 »
- 关于动态SQL的问题
- oracle数据库中添加删除约束的问题
- Oracle中执行动态SQL语句的话,SQL语句只能声明为varchar2类型吗?
- oracle 删除表
- 讨论一下,oracle查询优化方案,哪个好,
- Oracle 创建触发器(急急急)
- 异常处理捕捉不到错误消息
- 创建表空间时出错:'c:\oracle\oradata\cnc\lgy.ora'出错,无法创建文件,无法打开文件,系统找不到指定的路径!
- 一个思路问题?不知道对不对,而且不知道sql能不能实现
- 熟悉C#和Oracle的高手看过来
- 怎么往磁盘阵列上装ORACLE
- ---------建立表分区后,一般都把对应的index也分区么?----------
最少返回数据应该是 108029 行
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