各种语句的不同写法
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
最近处理的问题涉及SQL Tuning的东西比较多。不少语句不是加几个索引这么简单，而是语句是在太复杂了，有些作者都不知道是谁，逻辑非常难理解。碰到这种情况着实令人头疼。但是根据经验，很多语句的书写方式是可以用其他方式代替，通过尝试修改语句的写法，往往取得不错的效果。 当然，改变语句的写法只是语句优化的一种手段之一，在这个基础上，在结合其他的优化手段（采用PLSQL块提花单个语句、采用游标、提示等）才能取得最好的优化效果。以下的替换中，大多数情况下，右边的写法会优于左边的写法，并且根据等价原理进行互换，可以在复杂语句中组合成多种写法：
1.1.1 OR -> IN
SELECT B
FROM T
WHERE C = 1
OR C = 2;
=>
SELECT B
FROM T
WHERE C IN (1,2);
1.1.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);
1.1.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.B1.1.4 INTERSECT -> JOIN
SELECT B FROM TT1
INTERSECT
SELECT B FROM TT2;
=>
SELECT DISTINCT T1.B FROM T1, T2
WHERE T1.B=T2.B1.1.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);
1.1.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);
1.1.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);
1.1.8 NOT IN/NO EXISTS -> OUTER JOIN + IS 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;
1.1.9 NOT IN/NO EXISTS -> NOT IN + /*+ HASH_AJ */
SELECT B
FROM T1
WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);
=>
SELECT /*+ HASH_AJ */B
FROM T1
WHERE C NOT IN (SELECT C FROM T2 WHERE A=’ccc’);
优化器缺省是用嵌套循环来处理anti-joins的，但是如果使用了MERGE_AJ、HASH_AJ、NL_AJ提示，NOT IN能够被转换为一个排序合并或hash anti-join。
1.1.10 OUTER JOIN -> 子查询
SELECT T1.B, T2.C
FROM T1, T2
WHERE T1.A = T2.A(+);
=>
SELECT T1.B,
(SELECT T2.C FROM T2 WHERE T1.A = T2.A)
FROM T1;
当T1和T2都比较大，且T2的A上有索引时，第二个写法效率很高。当然，T2只能返回一个字段
SELECT T1.B, T2.C, T2.B
FROM T1, T2
WHERE T1.A = T2.A(+);
=>
SELECT B, to_number(substr(T2_DATA, 1 10)) T2_C, substr(T2_DATA, 11) T2_A
FROM
(
SELECT T1.B,
(SELECT NVL(to_char(T2.C, fm0000000009),rpad(‘ ’, 10))||T2.B FROM T2 WHERE T1.A = T2.A) T2_DATA
FROM T1
);
=>
CREATE OR REPLACE TYPE TYPE_A_C AS OBJECT (A T2.A%TYPE, C T2.C%TYPE);
/
SELECT B, T2_DATA.A T2_A, T2_DATA.C T2_C
FROM
(
SELECT T1.B,
(SELECT TYPE_A_C(T2.A, T2.C) FROM T2 WHERE T1.A = T2.A) T2_DATA
FROM T1
);
1.1.11 条件中的子查询 -> 谓词中的子查询
SELECT *
FROM T1
WHERE T1.A = (SELECT MAX(T2.A) FROM T2 WHERE T1.B=T2.B)
=>
SELECT T1.*
FROM T1, (SELECT T2.B, MAX(T2.A) T2_A FROM T2 GROUP BY B) T22
WHERE T1.B = T22.B
AND T1.A = T22.T2_A
1.1.12 GROUP BY
GROUP BY在不同情况下可以用不同方式转换：
1.1.12.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;
1.1.12.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;
1.1.12.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);
1.1.12.4 分析函数
SELECT C， MAX(A)
FROM T
GROUP BY C;
=>
SELECT DISTINCT C, MAX(A) OVER (PARTITION BY C)
FROM T
1.1.12.5 HAVING -> WHERE
SELECT C
FROM T
GROUP BY C
HAVING B < 3;
=>
SELECT C
FROM T
WHERE B < 3;
GROUP BY C;
1.1.13 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);
1.1.14 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
1.1.15 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
);