ORACLE SQL常用优化方法

1       查询sql优化

1.1    选择最有效率的表名顺序（只在基于规则的优化器中有效ORACLE）

解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACEL处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如：表TAB1 16,384条记录

表TAB2 1条记录

选择TAB2作为基础表（最好的方法）

Select count(*) from tab1,tab2 执行时间0.96秒

选择TAB2作为基础表（不佳方法）

Select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

例如：EMP表描述了LOCATION表和CATEGORY表的交集

SELECT *

FROM LOCATION L，

          CATEGORY C，

          EMP E

WHERE E.EPM_NO BETWEEN 1000 AND 2000

AND E.CAT_NO=C.CAT_NO

AND E.LOCN=L.LOCN

将比下列SQL更有效率

SELECT *

FROM EMP E，

LOCATION L，

          CATEGORY C

WHERE E.EPM_NO BETWEEN 1000 AND 2000

AND E.CAT_NO=C.CAT_NO

AND E.LOCN=L.LOCN

 

1.2    减少访问数据库的次数

当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据的次数，就能实际上减少ORACLE的工作量。

例如：

以下有三种方法可以检索出雇员号等于0342或0291的职员。

 

方法1（最低效）

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO=342;

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO=29;

 

方法2（高效）

SELECT A.EMP_NAME,A.SALARY,A.GRADE,

           B.EMP_NAME,B.SALARY,B.GRADE

FORM EMP A,EMP B

WHERE A.EMP_NO=342

AND B.EMP_NO=29

 

1.3    减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。例如：

低效

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME=(SELECT TAB_NAME

                                    FROM TAB_COLUMNS

                     WHERE VERSION=604)

AND DB_VER=(SELECT DB_VER

               FROM TAB_COLUMNS

               WHERE VERSION=604)

高效

SELECT TAB_NAME

FROM TABlES

WHERE (TAB_NAME,DB_VER)

               =(SELECT TAB_NAME,DB_VER)

          FROM TAB_COLUMNS

          WHERE VERSION= 604)

低效

UPDATE EMP

SET EMP_CAT=(SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),

SAL_RANGE=(SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT=0020;

高效：

UPDATE EMP

SET(EMP_CAT,SAL_RANGE)

=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)

FROM EMP_CATEGORIES)

WHERE EMP_DEPT-0020;

 

1.4    SELECT子句中避免使用*

当你想在SELECT子句中列出所有COLUMN时，使用动态SQL列引用‘*’是一个方便的方法，不幸的是，这是一个非常低效的方法。实际上。ORACLE在解析的过程中，会将‘*’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

 

1.5    用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。

低效

SELECT *

FROM EMP（基础表）

WHERE EMPNO>0

AND DEPTNO IN(SELECT DEPTNO

                FROM DEPT

                WHERE LOC=’MELB’)

高效

SELECT *

FROM EMP（基础表）

WHERE EMPNO>0

AND EXISTS(SELECT ‘X’

            FROM DEPT

            WHERE DEPT.DEPTNO=EMP.DEPTNO

             AND LOC=’MELB’)

 

 

【关于exists和in的区别】

exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因，换成"select 6"完全一样，当然也可以select字段，但是明显效率低些)

其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.

in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.因此，IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。通常情况下采用exists要比in效率高。

 

【 in与exists执行效率】

in数据量少效率还可以,数据量大就效率低

exists的效率依赖于匹配度。

1.6    用NOT EXISTS替代NOTIN

在子查询中，NOTIN子句将执行一个内部排序和合并，无论在哪种情况下，NOTIN都是最低效的（因为它对子查询中的表执行了一个全表遍历），为了避免使用NOTIN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。

例如：

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN(SELECT DEPT_NO

                        FROM DEPT

                        WHERE DEPT_CAT=’A’);

为了提高效率改写为：

（方法一：高效）

SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO=B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+)=’A’

（方法二：最高效）

SELECT ….

FROM EMP E

WHERE NOT EXISTS(SELECT ‘X’

                   FROM DEPT D

                   WHERE D.DEPT_NO =E.DEPT_NO

                   AND DEPT_CAT=’A’)

 

1.7    WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句末尾。

例如：

（低效，执行时间156.3秒）

SELECT …

FROM EMP E

WHERE SAL>50000

AND JOB=’MANAGER’

AND 25<(SELECT COUNT(*) FROM EMP WHERE MGR=E。EMPNO)；

(高效，执行时间10.6秒)

SELECT …

FROM EMP E

WHERE 25<(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)

AND SAL>50000

AND JOB=’MANAGER’;

 

2       操作sql优化

2.1    删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID >(SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

 

 

 

3       其他方式优化

3.1    使用表的别名（Alias）

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。