各种语句的不同写法

原文引自:http://fuyuncat.itpub.net/post/5203/56053

各种语句的不同写法

fuyuncat

最近处理的问题涉及SQL Tuning的东西比较多。不少语句不是加几个索引这么简单，而是语句是在太复杂了，有些作者都不知道是谁，逻辑非常难理解。碰到这种情况着实令人头疼。但是根据经验，很多语句的书写方式是可以用其他方式代替，通过尝试修改语句的写法，往往取得不错的效果。

当然，改变语句的写法只是语句优化的一种手段之一，在这个基础上，在结合其他的优化手段（采用PLSQL块提花单个语句、采用游标、提示等）才能取得最好的优化效果。以下的替换中，大多数情况下，右边的写法会优于左边的写法，并且根据等价原理进行互换，可以在复杂语句中组合成多种写法：

1 OR -> IN

SELECT B

FROM T

WHERE C = 1

OR C = 2;

=>

SELECT B

FROM T

WHERE C IN (1,2);

2 IN -> EXISTS

SELECT B

FROM T1

WHERE C IN (SELECT C FROM T2 WHERE A=’aaa’);

=>

SELECT B

FROM T1

WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);

3 IN -> JOIN

SELECT B FROM T1
WHERE B IN (SELECT B FROM T2);

=>

SELECT DISTINCT T1.B FROM T1, T2
WHERE T1.B=T2.B

4 INTERSECT -> JOIN

SELECT B FROM TT1
INTERSECT
SELECT B FROM TT2;

=>

SELECT DISTINCT T1.B FROM T1, T2
WHERE T1.B=T2.B

5 DISTINCT -> EXISTS

SELECT DISTINCT T1.B

FROM T1, T2

WHERE T2.A=’aaa’

and T1.C = T2.C;

WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);

=>

SELECT B

FROM T1

WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);

6 EXISTS -> JOIN

SELECT B

FROM T1

WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);

=>

SELECT T1.B

FROM T1, T2

WHERE T2.A=’aaa’

and T1.C = T2.C;

WHERE EXISTS (SELECT 1 FROM T2 WHERE A=’aaa’ and T1.C = T2.C);

7 NOT IN -> NOT EXISTS

SELECT B

FROM T1

WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);

=>

SELECT B

FROM T1

WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE A=’ccc’ AND T1.B = T2.B);

8 NOT IN/NO EXISTS -> OUTER JOIN + IS NOT NULL

SELECT B

FROM T1

WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);

=>

SELECT B

FROM T1 Ta,

(SELECT C FROM T2 WHERE A=’ccc’) Tb

WHERE Ta.C = Tb.C(+)

AND Tb.C IS NULL;

9 GROUP BY

GROUP BY在不同情况下可以用不同方式转换：

9.1 DECODE

SELECT B, COUNT(B)

FROM T

GROUP BY B;

假如A的取值范围为1,2,3

=>

SELECT COUNT(DECODE(B,1,’X’,NULL)) AS “B1”,

COUNT(DECODE(B,2,’X’,NULL)) AS “B2”,

COUNT(DECODE(B,3,’X’,NULL)) AS “B3”

FROM T;

9.2 MINUS

SELECT C

FROM T

GROUP BY C

HAVING MAX(B) < 3

=>

SELECT C

FROM T

WHERE B < 3

MINUS

SELECT C

FROM T

WHERE B >=3;

9.3 JOIN

SELECT C

FROM T

GROUP BY C

HAVING COUNT(B) > 1;

=>

SELECT DISTINCT C

FROM T t1

WHERE ROWID > (SELECT MIN(ROWID) FROM T t2 WHERE t1.C = t2.C);

10 HAVING -> WHERE

SELECT C

FROM T

GROUP BY C

HAVING B < 3;

=>

SELECT C

FROM T

WHERE B < 3;

GROUP BY C;

11 MINUS -> NOT IN

SELECT B

FROM T1

WHERE A>1

MINUS

SELECT C

FROM T2

WHERE A>2;

=>

SELECT DISTINCT B

FROM T1

WHERE A>1

AND B NOT IN (SELECT B FROM T2 WHERE A>2);

12 OR -> UNION

SELECT B

FROM T

WHERE AIS NULL

OR A=2

=>

SELECT B

FROM T

WHERE A IS NULL

UNION

SELECT B

FROM T

WHERE A=2

13 UNION -> UNION ALL + DISTINCT

SELECT B

FROM T1

WHERE A>1

UNION

SELECT B

FROM T2

WHERE A<3;

=>

SELECT DISTINCT B

FROM

(

SELECT B

FROM T1

WHERE A>1

UNION ALL

SELECT B

FROM T2

WHERE A<3

);

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12482/viewspace-864994/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12482/viewspace-864994/