在Oracle中，SQL优化在写法上有哪些常用的方法？
一般在书写SQL时需要注意哪些问题，如何书写可以提高查询的效率呢？可以从以下几个方面去考虑：
（1）减少对数据库的访问次数。
当执行每条SQL语句时，Oracle在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少Oracle的工作量。充分利用表索引，避免进行全表扫描；充分利用共享缓存机制，提高SQL工作效率；充分利用结构化编程方式，提高查询的复用能力。常用的方法为把对数据库的操作写成存储过程，然后应用程序通过调用存储过程，而不是直接使用SQL。
（2）减少对大表的扫描次数。可以利用WITH对SQL中多次扫描的表来进行修改。采用各种手段来避免全表扫描。
（3）SELECT子句中避免使用“*”，应该写出需要查询的字段。
当想在SELECT子句中列出所有的列时，可以使用“*”来返回所有的列，但这是一个非常低效的方法。实际上，Oracle在解析的过程中，会将“*”依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。不需要的字段尽量少查，多查的字段可能有行迁移或行链接（timesten还有行外存储问题）。少查LOB类型的字段可以减少I/O。
（4）尽量使用表的别名（ALIAS）。
当在SQL语句中连接多个表时，请使用表的别名，并把别名前缀于每个列上。此时就可以减少解析的时间并减少那些由列歧义引起的语法错误。
（5）对于数据量较少、又有主键索引的情况，可以考虑将关联子查询或外连接的SQL修改为标量子查询。
（6）避免隐式类型转换（Implicit Type Conversion）。如果进行比较的两个值的数据类型不同，那么Oracle必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换。通常当开发人员将数字存储在字符列时会导致这种问题的产生。Oracle在运行时会在索引字符列使用TO_NUMBER函数强制转化字符类型为数值类型。由于添加函数到索引列所以导致索引不被使用。实际上，Oracle也只能这么做，类型转换是一个应用程序设计因素。由于转换是在每行都进行的，这会导致性能问题。一般情况下，当比较不同数据类型的数据时，Oracle自动地从复杂向简单的数据类型转换，该规则和MySQL中的隐式类型转换是一致的。所以，字符类型的字段值应该加上引号。例如，假设USER_NO是一个字符类型的索引列，则：
1SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = 109204421;
2
3--这个语句在执行的时候被Oracle在内部自动的转换为：
4SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) = 109204421;
5
6--因为内部发生的类型转换，这个索引将不会被使用，所以正确的写法应该是：
7SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = '109204421';
8
9--但是，在下面的SQL语句中，Oracle隐式地将字符串“03-MAR-97”转化为默认日期类型为“DD-MON-YY”的日期：
10SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE = '03-MAR-97';
11
12Execution Plan
13----------------------------------------------------------
14Plan hash value: 1445457117
15
16-------------------------------------------------------------------------------
17| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
18-------------------------------------------------------------------------------
19| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
20|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
21-------------------------------------------------------------------------------
22
23Predicate Information (identified by operation id):
24---------------------------------------------------
25
26 1 - filter("HIRE_DATE"='24-APR-06')
（7）避免使用耗费资源的操作，包括DISTINCT、UNION、MINUS、INTERSECT、ORDER BY、GROUP BY等。能用DISTINCT的就不用GROUP BY。能用UNION ALL就不要用UNION。
（8）用TRUNCATE替代DELETE。若要删除表中所有的数据，则可以用TRUNCATE替代DELETE。
（9）根据查询条件建立合适的索引，利用索引可以避免大表全表扫描（FULL TABLE SCAN）。
（10）合理使用临时表。
（11）避免写过于复杂的SQL，不一定非要一个SQL解决问题。将一个大的SQL改写为多个小的SQL来实现功能。条件允许的情况下可以使用批处理来完成。
（12）在不影响业务的前提下尽量减小事务的粒度。
（13）当使用基于规则的优化器（RBO）时，在多表连接查询的时候，记录数少的表应该放在右边。
（14）避免使用复杂的集合函数，像NOT IN等。通常，要避免在索引列上使用NOT，NOT会产生和在索引列上使用函数相同的影响。当Oracle遇到NOT操作符时，它就会停止使用索引转而执行全表扫描。很多时候用EXISTS和NOT EXISTS代替IN和NOT IN语句是一个好的选择。需要注意的是，在Oracle 11g之前，若NOT IN的列没有指定非空的话（注意：是主表和子表的列未同时有NOT NULL约束，或都未加IS NOT NULL限制），则NOT IN选择的是filter操作（如果指定了非空，那么会选择ANTI的反连接），但是从Oracle 11g开始有新的ANTI NA（NULL AWARE）优化，可以对子查询进行UNNEST，NOT IN和NOT EXISTS都选择的是ANTI的反连接，所以效率是一样的。在一般情况下，ANTI的反连接算法比filter更高效。对于未UNNEST的子查询，若选择了filter操作，则至少有两个子节点，执行计划还有个特点就是Predicate谓词部分有“:B1”这种类似绑定变量的内容，内部操作走类似Nested Loops操作。如果在Oracle 11g之前，遇到NOT IN无法UNNEST，那么可以将NOT IN部分的匹配条件均设为NOT NULL约束。若不添加NOT NULL约束，则需要两个条件均增加IS NOT NULL条件。当然也可以将NOT IN修改为NOT EXISTS。关于反连接的更多内容参考【3.2.5.10 什么是半连接、反连接和星型连接？】。
分别在Oracle 10g和Oracle 11g实验：
1SELECT * FROM V$VERSION;
2DROP TABLE EMP PURGE;
3DROP TABLE DEPT PURGE;
4CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
5CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
6SET TIMING ON
7SET LINESIZE 1000
8SET AUTOTRACE TRACEONLY
9--写法1
10SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
11--写法2
12SELECT * FROM DEPT WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO);
13--写法3
14SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL) AND DEPTNO IS NOT NULL;
15--写法4
16SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) AND DEPTNO IS NOT NULL;
17--写法5
18SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);
看一下详细执行计划：
1SELECT * FROM V$VERSION;
2DROP TABLE EMP PURGE;
3DROP TABLE DEPT PURGE;
4CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
5CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
6SET TIMING ON
7SET LINESIZE 1000
8SET AUTOTRACE TRACEONLY
9--写法1
10SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
11SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
12
13LHR@orclasm > SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
14
15 DEPTNO DNAME LOC
16---------- -------------- -------------
17 40 OPERATIONS BOSTON
18
19
20Execution Plan
21----------------------------------------------------------
22Plan hash value: 3547749009
23
24---------------------------------------------------------------------------
25| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
26---------------------------------------------------------------------------
27| 0 | SELECT STATEMENT | | 1 | 30 | 5 (0)| 00:00:01 |
28|* 1 | FILTER | | | | | |
29| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
30|* 3 | TABLE ACCESS FULL| EMP | 13 | 169 | 2 (0)| 00:00:01 |
31---------------------------------------------------------------------------
32
33Predicate Information (identified by operation id):
34---------------------------------------------------
35
36 1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE
37 LNNVL("DEPTNO"<>:B1)))
38 3 - filter(LNNVL("DEPTNO"<>:B1))
39
40Note
41-----
42 - dynamic sampling used for this statement (level=2)
43
44
45Statistics
46----------------------------------------------------------
47 15 recursive calls
48 0 db block gets
49 31 consistent gets
50 0 physical reads
51 0 redo size
52 674 bytes sent via SQL*Net to client
53 519 bytes received via SQL*Net from client
54 2 SQL*Net roundtrips to/from client
55 0 sorts (memory)
56 0 sorts (disk)
57 1 rows processed
58
59LHR@orclasm > SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
60
61 DEPTNO DNAME LOC
62---------- -------------- -------------
63 40 OPERATIONS BOSTON
64
65
66Execution Plan
67----------------------------------------------------------
68Plan hash value: 2100826622
69
70---------------------------------------------------------------------------
71| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
72---------------------------------------------------------------------------
73| 0 | SELECT STATEMENT | | 4 | 172 | 7 (15)| 00:00:01 |
74|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 7 (15)| 00:00:01 |
75| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
76| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
77---------------------------------------------------------------------------
78
79Predicate Information (identified by operation id):
80---------------------------------------------------
81
82 1 - access("DEPTNO"="DEPTNO")
83
84Note
85-----
86 - dynamic sampling used for this statement (level=2)
87
88
89Statistics
90----------------------------------------------------------
91 7 recursive calls
92 0 db block gets
93 14 consistent gets
94 0 physical reads
95 0 redo size
96 674 bytes sent via SQL*Net to client
97 519 bytes received via SQL*Net from client
98 2 SQL*Net roundtrips to/from client
99 0 sorts (memory)
100 0 sorts (disk)
101 1 rows processed
针对上面的NOT IN子查询，如果子查询中的DEPTNO有NULL存在，那么整个查询都不会有结果，在Oracle 11g之前，如果主表和子表的DEPTNO未同时有NOT NULL约束，或都未加IS NOT NULL限制，那么Oracle会选择filter。从Oracle 11g开始有新的ANTI NA（NULL AWARE）优化，可以对子查询进行UNNEST，从而提高效率。对于未UNNEST的子查询，若选择了FILTER操作，则至少有两个子节点，执行计划还有个特点就是Predicate谓词部分有“:B1”这种类似绑定变量的内容，内部操作走类似Nested Loops操作。
如下所示：
1LHR@orclasm > SELECT /*+rule gather_plan_statistics*/ *
2 2 FROM SCOTT.EMP
3 3 WHERE NOT EXISTS (SELECT 0
4 4 FROM SCOTT.DEPT
5 5 WHERE DEPT.DNAME = 'SALES'
6 6 AND DEPT.DEPTNO = EMP.DEPTNO)
7 7 AND NOT EXISTS
8 8 (SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME);
9
10 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
11---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
12 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
13 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
14 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
15 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
16 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
17 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
18 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
19 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
20
218 rows selected.
22
23LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
24
25
26PLAN_TABLE_OUTPUT
27------------------------------------------------------------------------------------------
28SQL_ID b8w1s38hqtjkj, child number 0
29-------------------------------------
30SELECT /*+rule gather_plan_statistics*/ * FROM SCOTT.EMP WHERE NOT
31EXISTS (SELECT 0 FROM SCOTT.DEPT WHERE DEPT.DNAME =
32'SALES' AND DEPT.DEPTNO = EMP.DEPTNO) AND NOT EXISTS
33(SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME)
34
35Plan hash value: 1445856646
36
37-----------------------------------------------------------------------------------------
38| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
39-----------------------------------------------------------------------------------------
40| 0 | SELECT STATEMENT | | 1 | 8 |00:00:00.01 | 14 |
41|* 1 | FILTER | | 1 | 8 |00:00:00.01 | 14 |
42| 2 | TABLE ACCESS FULL | EMP | 1 | 14 |00:00:00.01 | 8 |
43|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 |00:00:00.01 | 6 |
44|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 |00:00:00.01 | 3 |
45|* 5 | TABLE ACCESS FULL | BONUS | 8 | 0 |00:00:00.01 | 0 |
46-----------------------------------------------------------------------------------------
47
48Query Block Name / Object Alias (identified by operation id):
49-------------------------------------------------------------
50
51 1 - SEL$1
52 2 - SEL$1 / EMP@SEL$1
53 3 - SEL$2 / DEPT@SEL$2
54 4 - SEL$2 / DEPT@SEL$2
55 5 - SEL$3 / BONUS@SEL$3
56
57Outline Data
58-------------
59
60 /*+
61 BEGIN_OUTLINE_DATA
62 IGNORE_OPTIM_EMBEDDED_HINTS
63 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
64 DB_VERSION('11.2.0.3')
65 RBO_OUTLINE
66 OUTLINE_LEAF(@"SEL$2")
67 OUTLINE_LEAF(@"SEL$3")
68 OUTLINE_LEAF(@"SEL$1")
69 FULL(@"SEL$1" "EMP"@"SEL$1")
70 FULL(@"SEL$3" "BONUS"@"SEL$3")
71 INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
72 END_OUTLINE_DATA
73 */
74
75Predicate Information (identified by operation id):
76---------------------------------------------------
77
78 1 - filter(( IS NULL AND IS NULL))
79 3 - filter("DEPT"."DNAME"='SALES')
80 4 - access("DEPT"."DEPTNO"=:B1)
81 5 - filter("BONUS"."ENAME"=:B1)
82
83Column Projection Information (identified by operation id):
84-----------------------------------------------------------
85
86 1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
87 "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
88 "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
89 2 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
90 "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
91 "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
92 4 - "DEPT".ROWID[ROWID,10]
93
94Note
95-----
96 - rule based optimizer used (consider using cbo)
97
98
9970 rows selected.
100
101
102-----------------------------------------------------------------------------------------
103| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
104-----------------------------------------------------------------------------------------
105| 0 | SELECT STATEMENT | | 1 | 8 |00:00:00.01 | 14 |
106|* 1 | FILTER | | 1 | 8 |00:00:00.01 | 14 |
107| 2 | TABLE ACCESS FULL | EMP | 1 | 14 |00:00:00.01 | 8 |
108|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 |00:00:00.01 | 6 |
109|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 |00:00:00.01 | 3 |
110|* 5 | TABLE ACCESS FULL | BONUS | 8 | 0 |00:00:00.01 | 0 |
111-----------------------------------------------------------------------------------------
112
113Predicate Information (identified by operation id):
114---------------------------------------------------
1151 - filter(( IS NULL AND IS NULL))
1163 - filter("DEPT"."DNAME"='SALES')
1174 - access("DEPT"."DEPTNO"=:B1)
1185 - filter("BONUS"."ENAME"=:B1)
该执行计划的执行顺序为：
① ID1有3个子节点ID2、ID3、ID5，由于ID2最小，故先执行ID2；
② ID2对EMP表进行全表扫描，将返回14行给ID1；
③ 在相关组合中ID2应当控制ID3和ID5的执行，由于Oracle此处对Distinct Value做了优化，所以ID3只执行了3次。
④ ID4执行3次，并返回3个RWOID到ID3；
⑤ ID3使用ID4返回的3个ROWID来访问数据表块，过滤“filter("DEPT"."DNAME"='SALES')”的数据，由于是NOT EXISTS，所以这导致ID1原来获得的14行排除6行的“"DEPT"."DNAME"='SALES'”，只剩下8行，这8行数据影响了ID5的执行次数，将执行8次，其中“filter("BONUS"."ENAME"=:B1)”过滤条件的“:B1”由ID1的8行数据提供，ID5没有返回数据，所以那8行没有减少ID1将8行彻底过滤的数据返回给客户端。
（15）尽量避免使用UNION关键词，可以根据情况修改为UNION ALL。
（16）在Oracle数据库里，IN和OR是等价的，优化器在处理带IN的目标SQL时会将其转换为带OR的等价SQL。例如，“DEPTNO IN (10,20)”和“DEPTNO=10 OR DEPTNO=20”是等价的。
（17）选择合适的谓词进行过滤。
（18）避免使用前置通配符（%）。在WHERE子句中，如果索引列所对应的值的第一个字符由通配符（WILDCARD）开始，索引将不被采用。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其它位置时，优化器就能利用索引。若前置通配符实在无法取消，则可以从2个方面去考虑。①去重和去空。应该把表中的重复记录或者为空的记录全部去掉，这样可以大大减少结果集，因而提升性能，这里也体现了大表变小表的思想；②考虑建立文本索引。③做相关的转换，请参考【3.2.5.3 模糊查询可以使用索引吗？】。
（19）应尽量避免在WHERE子句中对索引字段进行函数、算术运算或其他表达式等操作，因为这样可能会使索引失效，查询时要尽可能将操作移至等号右边。见如下例子：
SELECT * FROM T1 WHERE SUBSTR(NAME,2,1)='L';
在以上SQL中，即使NAME字段建有唯一索引，该SQL语句也无法利用索引进行检索数据，而是走全表扫描的方式。一些常见的改写如下表所示：
原SQL语句 | 优化后SQL语句 |
SELECT * FROM T1 WHERE COL/2=100; | SELECT * FROM T1 WHERE COL=200; |
SELECT * FROM T1 WHERE SUBSTR(CARD_NO,1,4)='5378'; | SELECT * FROM T1 WHERE CARD_NO LIKE '5378%'; |
SELECT * FROM T1 WHERE TO_CHAR(CREATED,'YYYY') = '2011'; | SELECT * FROM T1 WHERE CREATED >= TO_DATE('20110101','YYYYMMDD') AND CREATED < TO_DATE('20120101','YYYYMMDD'); |
SELECT * FROM T1 WHERE TRUNC(CREATED)=TRUNC(SYSDATE); | SELECT * FROM T1 WHERE CREATED >= TRUNC(SYSDATE) AND CREATED < TRUNC(SYSDATE+1); |
SELECT * FROM T1 WHERE 'X'||COL2>'X5400021452'; | SELECT * FROM T1 WHERE COL2>'5400021452'; |
SELECT * FROM T1 WHERE COL||COL2='5400250000';（在该SQL中，COL和COL2列长度固定） | SELECT * FROM T1 WHERE COL='5400' AND COL2='250000'; |
SELECT * FROM T1 WHERE TO_CHAR(CREATED,'YYYY') = TO_CHAR(ADD_MONTHS(SYSDATE, -12),'YYYY'); | SELECT * FROM T1 WHERE CREATED >= TRUNC(ADD_MONTHS(SYSDATE, -12),'YYYY') AND CREATED < TRUNC(SYSDATE,'YYYY');--去年 |
需要注意的是，如果SELECT需要检索的字段只包含索引列且WHERE查询中的索引列含有非空约束的时候，以上规则并不适用。例如，SQL语句“SELECT CREATED FROM T1 WHERE TRUNC(CREATED)=TRUNC(SYSDATE);”，若CREATED列上有非空约束或在WHERE子句中加上“CREATED IS NOT NULL”，则该SQL语句仍然会走索引，如下所示：
1DROP TABLE T PURGE;
2CREATE TABLE T NOLOGGING AS SELECT * FROM DBA_OBJECTS D ;
3CREATE INDEX IND_OBJECTNAME ON T(OBJECT_NAME);
4
5SELECT T.OBJECT_NAME FROM T WHERE T.OBJECT_NAME ='T'; --走索引
6SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ='T'; --不走索引
7SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ='T' AND T.OBJECT_NAME IS NOT NULL ; --走索引(INDEX FAST FULL SCAN)
8SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ||'AAA' ='T'||'AAA' AND T.OBJECT_NAME IS NOT NULL ; --走索引(INDEX FAST FULL SCAN)
9SELECT T.OBJECT_NAME,T.OWNER FROM T WHERE UPPER(T.OBJECT_NAME) ||'AAA' ='T'||'AAA' AND T.OBJECT_NAME IS NOT NULL ; --不走索引
（20）合理使用分析函数。
（21）应尽量避免在WHERE子句中使用不等操作符（!=或<>），否则引擎将放弃使用索引而进行全表扫描。
（22）避免不必要和无意义的排序。
（23）尽可能减少关联表的数量，关联表尽量不要超过3张。
（24）在建立复合索引时，尽量把最常用、重复率低的字段放在最前面。在查询的时候，WHERE条件尽量要包含索引的第一列即前导列。
（25）应尽量避免在WHERE子句中对字段进行IS NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。可以通过加伪列创建伪联合索引来使得IS NULL使用索引。例如语句：“SELECT ID FROM T WHERE NUM IS NULL;”可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：“SELECT ID FROM T WHERE NUM=0;”。
（26）IN要慎用，因为IN会使系统无法使用索引，而只能直接搜索表中的数据。如：
1SELECT ID FROM T WHERE NUM IN (1,2,3);
2对于连续的数值，能用BETWEEN就不要用IN了：
3SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3;
（27）必要时使用Hint强制查询优化器使用某个索引，如在WHERE子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
（28）在条件允许的情况下，只访问索引，从而可以避免索引回表读（TABLE ACCESS BY INDEX ROWID，通过索引再去读表中的内容）。当索引中包括处理查询所需要的所有数据时，可以执行只扫描索引操作，而不用做索引回表读操作。因为索引回表读开销很大，能避免则避免。避免的方法就是，①根据业务需求只留下索引字段；②建立联合索引。这里的第二点需要注意平衡，如果联合索引的联合列太多，必然导致索引过大，虽然消减了回表动作，但是索引块变多，在索引中的查询可能就要遍历更多的BLOCK了，所以需要全面考虑，联合索引列不宜过多，一般来说超过3个字段组成的联合索引都是不合适的，需要权衡利弊。
（29）选择合适的索引。Oracle在进行一次查询时，一般对一个表只会使用一个索引。例如，某表有索引1（POLICYNO）和索引2（CLASSCODE），如果查询条件为POLICYNO ='XX' AND CLASSCODE ='XX'，那么系统有可能会使用索引2，相较于使用索引1，查询效率明显降低。
（30）优先且尽可能使用分区索引。
（31）在删除（DELETE）、插入（INSERT）、更新（UPDATE）频繁的表中，建议不要使用位图索引。
（32）对于分区表，应该减少需要扫描的分区，避免全分区扫描。对于单分区扫描，在分区表后加上PARTITION(分区名)；对于多分区扫描，使用分区关键字来限制需要扫描的范围，从而可以避免全分区扫描。
（33）使用分批处理、DBMS_PARALLEL_EXECUTE进行处理。
（34）删除重复记录尽量采用ROWID的方法，如下所示：
DELETE FROM SCOTT.EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM SCOTT.EMP X WHERE X.EMPNO = E.EMPNO);
（35）SQL中慎用自定义函数。如果自定义函数的内容，只是针对函数输入参数的运算，而没有访问表这样的代码，那么这样的自定义函数在SQL中直接使用是高效的；否则，如果函数中含有对表的访问的语句，那么在SQL中调用该函数很可能会造成很大的性能问题，需要谨慎！在这种情况下，往往将函数中访问表的代码取出和调用它的SQL整合成新的SQL。
（36）使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，这对于大表非常有效，如下所示：
1SELECT COUNT(*), SUM(SAL) FROM SCOTT.EMP WHERE DEPTNO = 20 AND ENAME LIKE 'SMITH%';
2SELECT COUNT(*), SUM(SAL) FROM SCOTT.EMP WHERE DEPTNO = 30 AND ENAME LIKE 'SMITH%';
3
4--若使用DECODE函数则对SCOTT.EMP表只访问一次，如下所示：
5SELECT COUNT(DECODE(DEPTNO, 20, '1', NULL)) D20_COUNT, COUNT(DECODE(DEPTNO, 30, '1', NULL)) D30_COUNT,
6 SUM(DECODE(DEPTNO, 20, SAL, NULL)) D20_SAL, SUM(DECODE(DEPTNO, 30, SAL, NULL)) D30_SAL
7 FROM SCOTT.EMP
8 WHERE ENAME LIKE 'SMITH%';
类似的，DECODE函数也可以运用于GROUP BY和ORDER BY子句中。
（37）在计算表的行数时，若表上有主键，则尽量使用COUNT(*)或COUNT(1)。
（38）用WHERE子句替换HAVING子句。避免使用HAVING子句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目，那么就能提高SQL的性能。如下所示：
1--低效：
2SELECT T.EMPNO, COUNT(*) FROM SCOTT.EMP T GROUP BY T.EMPNO HAVING EMPNO = 7369;
3
4--高效：
5SELECT T.EMPNO, COUNT(*) FROM SCOTT.EMP T WHERE EMPNO = 7369 GROUP BY T.EMPNO ;
（39）减少对表的查询，尤其是要避免在同一个SQL中多次访问同一张大表。可以考虑如下的改写方法：
① 先根据条件提取数据到临时表中，然后再做连接，即利用WITH进行改写。
② 有的相似的语句可以用MAX+DECODE函数来处理。
③ 在含有子查询的SQL语句中，要特别注意减少对表的查询，例如形如“UPDATE AAA T SET T.A=(....) T.B=(....) WHERE ....;”该更新的SQL语句中小括号中的大表都是一样的，且查询非常相似，这个时候可以修改为：“UPDATE AAA T SET (T.A,T.B)=(.....) WHERE ....;”。
（40）SQL语句统一使用大写。因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。
（41）对于一些固定性的小的查询结果集或统计性的SQL语句（例如，SQL语句非常复杂，但是最终返回的结果集很简单，只包含少数的几行数据）可以使用结果集缓存（Result Cache）。对于一些常用的小表可以使用保留池（Keep Pool）。
（42）如果在一条SQL语句中同时取最大值和最小值，那么需要注意写法上的差异：
1SELECT MAX(OBJECT_ID),MIN(OBJECT_ID) FROM T; --效率差，选择INDEX FAST FULL SCAN
2SELECT MAX_VALUE, MIN_VALUE FROM (SELECT MAX(OBJECT_ID) MAX_VALUE FROM T) A, (SELECT MIN(OBJECT_ID) MIN_VALUE FROM T) B;--效率高，选择INDEX FULL SCAN (MIN/MAX)
示例如下所示：
准备环境：
1DROP TABLE T_20170704_LHR_01 PURGE;
2CREATE TABLE T_20170704_LHR_01 AS SELECT * FROM DBA_OBJECTS;
3UPDATE T_20170704_LHR_01 SET OBJECT_ID=ROWNUM;
4COMMIT;
5ALTER TABLE T_20170704_LHR_01 ADD CONSTRAINT PK_20170704_OBJECT_ID PRIMARY KEY (OBJECT_ID);
普通写法：
1LHR@orclasm > SET AUTOTRACE ON
2LHR@orclasm > SET LINESIZE 1000
3LHR@orclasm > SELECT MAX(OBJECT_ID),MIN(OBJECT_ID) FROM T_20170704_LHR_01;
4
5MAX(OBJECT_ID) MIN(OBJECT_ID)
6-------------- --------------
7 79298 1
8
9
10Execution Plan
11----------------------------------------------------------
12Plan hash value: 2419726051
13
14-----------------------------------------------------------------------------------------------
15| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
16-----------------------------------------------------------------------------------------------
17| 0 | SELECT STATEMENT | | 1 | 13 | 51 (2)| 00:00:01 |
18| 1 | SORT AGGREGATE | | 1 | 13 | | |
19| 2 | INDEX FAST FULL SCAN| PK_20170704_OBJECT_ID | 76600 | 972K| 51 (2)| 00:00:01 |
20-----------------------------------------------------------------------------------------------
21
22Note
23-----
24 - dynamic sampling used for this statement (level=2)
25
26
27Statistics
28----------------------------------------------------------
29 0 recursive calls
30 0 db block gets
31 172 consistent gets
32 0 physical reads
33 0 redo size
34 613 bytes sent via SQL*Net to client
35 519 bytes received via SQL*Net from client
36 2 SQL*Net roundtrips to/from client
37 0 sorts (memory)
38 0 sorts (disk)
39 1 rows processed
优化后的写法：
1LHR@orclasm > SELECT MAX_VALUE, MIN_VALUE FROM (SELECT MAX(OBJECT_ID) MAX_VALUE FROM T_20170704_LHR_01) A, (SELECT MIN(OBJECT_ID) MIN_VALUE FROM T_20170704_LHR_01) B;
2
3 MAX_VALUE MIN_VALUE
4---------- ----------
5 79298 1
6
7
8Execution Plan
9----------------------------------------------------------
10Plan hash value: 3965153161
11
12------------------------------------------------------------------------------------------------------
13| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
14------------------------------------------------------------------------------------------------------
15| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
16| 1 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
17| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
18| 3 | SORT AGGREGATE | | 1 | 13 | | |
19| 4 | INDEX FULL SCAN (MIN/MAX)| PK_20170704_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |
20| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
21| 6 | SORT AGGREGATE | | 1 | 13 | | |
22| 7 | INDEX FULL SCAN (MIN/MAX)| PK_20170704_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |
23------------------------------------------------------------------------------------------------------
24
25Note
26-----
27 - dynamic sampling used for this statement (level=2)
28
29
30Statistics
31----------------------------------------------------------
32 0 recursive calls
33 0 db block gets
34 4 consistent gets
35 0 physical reads
36 0 redo size
37 603 bytes sent via SQL*Net to client
38 519 bytes received via SQL*Net from client
39 2 SQL*Net roundtrips to/from client
40 0 sorts (memory)
41 0 sorts (disk)
42 1 rows processed
无论是从cost还是逻辑读方面，差异都是非常大的，因为优化后的SQL选择的是“INDEX FULL SCAN (MIN/MAX)”，性能大幅度提升。
（43）在PL/SQL中，在定义变量类型时尽量使用%TYPE和%ROWTYPE，这样可以减少代码的修改，增加程序的可维护性。
以上讲解的每点优化内容希望读者可以通过实验来加深理解。
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
