ORA-01795, limit on in clause

遇到问题:

        实施过程中 oracle 报错 ORA-01795, limit on in clause, 发现是SQL  IN 语句中的条件从ZZ表中拼接而成。 

SELECT *
FROM T1
WHERE T1.VAL in ('1111',
		 '2222',
                 ... more than 1000 here
                );

相关调整方案:

-- 1 子查询1
SELECT VAL
FROM T1
WHERE T1. VAL IN (
		   SELECT VAL
                   FROM T2
		 );
-- 2. 子查询2
SELECT VAL
FROM ( 
	SELECT DISTINCT VAL
	FROM T2
     ) B
WHERE B.VAL=A.VAL


-- 3. JOIN 
SELECT VAL
FROM (
	SELECT DISTINCT VAL
	FROM T2
     ) B
JOIN  A
ON B.VAL=A.VAL


-- 4. EXISTS
SELECT VAL
FROM T1 A
WHERE EXISTS (
		SELECT 1 
		FROM T2 
		WHERE A.VAL=VAL
	     );

使用IN还是EXISTS 

以下引用oracle  ASK TOM 中描述:   

SELECT * 
FROM T1 
WHERE X IN (
  	     SELECT Y 
	     FROM T2
 	   );
SELECT * 
FROM T1 
WHERE EXISTS ( 
	       SELECT NULL 
               FROM T2 
               WHERE Y = X 
	     )

  •      如果子查询中T2 的结果很小 IN通常比较合适
  •      如果子查询T2结果很大(huge)但是T1数据较小并且T2 在y上有 生效的索引, exists会更快, 因为全表扫描T1并且使用T2索引,会比全表扫描T2 使用的时间少

 

参考:

  ASK TOM IN & EXISTS

Be First to Comment

发表评论

电子邮件地址不会被公开。 必填项已用*标注