原文引自: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/