简答题
2. 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自动地从复杂向简单的数据类型转换。所以，字符类型的字段值应该加上引号。例如，假设USER_NO是一个字符类型的索引列，则：
这个语句在执行的时候被Oracle在内部自动的转换为：
因为内部发生的类型转换，这个索引将不会被使用，所以正确的写法应该是：
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。
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)开始，索引将不被采用。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。若前置通配符实在无法取消，则可以从几个方面去考虑。①去重和去空。应该把表中的重复记录或者为空的记录全部去掉，这样可以大大减少结果集，因而提升性能，这里也体现了大表变小表的思想。②考虑建立文本索引。③做相关的转换。
19)应尽量避免在WHERE子句中对字段进行函数、算术运算或其他表达式等操作，因为这样可能会使索引失效，查询时要尽可能将操作移至等号右边。见如下例子：
在以上SQL中，即使NAME字段建有唯一索引，该SQL语句也无法利用索引进行检索数据，而是走全表扫描的方式。一些常见的改写见表。
需要注意的是，如果SELECT需要检索的字段只包含索引列且WHERE查询中的索引列含有非空约束的时候，以上规则并不适用。例如，SQL语句“SELECT CREATED FROM T1 WHERE TRUNC (CREATED)=TRUNC(SYSDATE);”，若CREATED列上有非空约束或在WHERE子句中加上“CREATED IS NOT NULL”，则该SQL语句仍然会进行索引，如下：
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会使系统无法使用索引，而只能直接搜索表中的数据。如：
对于连续的数值，能用BETWEEN就不要用IN了：
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的方法，如下：
35)SQL中慎用自定义函数。如果自定义函数的内容，只是针对函数输入参数的运算，而没有访问表这样的代码，那么这样的自定义函数在SQL中直接使用是高效的；否则，如果函数中含有对表的访问的语句，那么在SQL中调用该函数很可能会造成很大的性能影响，需要谨慎!在这种情况下，往往会将函数中访问表的代码取出和调用它的SQL整合成新的SQL。
36)使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，这对于大表非常有效，如下：
若使用DECODE函数则对SCOTT.EMP表只访问一次，如下：
类似的，DECODE函数也可以运用于GROUP BY和ORDER BY子句中。
37)在计算表的行数时，若表上有主键，则尽量使用COUNT(*)或COUNT(1)。
38)用WHERE子句替换HAVING子句。避免使用HAVING子句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目，那么就能提高SQL的性能。如下：
39)减少对表的查询，尤其是要避免在同一个SQL中多次访问同一张大表。可以考虑如下的改写方法：
①先根据条件提取数据到临时表中，然后再做连接，即利用WITH进行改写。
②有的相似的语句可以用MAX+DECODE函数来处理。
③在含有子查询的SQL语句中，要特别注意减少对表的查询，例如形如“UPDATE AAA T SET TA=(...)T.B=(....) WHERE....;”该更新的SQL语句中小括号中的大表都是一样的，且查询非常相似，这个时候可以修改为：“UPDATE AAA T SET(T.A, T.B)=(....) WHERE....;”。
40)SQL语句统一使用大写。因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。
41)对于一些固定性的查询结果可以使用结果集缓存(Result Cache)，对于一些常用的小表可以使用保留池(Keep Pool)。
42)如果在一条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自动地从复杂向简单的数据类型转换。所以，字符类型的字段值应该加上引号。例如，假设USER_NO是一个字符类型的索引列，则：
这个语句在执行的时候被Oracle在内部自动的转换为：
因为内部发生的类型转换，这个索引将不会被使用，所以正确的写法应该是：
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。
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)开始，索引将不被采用。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。若前置通配符实在无法取消，则可以从几个方面去考虑。①去重和去空。应该把表中的重复记录或者为空的记录全部去掉，这样可以大大减少结果集，因而提升性能，这里也体现了大表变小表的思想。②考虑建立文本索引。③做相关的转换。
19)应尽量避免在WHERE子句中对字段进行函数、算术运算或其他表达式等操作，因为这样可能会使索引失效，查询时要尽可能将操作移至等号右边。见如下例子：
在以上SQL中，即使NAME字段建有唯一索引，该SQL语句也无法利用索引进行检索数据，而是走全表扫描的方式。一些常见的改写见表。
需要注意的是，如果SELECT需要检索的字段只包含索引列且WHERE查询中的索引列含有非空约束的时候，以上规则并不适用。例如，SQL语句“SELECT CREATED FROM T1 WHERE TRUNC (CREATED)=TRUNC(SYSDATE);”，若CREATED列上有非空约束或在WHERE子句中加上“CREATED IS NOT NULL”，则该SQL语句仍然会进行索引，如下：
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会使系统无法使用索引，而只能直接搜索表中的数据。如：
对于连续的数值，能用BETWEEN就不要用IN了：
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的方法，如下：
35)SQL中慎用自定义函数。如果自定义函数的内容，只是针对函数输入参数的运算，而没有访问表这样的代码，那么这样的自定义函数在SQL中直接使用是高效的；否则，如果函数中含有对表的访问的语句，那么在SQL中调用该函数很可能会造成很大的性能影响，需要谨慎!在这种情况下，往往会将函数中访问表的代码取出和调用它的SQL整合成新的SQL。
36)使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，这对于大表非常有效，如下：
若使用DECODE函数则对SCOTT.EMP表只访问一次，如下：
类似的，DECODE函数也可以运用于GROUP BY和ORDER BY子句中。
37)在计算表的行数时，若表上有主键，则尽量使用COUNT(*)或COUNT(1)。
38)用WHERE子句替换HAVING子句。避免使用HAVING子句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目，那么就能提高SQL的性能。如下：
39)减少对表的查询，尤其是要避免在同一个SQL中多次访问同一张大表。可以考虑如下的改写方法：
①先根据条件提取数据到临时表中，然后再做连接，即利用WITH进行改写。
②有的相似的语句可以用MAX+DECODE函数来处理。
③在含有子查询的SQL语句中，要特别注意减少对表的查询，例如形如“UPDATE AAA T SET TA=(...)T.B=(....) WHERE....;”该更新的SQL语句中小括号中的大表都是一样的，且查询非常相似，这个时候可以修改为：“UPDATE AAA T SET(T.A, T.B)=(....) WHERE....;”。
40)SQL语句统一使用大写。因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。
41)对于一些固定性的查询结果可以使用结果集缓存(Result Cache)，对于一些常用的小表可以使用保留池(Keep Pool)。
42)如果在一条SQL语句中同时取最大值和最小值，那么需要注意写法上的差异：
【答案解析】