筛选重复数据速度比较

SELECT     
A.CUSTOMER_CONNECTION_CODE,     
A.CUSTOMER_NAME,     
A.DEALER_CONNECTION_CODE,    
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE  
              P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)  
THEN '1' ELSE '0' END AS EXIST_FLG, 
A.INSERT_DATE  
FROM     
KAMS_M_CUSTOMER A     
WHERE (DEALER_CONNECTION_CODE,CUSTOMER_NAME,TEL) IN     
(SELECT DEALER_CONNECTION_CODE,CUSTOMER_NAME,TEL     
FROM     
  KAMS_M_CUSTOMER CUS     
WHERE TEL IS NOT NULL    
GROUP BY DEALER_CONNECTION_CODE,CUSTOMER_NAME,TEL     
HAVING COUNT(1) > 1)  
UNION   
SELECT     
A.CUSTOMER_CONNECTION_CODE,     
A.CUSTOMER_NAME,     
A.DEALER_CONNECTION_CODE,    
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE  
              P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)  
THEN '1' ELSE '0' END AS EXIST_FLG, 
A.INSERT_DATE  
FROM     
KAMS_M_CUSTOMER A     
WHERE (DEALER_CONNECTION_CODE,CUSTOMER_NAME,PORTABLE_TEL1) IN     
(SELECT DEALER_CONNECTION_CODE,CUSTOMER_NAME,PORTABLE_TEL1     
FROM     
  KAMS_M_CUSTOMER CUS     
WHERE PORTABLE_TEL1 IS NOT NULL    
GROUP BY DEALER_CONNECTION_CODE,CUSTOMER_NAME,PORTABLE_TEL1     
HAVING COUNT(1) > 1)    
ORDER BY CUSTOMER_NAME,DEALER_CONNECTION_CODE,EXIST_FLG DESC,INSERT_DATE ASC 

查询出4674条数据 共用时0.6秒

SELECT   
A.CUSTOMER_CONNECTION_CODE,   
A.CUSTOMER_NAME,   
A.DEALER_CONNECTION_CODE,  
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE
              P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)
THEN '1' ELSE '0' END AS EXIST_FLG, 
A.INSERT_DATE  
FROM   
KAMS_M_CUSTOMER A   
WHERE EXISTS   
(SELECT 1   
FROM   
  KAMS_M_CUSTOMER CUS   
WHERE CUS.PORTABLE_TEL1 IS NOT NULL AND A.PORTABLE_TEL1 = CUS.PORTABLE_TEL1
AND A.CUSTOMER_CONNECTION_CODE <> CUS.CUSTOMER_CONNECTION_CODE
AND A.DEALER_CONNECTION_CODE = CUS.DEALER_CONNECTION_CODE
AND A.CUSTOMER_NAME = CUS.CUSTOMER_NAME)  
UNION
SELECT   
A.CUSTOMER_CONNECTION_CODE,   
A.CUSTOMER_NAME,   
A.DEALER_CONNECTION_CODE,  
CASE WHEN EXISTS (SELECT 1 FROM RETENTION_PRODUCT_D P WHERE
              P.CUSTOMER_CONNECTION_CODE = A.CUSTOMER_CONNECTION_CODE)
THEN '1' ELSE '0' END AS EXIST_FLG, 
A.INSERT_DATE  
FROM   
KAMS_M_CUSTOMER A   
WHERE EXISTS      
(SELECT 1   
FROM   
  KAMS_M_CUSTOMER CUS   
WHERE CUS.TEL IS NOT NULL AND A.TEL = CUS.TEL
AND A.CUSTOMER_CONNECTION_CODE <> CUS.CUSTOMER_CONNECTION_CODE
AND A.DEALER_CONNECTION_CODE = CUS.DEALER_CONNECTION_CODE
AND A.CUSTOMER_NAME = CUS.CUSTOMER_NAME)
ORDER BY CUSTOMER_NAME,DEALER_CONNECTION_CODE,EXIST_FLG DESC,INSERT_DATE ASC 

查询出4674条数据 共用时0.5秒