在写这篇文章之前，xxx已经写过了几篇关于改查询语句主题的文章,想要了解的朋友可以去翻一下之前的文章
No SQL,No cost. SQL语句是成造数据库销开最大的部份。而不良SQL写法直接致导数据库系统性能下降的况情是皆比比。那么如何才能称得
上高效的SQL语句呢？一是查询优化器为当前的SQL语句成生最好的执行计划，证保数据读写应用最好路径；二是置设理合的物理存储构结，如表
的类型，字段的序顺，字段的数据类型等。本文重要描述如何编写高效的SQL语句并给出示例。面下的描述重要分为三个部份，一是编写高效SQL
语句，二是应用索引高提查询性能的部份，三是结总部份。
一、编写高效SQL语句
[sql]
- 1) 择选最有效的表名序顺(仅适于用RBO模式)
- ORACLE的剖析器是总按照从右到左的序顺处置FROM句子中的表名，因此FROM句子中最后的一个表将作为驱动表被优先处置。当FROM句子
- 存在多个表的时候，应该虑考将表上录记起码的那个表置于FROM的最右端作为基表。Oracle会首先描扫基表(FROM句子中最后的那个表)并对
- 录记停止排序，然后描扫第二个表(FROM句子中最后第二个表)，最后将有所从第二个表中检索出的录记与第一个表中适合录记停止合并。如
- 果有3个以上的表连接查询， 那就要需择选叉交表（intersection table）作为基本表，叉交表是指那个被其他表所引用的表。
- 面下的例子应用最常见的scott或hr模式下的表停止演示
- 表 EMP 有14条录记
- 表 DEPT 有4条录记
- SELECT /*+ rule */ COUNT( * ) FROM emp, dept; --高效的写法
- scott@CNMMBO> set autotrace traceonly stat;
- scott@CNMMBO> SELECT /*+ rule */ COUNT( * ) FROM emp, dept;
- Elapsed: 00:00:00.14
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 35 consistent gets
- 0 physical reads
- 0 redo size
- 515 bytes sent via SQL*Net to client
- 492 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- SELECT /*+ rule */ COUNT( * ) FROM dept, emp; --低效的写法
- scott@CNMMBO> SELECT /*+ rule */ COUNT( * ) FROM dept, emp;
- Elapsed: 00:00:00.02
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 105 consistent gets
- 0 physical reads
- 0 redo size
- 515 bytes sent via SQL*Net to client
- 492 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- 2) select 查询中免避应用'*'
- 当你想在SELECT句子中列出有所的COLUMN时,应用动态SQL列引用 '*' 是一个便利的方法.幸不的是,这是一个非常低效的方法.现实
- 上，ORACLE在剖析的过程当中， 会将 '*' 次依转换成有所的列名， 这个任务是通过查询数据字典成完的， 这意味着将消耗更多的间时。
- 注：本文中的例子出于简化演示而应用了select * ,生产境环应免避应用.
- 3) 加增拜访数据库的数次
- 每当执行一条SQL语句,Oracle 要需成完大批的外部作操,象剖析SQL语句,预算索引的利用率,绑定变量, 读数据块等等.由此可
- 见，加增拜访数据库的数次，现实上是降低了数据库系统销开
- -->面下通过3种方法来得获员雇编号为7788与7902的关相息信
- -->方法 1 (最低效):
- select ename,job,sal from emp where empno=7788;
- select ename,job,sal from emp where empno=7902;
- -->方法 2 (次低效):
- -->面下应用了参数游标来成完，每递传一次参数则要需对表emp拜访一次，加增了I/O
- DECLARE
- CURSOR C1(E_NO NUMBER) IS
- SELECT ename, job, sal
- FROM emp
- WHERE empno = E_NO;
- BEGIN
- OPEN C1 (7788);
- FETCH C1 INTO …, …, …;
- ..
- OPEN C1 (7902);
- FETCH C1 INTO …, …, …;
- CLOSE C1;
- END;
- -->方法 3 (最高效)
- SELECT a.ename
- , a.job
- , a.sal
- , b.ename
- , b.job
- , b.sal
- FROM emp a, emp b
- WHERE a.empno = 7788 OR b.empno = 7902;
- 注意：在SQL*Plus,SQL*Forms和Pro*C中新重置设ARRAYSIZE参数,可以加增每数次据库拜访的检索数据量,提议值为200.
- 4) 应用DECODE函数来加增处置间时
- -->应用decode函数可以免避重复描扫雷同的行或重复连接雷同的表
- select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%';
- select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%';
- -->通过应用decode函数一次描扫可即成完有所足满件条录记的处置
- SELECT COUNT( DECODE( deptno, 20, 'x', NULL ) ) d20_count
- , COUNT( DECODE( deptno, 30, 'x', NULL ) ) d30_count
- , SUM( DECODE( deptno, 20, sal, NULL ) ) d20_sal
- , SUM( DECODE( deptno, 30, sal, NULL ) ) d30_sal
- FROM emp
- WHERE ename LIKE 'SMITH%';
- 似类的，DECODE函数也可以运于用GROUP BY 和ORDER BY句子中。
- 5) 整合单简，无关联的数据库拜访
- -->如果你有几个单简的数据库查询语句，你可以把它们整合到一个查询中以高提性能(即使它们之间没有关系)
- -->整合前
- SELECT name
- FROM emp
- WHERE empno = 1234;
- SELECT name
- FROM dept
- WHERE deptno = 10;
- SELECT name
- FROM cat
- WHERE cat_type = 'RD';
- -->整合后
- SELECT e.name, d.name, c.name
- FROM cat c
- , dpt d
- , emp e
- , dual x
- WHERE NVL( 'X', x.dummy ) = NVL( 'X', e.ROWID(+) )
- AND NVL( 'X', x.dummy ) = NVL( 'X', d.ROWID(+) )
- AND NVL( 'X', x.dummy ) = NVL( 'X', c.ROWID(+) )
- AND e.emp_no(+) = 1234
- AND d.dept_no(+) = 10
- AND c.cat_type(+) = 'RD';
- -->从上面的SQL语句可以看出，尽管三条语句被整合为一条，性能得以高提，然可读性差，此时应权衡性能与价值
- 6) 删除重复录记
- -->通过应用rowid来作为过滤件条，性能高效
- DELETE FROM emp e
- WHERE e.ROWID > (SELECT MIN( x.ROWID )
- FROM emp x
- WHERE x.empno = e.empno);
- 7) 应用truncate 取代 delete
- -->常通况情下,意任录记的删除要需在回滚段结构删除前像镜以实现回滚(rollback).对于未交提的数据在执行rollback以后,Oracle会成生
- -->等价SQL语句去复恢录记(如delete,则成生对应的insert语句;如insert则成生对应的delete;如update,则是同时成生delete和insert
- -->应用truncate命令则是执行DDL命令,不生产任何回滚息信,直接格式化并释放高水线位.故该语句性能高效.由于不能rollback,因此慎用.
- 8) 尽量多应用COMMIT(COMMIT应确保事务的完整性)
- -->只要有可能，在序程中尽量多应用COMMIT,这样序程的性能到得高提,需求也会因为COMMIT所释放的资源而加增
- -->COMMIT所释放的资源：
- -->1.回滚段上于用复恢数据的息信
- -->2.释放语句处置期间所持有的锁
- -->3.释放redo log buffer占用的间空(commit将redo log buffer中的entries 写入到联机重做日志文件)
- -->4.ORACLE为理管上述3种资源中的外部销开
- 9) 盘算录记条数
- -->一般的况情下,count(*)比count(1)稍快.如果可以通过索引检索,对索引列的计数是最快的,因为直接描扫索引可即,例如COUNT(EMPNO)
- -->现实况情是经试测上述三种况情并无显著异差.
- 10) 用Where句子替换HAVING句子
- -->尽可能的免避having句子，因为HAVING 句子是对检索出有所录记以后再对结果集停止过滤。这个处置要需排序，总计等作操
- -->通过WHERE句子则在组分之前可即过滤不必要的录记目数，从而加增聚合的销开
- -->低效:
- SELECT deptno, AVG( sal )
- FROM emp
- GROUP BY deptno
- HAVING deptno = 20;
- scott@CNMMBO> SELECT deptno, AVG( sal )
- 2 FROM emp
- 3 GROUP BY deptno
- 4 HAVING deptno= 20;
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 7 consistent gets
- 0 physical reads
- 0 redo size
- 583 bytes sent via SQL*Net to client
- 492 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- -->高效:
- SELECT deptno, AVG( sal )
- FROM emp
- WHERE deptno = 20
- GROUP BY deptno;
- scott@CNMMBO> SELECT deptno, AVG( sal )
- 2 FROM emp
- 3 WHERE deptno = 20
- 4 GROUP BY deptno;
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 2 consistent gets
- 0 physical reads
- 0 redo size
- 583 bytes sent via SQL*Net to client
- 492 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- 11) 最小化表查询数次
- -->在含有子查询的SQL语句中，要特别注意加增对表的查询
- -->低效:
- SELECT *
- FROM employees
- WHERE department_id = (SELECT department_id
- FROM departments
- WHERE department_name = 'Marketing')
- AND manager_id = (SELECT manager_id
- FROM departments
- WHERE department_name = 'Marketing');
- -->高效:
- SELECT *
- FROM employees
- WHERE ( department_id, manager_id ) = (SELECT department_id, manager_id
- FROM departments
- WHERE department_name = 'Marketing')
- -->似类更新多列的况情
- -->低效:
- UPDATE employees
- SET job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )
- WHERE department_id = 10;
- -->高效:
- UPDATE employees
- SET ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )
- WHERE department_id = 10;
- 12) 应用表别名
- -->在多表查询时，为所返回列应用表别名作为前缀以加增剖析间时以及那些雷同列歧义起引的语法错误
- 13) 用EXISTS取代IN
- 在一些基于基本表的查询中,为了足满一个件条,常常要需对另一个表停止联接.在这类况情下,应用EXISTS(或NOT EXISTS)常通
- 将高提查询的效率.
- -->低效:
- SELECT *
- FROM emp
- WHERE sal > 1000
- AND deptno IN (SELECT deptno
- FROM dept
- WHERE loc = 'DALLAS')
- -->高效:
- SELECT *
- FROM emp
- WHERE empno > 1000
- AND EXISTS
- (SELECT 1
- FROM dept
- WHERE deptno = emp.deptno AND loc = 'DALLAS')
- 14) 用NOT EXISTS取代NOT IN
- 在子查询中,NOT IN句子起引一个外部的排序与合并.因此,无论何时NOT IN句子都是最低效的,因为它对子查询中的表执行了一个全表
- 遍历.为免避该况情,应该将其改写成外部连接(OUTTER JOIN)或实用NOT EXISTS
- -->低效:
- SELECT *
- FROM emp
- WHERE deptno NOT IN (SELECT deptno
- FROM dept
- WHERE loc = 'DALLAS');
- -->高效:
- SELECT e.*
- FROM emp e
- WHERE NOT EXISTS
- (SELECT 1
- FROM dept
- WHERE deptno = e.deptno AND loc = 'DALLAS');
- -->最高效(尽管面下的查询最高效,不并推荐应用,因为列loc应用了不等算运,当表dept数据量较大,且loc列存在索引的话,则此时索引效失)
- SELECT e.*
- FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
- WHERE d.loc <> 'DALLAS'
- 15) 应用表连接替换EXISTS
- 一般况情下，应用表连接比EXISTS更高效
- -->低效:
- SELECT *
- FROM employees e
- WHERE EXISTS
- (SELECT 1
- FROM departments
- WHERE department_id = e.department_id AND department_name = 'IT');
- -->高效:
- SELECT * -->经试测此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果分歧
- FROM employees e INNER JOIN departments d ON d.department_id = e.department_id
- WHERE d.department_name = 'IT';
- 16) 用EXISTS替换DISTINCT
- 对于一对多关系表息信查询时(如部门表和员雇表)，应免避在select 句子中应用distinct,而应用exists来替换
- -->低效:
- SELECT DISTINCT e.department_id, d.department_name
- FROM departments d INNER JOIN employees e ON d.department_id = e.department_id;
- -->高效:
- SELECT d.department_id,department_name
- from departments d
- WHERE EXISTS
- (SELECT 1
- FROM employees e
- WHERE d.department_id=e.department_id);
- EXISTS 使查询更为速迅，因为RDBMS核心块模将在子查询的件条一旦足满后，立刻返回结果
- -->经试测此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果分歧
- 17) 应用 UNION ALL 替换 UNION(如果有可能的话)
- 当SQL语句要需UNION两个查询结果集时，这两个结果集合会以UNION-ALL的方法被合并， 然后在输出最终结果前停止排序。
- 如果用UNION ALL取代UNION， 这样排序就不是必要了。 效率就会因此到得高提。
- 注意：
- UNION ALL会输出有所的结果集,而UNION则过滤掉重复录记并对其停止排序.因此在应用时应虑考业务逻辑是否允许当前的结果集存在重复现象
- 寻找低效的SQL语句
- -->面下的语句重要适于用从视图v$sqlarea中得获当前运行下且耗用buffer_gets较多的SQL语句
- SELECT executions
- , disk_reads
- , buffer_gets
- , ROUND( ( buffer_gets
- - disk_reads )
- / buffer_gets, 2 )
- hit_ratio
- , ROUND( disk_reads / executions, 2 ) reads_per_run
- , sql_text
- FROM v$sqlarea
- WHERE executions > 0
- AND buffer_gets > 0
- AND ( buffer_gets
- - disk_reads )
- / buffer_gets < 0.80
- ORDER BY 4 DESC;
- 18) 尽可能免避应用函数，函数会致导更多的 recursive calls
1) 择选最有效的表名序顺(仅适于用RBO模式)
ORACLE的剖析器是总按照从右到左的序顺处置FROM句子中的表名，因此FROM句子中最后的一个表将作为驱动表被优先处置。当FROM句子
存在多个表的时候，应该虑考将表上录记起码的那个表置于FROM的最右端作为基表。Oracle会首先描扫基表(FROM句子中最后的那个表)并对
录记停止排序，然后描扫第二个表(FROM句子中最后第二个表)，最后将有所从第二个表中检索出的录记与第一个表中适合录记停止合并。如
果有3个以上的表连接查询， 那就要需择选叉交表（intersection table）作为基本表，叉交表是指那个被其他表所引用的表。
面下的例子应用最常见的scott或hr模式下的表停止演示
表 EMP 有14条录记
表 DEPT 有4条录记
SELECT /*+ rule */ COUNT( * ) FROM emp, dept; --高效的写法
scott@CNMMBO> set autotrace traceonly stat;
scott@CNMMBO> SELECT /*+ rule */ COUNT( * ) FROM emp, dept;
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SELECT /*+ rule */ COUNT( * ) FROM dept, emp; --低效的写法
scott@CNMMBO> SELECT /*+ rule */ COUNT( * ) FROM dept, emp;
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
105 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2) select 查询中免避应用'*'
当你想在SELECT句子中列出有所的COLUMN时,应用动态SQL列引用 '*' 是一个便利的方法.幸不的是,这是一个非常低效的方法.现实
上，ORACLE在剖析的过程当中， 会将 '*' 次依转换成有所的列名， 这个任务是通过查询数据字典成完的， 这意味着将消耗更多的间时。
注：本文中的例子出于简化演示而应用了select * ,生产境环应免避应用.
3) 加增拜访数据库的数次
每当执行一条SQL语句,Oracle 要需成完大批的外部作操,象剖析SQL语句,预算索引的利用率,绑定变量, 读数据块等等.由此可
见，加增拜访数据库的数次，现实上是降低了数据库系统销开
-->面下通过3种方法来得获员雇编号为7788与7902的关相息信
-->方法 1 (最低效):
select ename,job,sal from emp where empno=7788;
select ename,job,sal from emp where empno=7902;
-->方法 2 (次低效):
-->面下应用了参数游标来成完，每递传一次参数则要需对表emp拜访一次，加增了I/O
DECLARE
CURSOR C1(E_NO NUMBER) IS
SELECT ename, job, sal
FROM emp
WHERE empno = E_NO;
BEGIN
OPEN C1 (7788);
FETCH C1 INTO …, …, …;
..
OPEN C1 (7902);
FETCH C1 INTO …, …, …;
CLOSE C1;
END;
-->方法 3 (最高效)
SELECT a.ename
, a.job
, a.sal
, b.ename
, b.job
, b.sal
FROM emp a, emp b
WHERE a.empno = 7788 OR b.empno = 7902;
注意：在SQL*Plus,SQL*Forms和Pro*C中新重置设ARRAYSIZE参数,可以加增每数次据库拜访的检索数据量,提议值为200.
4) 应用DECODE函数来加增处置间时
-->应用decode函数可以免避重复描扫雷同的行或重复连接雷同的表
select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%';
select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%';
-->通过应用decode函数一次描扫可即成完有所足满件条录记的处置
SELECT COUNT( DECODE( deptno, 20, 'x', NULL ) ) d20_count
, COUNT( DECODE( deptno, 30, 'x', NULL ) ) d30_count
, SUM( DECODE( deptno, 20, sal, NULL ) ) d20_sal
, SUM( DECODE( deptno, 30, sal, NULL ) ) d30_sal
FROM emp
WHERE ename LIKE 'SMITH%';
似类的，DECODE函数也可以运于用GROUP BY 和ORDER BY句子中。
5) 整合单简，无关联的数据库拜访
-->如果你有几个单简的数据库查询语句，你可以把它们整合到一个查询中以高提性能(即使它们之间没有关系)
-->整合前
SELECT name
FROM emp
WHERE empno = 1234;
SELECT name
FROM dept
WHERE deptno = 10;
SELECT name
FROM cat
WHERE cat_type = 'RD';
-->整合后
SELECT e.name, d.name, c.name
FROM cat c
, dpt d
, emp e
, dual x
WHERE NVL( 'X', x.dummy ) = NVL( 'X', e.ROWID(+) )
AND NVL( 'X', x.dummy ) = NVL( 'X', d.ROWID(+) )
AND NVL( 'X', x.dummy ) = NVL( 'X', c.ROWID(+) )
AND e.emp_no(+) = 1234
AND d.dept_no(+) = 10
AND c.cat_type(+) = 'RD';
-->从上面的SQL语句可以看出，尽管三条语句被整合为一条，性能得以高提，然可读性差，此时应权衡性能与价值
6) 删除重复录记
-->通过应用rowid来作为过滤件条，性能高效
DELETE FROM emp e
WHERE e.ROWID > (SELECT MIN( x.ROWID )
FROM emp x
WHERE x.empno = e.empno);
7) 应用truncate 取代 delete
-->常通况情下,意任录记的删除要需在回滚段结构删除前像镜以实现回滚(rollback).对于未交提的数据在执行rollback以后,Oracle会成生
-->等价SQL语句去复恢录记(如delete,则成生对应的insert语句;如insert则成生对应的delete;如update,则是同时成生delete和insert
-->应用truncate命令则是执行DDL命令,不生产任何回滚息信,直接格式化并释放高水线位.故该语句性能高效.由于不能rollback,因此慎用.
8) 尽量多应用COMMIT(COMMIT应确保事务的完整性)
-->只要有可能，在序程中尽量多应用COMMIT,这样序程的性能到得高提,需求也会因为COMMIT所释放的资源而加增
-->COMMIT所释放的资源：
-->1.回滚段上于用复恢数据的息信
-->2.释放语句处置期间所持有的锁
-->3.释放redo log buffer占用的间空(commit将redo log buffer中的entries 写入到联机重做日志文件)
-->4.ORACLE为理管上述3种资源中的外部销开
9) 盘算录记条数
-->一般的况情下,count(*)比count(1)稍快.如果可以通过索引检索,对索引列的计数是最快的,因为直接描扫索引可即,例如COUNT(EMPNO)
-->现实况情是经试测上述三种况情并无显著异差.
10) 用Where句子替换HAVING句子
-->尽可能的免避having句子，因为HAVING 句子是对检索出有所录记以后再对结果集停止过滤。这个处置要需排序，总计等作操
-->通过WHERE句子则在组分之前可即过滤不必要的录记目数，从而加增聚合的销开
-->低效:
SELECT deptno, AVG( sal )
FROM emp
GROUP BY deptno
HAVING deptno = 20;
scott@CNMMBO> SELECT deptno, AVG( sal )
2 FROM emp
3 GROUP BY deptno
4 HAVING deptno= 20;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-->高效:
SELECT deptno, AVG( sal )
FROM emp
WHERE deptno = 20
GROUP BY deptno;
scott@CNMMBO> SELECT deptno, AVG( sal )
2 FROM emp
3 WHERE deptno = 20
4 GROUP BY deptno;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11) 最小化表查询数次
-->在含有子查询的SQL语句中，要特别注意加增对表的查询
-->低效:
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'Marketing')
AND manager_id = (SELECT manager_id
FROM departments
WHERE department_name = 'Marketing');
-->高效:
SELECT *
FROM employees
WHERE ( department_id, manager_id ) = (SELECT department_id, manager_id
FROM departments
WHERE department_name = 'Marketing')
-->似类更新多列的况情
-->低效:
UPDATE employees
SET job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )
WHERE department_id = 10;
-->高效:
UPDATE employees
SET ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )
WHERE department_id = 10;
12) 应用表别名
-->在多表查询时，为所返回列应用表别名作为前缀以加增剖析间时以及那些雷同列歧义起引的语法错误
13) 用EXISTS取代IN
在一些基于基本表的查询中,为了足满一个件条,常常要需对另一个表停止联接.在这类况情下,应用EXISTS(或NOT EXISTS)常通
将高提查询的效率.
-->低效:
SELECT *
FROM emp
WHERE sal > 1000
AND deptno IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS')
-->高效:
SELECT *
FROM emp
WHERE empno > 1000
AND EXISTS
(SELECT 1
FROM dept
WHERE deptno = emp.deptno AND loc = 'DALLAS')
14) 用NOT EXISTS取代NOT IN
在子查询中,NOT IN句子起引一个外部的排序与合并.因此,无论何时NOT IN句子都是最低效的,因为它对子查询中的表执行了一个全表
遍历.为免避该况情,应该将其改写成外部连接(OUTTER JOIN)或实用NOT EXISTS
-->低效:
SELECT *
FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS');
-->高效:
SELECT e.*
FROM emp e
WHERE NOT EXISTS
(SELECT 1
FROM dept
WHERE deptno = e.deptno AND loc = 'DALLAS');
-->最高效(尽管面下的查询最高效,不并推荐应用,因为列loc应用了不等算运,当表dept数据量较大,且loc列存在索引的话,则此时索引效失)
SELECT e.*
FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc <> 'DALLAS'
15) 应用表连接替换EXISTS
一般况情下，应用表连接比EXISTS更高效
-->低效:
SELECT *
FROM employees e
WHERE EXISTS
(SELECT 1
FROM departments
WHERE department_id = e.department_id AND department_name = 'IT');
-->高效:
SELECT * -->经试测此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果分歧
FROM employees e INNER JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name = 'IT';
16) 用EXISTS替换DISTINCT
对于一对多关系表息信查询时(如部门表和员雇表)，应免避在select 句子中应用distinct,而应用exists来替换
-->低效:
SELECT DISTINCT e.department_id, d.department_name
FROM departments d INNER JOIN employees e ON d.department_id = e.department_id;
-->高效:
SELECT d.department_id,department_name
from departments d
WHERE EXISTS
(SELECT 1
FROM employees e
WHERE d.department_id=e.department_id);
EXISTS 使查询更为速迅，因为RDBMS核心块模将在子查询的件条一旦足满后，立刻返回结果
-->经试测此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果分歧
17) 应用 UNION ALL 替换 UNION(如果有可能的话)
当SQL语句要需UNION两个查询结果集时，这两个结果集合会以UNION-ALL的方法被合并， 然后在输出最终结果前停止排序。
如果用UNION ALL取代UNION， 这样排序就不是必要了。 效率就会因此到得高提。
注意：
UNION ALL会输出有所的结果集,而UNION则过滤掉重复录记并对其停止排序.因此在应用时应虑考业务逻辑是否允许当前的结果集存在重复现象
寻找低效的SQL语句
-->面下的语句重要适于用从视图v$sqlarea中得获当前运行下且耗用buffer_gets较多的SQL语句
SELECT executions
, disk_reads
, buffer_gets
, ROUND( ( buffer_gets
- disk_reads )
/ buffer_gets, 2 )
hit_ratio
, ROUND( disk_reads / executions, 2 ) reads_per_run
, sql_text
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND ( buffer_gets
- disk_reads )
/ buffer_gets < 0.80
ORDER BY 4 DESC;
18) 尽可能免避应用函数，函数会致导更多的 recursive calls
二、理合应用索引以高提性能
索引依赖于表而存在，是真实表的一个缩影，似类于一本书的目录，通过目录以更快得获所需的结果。Oracle应用了一个复杂的自平衡
B数据构结。即意任录记的DML作操将打破索引的平衡，而定期重构索引使得索引新重得获平衡。常通，通过索引查找数据比全表描扫更高效。
意任的DQL或DML作操，SQL优化引擎优先应用索引来盘算当前作操的成本以成生最好的执行计划。一旦应用索引操出参数optimizer_index_cost_adj
设定的值才应用全表描扫。同样对于多表连接应用索引也可以高提效率。同时索引也提供主键（primary key）的唯一性验证。
除了那些LONG或LONG RAW数据类型,你可以索引几乎有所的列.常通,在大型表中应用索引特别有效.当然,你也会发现,在描扫小表时,应用索
引同样能高提效率。
虽然应用索引能到得查询效率的高提,但是索引要需间空来存储,要需定期维护.尤其是在有大批DML作操的表上，意任的DML作操都将起引索
引的变更这意味着每条录记的INSERT ， DELETE ， UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引要需额外的存储间空和处置，
那些不必要的索引反而会使查询反应间时变慢。
DML作操应用索引上存在碎片而失去高度均衡,因此定期的重构索引是有必要的.
[sql]
- 1) 免避基于索引列的盘算
- where 句子中的谓词上存在索引，而此时基于该列的盘算将使得索引效失
- -->低效:
- SELECT employee_id, first_name
- FROM employees
- WHERE employee_id + 10 > 150; -->索引列上应用了盘算，因此索引效失，走全表描扫方法
- -->高效:
- SELECT employee_id, first_name
- FROM employees
- WHERE employee_id > 160; -->走索引范围描扫方法
- 例外况情
- 上述规则不适于用SQL中的MIN和MAX函数
- hr@CNMMBO> SELECT MAX( employee_id ) max_id
- 2 FROM employees
- 3 WHERE employee_id
- 4 + 10 > 150;
- 1 row selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1481384439
- ---------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | 4 | | |
- | 2 | FIRST ROW | | 5 | 20 | 1 (0)| 00:00:01 |
- |* 3 | INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK | 5 | 20 | 1 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------------------
- 2) 免避在索引列上应用NOT算运或不等于算运(<>,!=)
- 常通，我们要免避在索引列上应用NOT或<>,两者会生产在和在索引列上应用函数雷同的影响。 当ORACLE遇到NOT或不等算运时，他就会停止
- 应用索引转而执行全表描扫。
- -->低效:
- SELECT *
- FROM emp
- WHERE NOT ( deptno = 20 ); -->现实上NOT ( deptno = 20 )等同于deptno <> 20,即deptno <>同样会限制索引
- -->高效:
- SELECT *
- FROM emp
- WHERE deptno > 20 OR deptno < 20;
- -->尽管此方法可以替换且实现上述结果，但依然走全表描扫，如果是单纯的 > 或 < 算运，则此时为索引范围描扫
- 要需注意的是，在某些时候， ORACLE优化器会自动将NOT转化成相对应的关系作操符
- 其次如果是下列算运符停止NOT算运,依然有可能择选走索引, 仅仅除了NOT = 之外,因为 NOT = 等价于 <>
- “NOT >” to <=
- “NOT >=” to <
- “NOT <” to >=
- “NOT <=” to >
- 来看一个现实的例子
- hr@CNMMBO> SELECT *
- 2 FROM employees
- 3 where not employee_id<100; -->索引列上应用了not，但是该查询返回了有所的录记,即107条，因此此时择选走全表描扫
- 107 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1445457117
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 | -->执行计划中应用了走全表描扫方法
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("EMPLOYEE_ID">=100) -->查看这里的谓词息信被自动转换为 >= 算运符
- hr@CNMMBO> SELECT *
- 2 FROM employees
- 3 where not employee_id<140; -->此例与上面的语句雷同,仅仅是查询范围不同返回67条录记,而此时择选了索引范围描扫
- 67 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 603312277
- ---------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 68 | 4624 | 3 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 68 | 4624 | 3 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 68 | | 1 (0)| 00:00:01 | -->索引范围描扫方法
- ---------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("EMPLOYEE_ID">=140)
- 3) 用UNION 替换OR(适于用索引列)
- 常通况情下,应用UNION 替换WHERE句子中的OR将会起到较好的效果.基于索引列应用OR使得优化器倾向于应用全表描扫，而不是描扫索引.
- 注意,以上规则仅适于用多个索引列有效。 如果有column没有被索引， 查询效率可能会因为你没有择选OR而降低。
- -->低效:
- SELECT deptno, dname
- FROM dept
- WHERE loc = 'DALLAS' OR deptno = 20;
- -->高效:
- SELECT deptno, dname
- FROM dept
- WHERE loc = 'DALLAS'
- UNION
- SELECT deptno, dname
- FROM dept
- WHERE deptno = 30
- -->经试测,由于数据量较少，此时where句子中的谓词上都存在索引列时，两者性能相当.
- -->假定where句子中存在两列
- scott@CNMMBO> create table t6 as select object_id,owner,object_name from dba_objects where owner='SYS' and rownum<1001;
- scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SCOTT' and rownum<6;
- scott@CNMMBO> create index i_t6_object_id on t6(object_id);
- scott@CNMMBO> create index i_t6_owner on t6(owner);
- scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SYSTEM' and rownum<=300;
- scott@CNMMBO> commit;
- scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);
- scott@CNMMBO> select owner,count(*) from t6 group by owner;
- OWNER COUNT(*)
- -------------------- ----------
- SCOTT 5
- SYSTEM 300
- SYS 1000
- scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;
- OBJECT_ID OWNER OBJECT_NAME
- ---------- -------------------- --------------------
- 69450 SCOTT T_TEST
- scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';
- 301 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 238853296
- -----------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
- | 1 | CONCATENATION | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("OBJECT_ID"=69450)
- 4 - filter(LNNVL("OBJECT_ID"=69450))
- 5 - access("OWNER"='SYSTEM')
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 46 consistent gets
- 0 physical reads
- 0 redo size
- 11383 bytes sent via SQL*Net to client
- 712 bytes received via SQL*Net from client
- 22 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 301 rows processed
- scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;
- 301 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 238853296
- -----------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
- | 1 | CONCATENATION | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("OBJECT_ID"=69450)
- 4 - filter(LNNVL("OBJECT_ID"=69450))
- 5 - access("OWNER"='SYSTEM')
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 46 consistent gets
- 0 physical reads
- 0 redo size
- 11383 bytes sent via SQL*Net to client
- 712 bytes received via SQL*Net from client
- 22 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 301 rows processed
- scott@CNMMBO> select * from t6
- 2 where object_id=69450
- 3 union
- 4 select * from t6
- 5 where owner='SYSTEM';
- 301 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 370530636
- ------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 301 | 7224 | 7 (72)| 00:00:01 |
- | 1 | SORT UNIQUE | | 301 | 7224 | 7 (72)| 00:00:01 |
- | 2 | UNION-ALL | | | | | |
- | 3 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
- |* 4 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
- | 5 | TABLE ACCESS BY INDEX ROWID| T6 | 300 | 7200 | 3 (0)| 00:00:01 |
- |* 6 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access("OBJECT_ID"=69450)
- 6 - access("OWNER"='SYSTEM')
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 7 consistent gets
- 0 physical reads
- 0 redo size
- 11383 bytes sent via SQL*Net to client
- 712 bytes received via SQL*Net from client
- 22 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 301 rows processed
- -->从上面的统计息信可知，consistent gets由46下降为7，故当where句子中谓词上存在索引时，应用union替换or更高效
- -->即使当列object_id与owner上不存在索引时，应用union仍然比or更高效(在Oracle 10g R2与Oracle 11g R2试测)
- 4) 免避索引列上应用函数
- -->面下是一个来自现实生产境环的例子
- -->表acc_pos_int_tbl上business_date列存在索引,由于应用了SUBSTR函数，此时索引效失，应用全表描扫
- SELECT acc_num
- , curr_cd
- , DECODE( '20110728'
- , ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0
- , adj_credit_int_lv1_amt
- + adj_credit_int_lv2_amt
- - adj_debit_int_lv1_amt
- - adj_debit_int_lv2_amt )
- AS interest
- FROM acc_pos_int_tbl
- WHERE SUBSTR( business_date, 1, 6 ) = SUBSTR( '20110728', 1, 6 ) AND business_date <= '20110728';
- -->改进的办法
- SELECT acc_num
- , curr_cd
- , DECODE( '20110728'
- , ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0
- , adj_credit_int_lv1_amt
- + adj_credit_int_lv2_amt
- - adj_debit_int_lv1_amt
- - adj_debit_int_lv2_amt )
- AS interest
- FROM acc_pos_int_tbl acc_pos_int_tbl
- WHERE business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE( '20110728', 'yyyymmdd' ), -1 ) )
- + 1, 'yyyymmdd' )
- AND business_date <= '20110728';
- -->面下的例子虽然没有应用函数，但字符串连接同样致导索引效失
- -->低效:
- SELECT account_name, amount
- FROM transaction
- WHERE account_name
- || account_type = 'AMEXA';
- -->高效:
- SELECT account_name, amount
- FROM transaction
- WHERE account_name = 'AMEX' AND account_type = 'A';
- 5) 比较不匹配的数据类型
- -->面下的查询中business_date列上存在索引,且为字符型,这类
- -->低效:
- SELECT *
- FROM acc_pos_int_tbl
- WHERE business_date = 20090201；
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2335235465
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 37516 | 2857K| 106K (1)| 00:21:17 |
- |* 1 | TABLE ACCESS FULL| ACC_POS_INT_TBL | 37516 | 2857K| 106K (1)| 00:21:17 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(TO_NUMBER("BUSINESS_DATE")=20090201) -->这里可以看到生产了类型转换
- -->高效:
- SELECT *
- FROM acc_pos_int_tbl
- WHERE business_date = '20090201'
- 6) 索引列上应用 NULL 值
- IS NULL和IS NOT NULL会限制索引的应用,因为数据中没有值等于NULL值,即便是NULL值也不等于NULL值.且NULL值不存储在于索引之中
- 因此应尽可能免避在索引类上应用NULL值
- SELECT acc_num
- , pl_cd
- , order_qty
- , trade_date
- FROM trade_client_tbl
- WHERE input_date IS NOT NULL;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 901462645
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 44 | 15 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| TRADE_CLIENT_TBL | 1 | 44 | 15 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------
- alter table trade_client_tbl modify (input_date not null);
- 不推荐应用的查询方法
- SELECT * FROM table_name WHERE col IS NOT NULL
- SELECT * FROM table_name WHERE col IS NULL
- 推荐应用的方法
- SELECT * FROM table_name WHERE col >= 0 --尽可能的应用 =, >=, <=, like 等算运符
- -->Author: Robinson Cheng
1) 免避基于索引列的盘算
where 句子中的谓词上存在索引，而此时基于该列的盘算将使得索引效失
-->低效:
SELECT employee_id, first_name
FROM employees
WHERE employee_id + 10 > 150; -->索引列上应用了盘算，因此索引效失，走全表描扫方法
-->高效:
SELECT employee_id, first_name
FROM employees
WHERE employee_id > 160; -->走索引范围描扫方法
例外况情
上述规则不适于用SQL中的MIN和MAX函数
hr@CNMMBO> SELECT MAX( employee_id ) max_id
2 FROM employees
3 WHERE employee_id
4 + 10 > 150;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1481384439
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 5 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK | 5 | 20 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
2) 免避在索引列上应用NOT算运或不等于算运(<>,!=)
常通，我们要免避在索引列上应用NOT或<>,两者会生产在和在索引列上应用函数雷同的影响。 当ORACLE遇到NOT或不等算运时，他就会停止
应用索引转而执行全表描扫。
-->低效:
SELECT *
FROM emp
WHERE NOT ( deptno = 20 ); -->现实上NOT ( deptno = 20 )等同于deptno <> 20,即deptno <>同样会限制索引
-->高效:
SELECT *
FROM emp
WHERE deptno > 20 OR deptno < 20;
-->尽管此方法可以替换且实现上述结果，但依然走全表描扫，如果是单纯的 > 或 < 算运，则此时为索引范围描扫
要需注意的是，在某些时候， ORACLE优化器会自动将NOT转化成相对应的关系作操符
其次如果是下列算运符停止NOT算运,依然有可能择选走索引, 仅仅除了NOT = 之外,因为 NOT = 等价于 <>
“NOT >” to <=
“NOT >=” to <
“NOT <” to >=
“NOT <=” to >
来看一个现实的例子
hr@CNMMBO> SELECT *
2 FROM employees
3 where not employee_id<100; -->索引列上应用了not，但是该查询返回了有所的录记,即107条，因此此时择选走全表描扫
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 | -->执行计划中应用了走全表描扫方法
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID">=100) -->查看这里的谓词息信被自动转换为 >= 算运符
hr@CNMMBO> SELECT *
2 FROM employees
3 where not employee_id<140; -->此例与上面的语句雷同,仅仅是查询范围不同返回67条录记,而此时择选了索引范围描扫
67 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 603312277
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 4624 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 68 | 4624 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 68 | | 1 (0)| 00:00:01 | -->索引范围描扫方法
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">=140)
3) 用UNION 替换OR(适于用索引列)
常通况情下,应用UNION 替换WHERE句子中的OR将会起到较好的效果.基于索引列应用OR使得优化器倾向于应用全表描扫，而不是描扫索引.
注意,以上规则仅适于用多个索引列有效。 如果有column没有被索引， 查询效率可能会因为你没有择选OR而降低。
-->低效:
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS' OR deptno = 20;
-->高效:
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS'
UNION
SELECT deptno, dname
FROM dept
WHERE deptno = 30
-->经试测,由于数据量较少，此时where句子中的谓词上都存在索引列时，两者性能相当.
-->假定where句子中存在两列
scott@CNMMBO> create table t6 as select object_id,owner,object_name from dba_objects where owner='SYS' and rownum<1001;
scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SCOTT' and rownum<6;
scott@CNMMBO> create index i_t6_object_id on t6(object_id);
scott@CNMMBO> create index i_t6_owner on t6(owner);
scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SYSTEM' and rownum<=300;
scott@CNMMBO> commit;
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);
scott@CNMMBO> select owner,count(*) from t6 group by owner;
OWNER COUNT(*)
-------------------- ----------
SCOTT 5
SYSTEM 300
SYS 1000
scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;
OBJECT_ID OWNER OBJECT_NAME
---------- -------------------- --------------------
69450 SCOTT T_TEST
scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';
301 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 238853296
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=69450)
4 - filter(LNNVL("OBJECT_ID"=69450))
5 - access("OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
11383 bytes sent via SQL*Net to client
712 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
301 rows processed
scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;
301 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 238853296
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=69450)
4 - filter(LNNVL("OBJECT_ID"=69450))
5 - access("OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
11383 bytes sent via SQL*Net to client
712 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
301 rows processed
scott@CNMMBO> select * from t6
2 where object_id=69450
3 union
4 select * from t6
5 where owner='SYSTEM';
301 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 370530636
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 301 | 7224 | 7 (72)| 00:00:01 |
| 1 | SORT UNIQUE | | 301 | 7224 | 7 (72)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T6 | 300 | 7200 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_ID"=69450)
6 - access("OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
11383 bytes sent via SQL*Net to client
712 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
301 rows processed
-->从上面的统计息信可知，consistent gets由46下降为7，故当where句子中谓词上存在索引时，应用union替换or更高效
-->即使当列object_id与owner上不存在索引时，应用union仍然比or更高效(在Oracle 10g R2与Oracle 11g R2试测)
4) 免避索引列上应用函数
-->面下是一个来自现实生产境环的例子
-->表acc_pos_int_tbl上business_date列存在索引,由于应用了SUBSTR函数，此时索引效失，应用全表描扫
SELECT acc_num
, curr_cd
, DECODE( '20110728'
, ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0
, adj_credit_int_lv1_amt
+ adj_credit_int_lv2_amt
- adj_debit_int_lv1_amt
- adj_debit_int_lv2_amt )
AS interest
FROM acc_pos_int_tbl
WHERE SUBSTR( business_date, 1, 6 ) = SUBSTR( '20110728', 1, 6 ) AND business_date <= '20110728';
-->改进的办法
SELECT acc_num
, curr_cd
, DECODE( '20110728'
, ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0
, adj_credit_int_lv1_amt
+ adj_credit_int_lv2_amt
- adj_debit_int_lv1_amt
- adj_debit_int_lv2_amt )
AS interest
FROM acc_pos_int_tbl acc_pos_int_tbl
WHERE business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE( '20110728', 'yyyymmdd' ), -1 ) )
+ 1, 'yyyymmdd' )
AND business_date <= '20110728';
-->面下的例子虽然没有应用函数，但字符串连接同样致导索引效失
-->低效:
SELECT account_name, amount
FROM transaction
WHERE account_name
|| account_type = 'AMEXA';
-->高效:
SELECT account_name, amount
FROM transaction
WHERE account_name = 'AMEX' AND account_type = 'A';
5) 比较不匹配的数据类型
-->面下的查询中business_date列上存在索引,且为字符型,这类
-->低效:
SELECT *
FROM acc_pos_int_tbl
WHERE business_date = 20090201；
Execution Plan
----------------------------------------------------------
Plan hash value: 2335235465
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37516 | 2857K| 106K (1)| 00:21:17 |
|* 1 | TABLE ACCESS FULL| ACC_POS_INT_TBL | 37516 | 2857K| 106K (1)| 00:21:17 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("BUSINESS_DATE")=20090201) -->这里可以看到生产了类型转换
-->高效:
SELECT *
FROM acc_pos_int_tbl
WHERE business_date = '20090201'
6) 索引列上应用 NULL 值
IS NULL和IS NOT NULL会限制索引的应用,因为数据中没有值等于NULL值,即便是NULL值也不等于NULL值.且NULL值不存储在于索引之中
因此应尽可能免避在索引类上应用NULL值
SELECT acc_num
, pl_cd
, order_qty
, trade_date
FROM trade_client_tbl
WHERE input_date IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 901462645
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TRADE_CLIENT_TBL | 1 | 44 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
alter table trade_client_tbl modify (input_date not null);
不推荐应用的查询方法
SELECT * FROM table_name WHERE col IS NOT NULL
SELECT * FROM table_name WHERE col IS NULL
推荐应用的方法
SELECT * FROM table_name WHERE col >= 0 --尽可能的应用 =, >=, <=, like 等算运符
-->Author: Robinson Cheng
三、结总
1、尽可能最小化基表数据以及中间结果集(通过过滤件条免避后续生产不必要的盘算与聚合)
2、为where句子中的谓词息信提供最好的拜访路径(rowid拜访,索引拜访)
3、应用理合的SQL写法来免避过多的Oracle外部销开以高提性能
4、理合的应用提示以高提表之间的连接来高提连接效率(如免避迪卡尔集,将不理合的嵌套连接改为hash连接等)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
