HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

各种语句的不同写法

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2006-04-07 14:50:13

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

         最近处理的问题涉及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.B

1.1.4      INTERSECT -> JOIN

SELECT B FROM TT1
INTERSECT
SELECT B FROM TT2;

 

=>

 

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

1.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_AJHASH_AJNL_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;

 

T1T2都比较大,且T2A上有索引时,第二个写法效率很高。当然,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

);

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat