专栏首页小麦苗的DB宝专栏【DB笔试面试570】在Oracle中,SQL优化在写法上有哪些常用的方法?

【DB笔试面试570】在Oracle中,SQL优化在写法上有哪些常用的方法?

题目部分

在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是一个字符类型的索引列,则:

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = 109204421;

--这个语句在执行的时候被Oracle在内部自动的转换为:
SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) = 109204421;

--因为内部发生的类型转换,这个索引将不会被使用,所以正确的写法应该是:
SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = '109204421';

--但是,在下面的SQL语句中,Oracle隐式地将字符串“03-MAR-97”转化为默认日期类型为“DD-MON-YY”的日期:
SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE = '03-MAR-97';

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    69 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    69 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   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实验:

SELECT * FROM V$VERSION;
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
SET TIMING ON
SET LINESIZE 1000
SET AUTOTRACE TRACEONLY
--写法1
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
--写法2
SELECT * FROM DEPT WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO);
--写法3
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL) AND DEPTNO IS NOT NULL;
--写法4
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) AND DEPTNO IS NOT NULL;
--写法5
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);

看一下详细执行计划:

SELECT * FROM V$VERSION;
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;
SET TIMING ON
SET LINESIZE 1000
SET AUTOTRACE TRACEONLY
--写法1
SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);

LHR@orclasm > SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    30 |     5   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    13 |   169 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE
              LNNVL("DEPTNO"<>:B1)))
   3 - filter(LNNVL("DEPTNO"<>:B1))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        674  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LHR@orclasm > SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   172 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     4 |   172 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="DEPTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        674  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          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操作。

如下所示:

LHR@orclasm > SELECT  /*+rule gather_plan_statistics*/  *
  2    FROM SCOTT.EMP
  3   WHERE NOT EXISTS (SELECT 0
  4            FROM SCOTT.DEPT
  5           WHERE DEPT.DNAME = 'SALES'
  6             AND DEPT.DEPTNO = EMP.DEPTNO)
  7     AND NOT EXISTS
  8   (SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

8 rows selected.

LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  b8w1s38hqtjkj, child number 0
-------------------------------------
SELECT  /*+rule gather_plan_statistics*/  *   FROM SCOTT.EMP  WHERE NOT
EXISTS (SELECT 0           FROM SCOTT.DEPT          WHERE DEPT.DNAME =
'SALES'            AND DEPT.DEPTNO = EMP.DEPTNO)    AND NOT EXISTS
(SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME)

Plan hash value: 1445856646

----------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name    | Starts | A-Rows |   A-Time   | Buffers | 
----------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |         |      1 |      8 |00:00:00.01 |      14 | 
|*  1 |  FILTER                      |         |      1 |      8 |00:00:00.01 |      14 | 
|   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |00:00:00.01 |       8 | 
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |00:00:00.01 |       6 | 
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      3 |00:00:00.01 |       3 | 
|*  5 |   TABLE ACCESS FULL          | BONUS   |      8 |      0 |00:00:00.01 |       0 | 
-----------------------------------------------------------------------------------------  

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$2 / DEPT@SEL$2
   4 - SEL$2 / DEPT@SEL$2
   5 - SEL$3 / BONUS@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      RBO_OUTLINE
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      FULL(@"SEL$3" "BONUS"@"SEL$3")
      INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(( IS NULL AND  IS NULL))
   3 - filter("DEPT"."DNAME"='SALES')
   4 - access("DEPT"."DEPTNO"=:B1)
   5 - filter("BONUS"."ENAME"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
       "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   2 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
       "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   4 - "DEPT".ROWID[ROWID,10]

Note
-----
   - rule based optimizer used (consider using cbo)


70 rows selected.


----------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name    | Starts | A-Rows |   A-Time   | Buffers | 
----------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |         |      1 |      8 |00:00:00.01 |      14 | 
|*  1 |  FILTER                      |         |      1 |      8 |00:00:00.01 |      14 | 
|   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |00:00:00.01 |       8 | 
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |00:00:00.01 |       6 | 
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      3 |00:00:00.01 |       3 | 
|*  5 |   TABLE ACCESS FULL          | BONUS   |      8 |      0 |00:00:00.01 |       0 | 
-----------------------------------------------------------------------------------------  

Predicate Information (identified by operation id): 
--------------------------------------------------- 
1 - filter(( IS NULL AND  IS NULL)) 
3 - filter("DEPT"."DNAME"='SALES') 
4 - access("DEPT"."DEPTNO"=:B1) 
5 - 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语句仍然会走索引,如下所示:

DROP TABLE T  PURGE;
CREATE TABLE T  NOLOGGING AS SELECT *  FROM    DBA_OBJECTS D ;
CREATE   INDEX IND_OBJECTNAME ON  T(OBJECT_NAME); 

SELECT T.OBJECT_NAME FROM T WHERE T.OBJECT_NAME ='T';   --走索引
SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ='T';     --不走索引
SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ='T' AND T.OBJECT_NAME IS NOT NULL ;    --走索引(INDEX FAST FULL SCAN)
SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ||'AAA' ='T'||'AAA' AND T.OBJECT_NAME IS NOT NULL ;     --走索引(INDEX FAST FULL SCAN)
SELECT 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会使系统无法使用索引,而只能直接搜索表中的数据。如:

SELECT ID FROM T WHERE NUM IN (1,2,3);
对于连续的数值,能用BETWEEN就不要用IN了:
SELECT 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函数可以避免重复扫描相同记录或重复连接相同的表,这对于大表非常有效,如下所示:

SELECT COUNT(*), SUM(SAL) FROM SCOTT.EMP WHERE DEPTNO = 20 AND ENAME LIKE 'SMITH%';
SELECT COUNT(*), SUM(SAL) FROM SCOTT.EMP WHERE DEPTNO = 30 AND ENAME LIKE 'SMITH%';

--若使用DECODE函数则对SCOTT.EMP表只访问一次,如下所示:
SELECT COUNT(DECODE(DEPTNO, 20, '1', NULL)) D20_COUNT,  COUNT(DECODE(DEPTNO, 30, '1', NULL)) D30_COUNT,
       SUM(DECODE(DEPTNO, 20, SAL, NULL)) D20_SAL, SUM(DECODE(DEPTNO, 30, SAL, NULL)) D30_SAL
  FROM SCOTT.EMP
 WHERE ENAME LIKE 'SMITH%';

类似的,DECODE函数也可以运用于GROUP BY和ORDER BY子句中。

(37)在计算表的行数时,若表上有主键,则尽量使用COUNT(*)或COUNT(1)。

(38)用WHERE子句替换HAVING子句。避免使用HAVING子句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目,那么就能提高SQL的性能。如下所示:

--低效:
SELECT T.EMPNO, COUNT(*) FROM SCOTT.EMP T GROUP BY T.EMPNO HAVING EMPNO = 7369;

--高效:
SELECT 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语句中同时取最大值和最小值,那么需要注意写法上的差异:

SELECT MAX(OBJECT_ID),MIN(OBJECT_ID) FROM T; --效率差,选择INDEX FAST FULL SCAN
SELECT 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)

示例如下所示:

准备环境:

DROP TABLE T_20170704_LHR_01 PURGE;
CREATE TABLE T_20170704_LHR_01 AS SELECT * FROM DBA_OBJECTS;
UPDATE T_20170704_LHR_01 SET OBJECT_ID=ROWNUM;
COMMIT;
ALTER TABLE T_20170704_LHR_01 ADD CONSTRAINT PK_20170704_OBJECT_ID PRIMARY KEY (OBJECT_ID);

普通写法:

LHR@orclasm > SET AUTOTRACE ON
LHR@orclasm > SET LINESIZE 1000
LHR@orclasm > SELECT MAX(OBJECT_ID),MIN(OBJECT_ID) FROM T_20170704_LHR_01;

MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         79298              1


Execution Plan
----------------------------------------------------------
Plan hash value: 2419726051

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 |    13 |    51   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                       |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_20170704_OBJECT_ID | 76600 |   972K|    51   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        172  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

优化后的写法:

LHR@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;

 MAX_VALUE  MIN_VALUE
---------- ----------
     79298          1


Execution Plan
----------------------------------------------------------
Plan hash value: 3965153161

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                       |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   VIEW                       |                       |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |                       |     1 |    13 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PK_20170704_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |   VIEW                       |                       |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |                       |     1 |    13 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| PK_20170704_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        603  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

无论是从cost还是逻辑读方面,差异都是非常大的,因为优化后的SQL选择的是“INDEX FULL SCAN (MIN/MAX)”,性能大幅度提升。

(43)在PL/SQL中,在定义变量类型时尽量使用%TYPE和%ROWTYPE,这样可以减少代码的修改,增加程序的可维护性。

以上讲解的每点优化内容希望读者可以通过实验来加深理解。

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试617】在Oracle中,和“表达式和条件评估”相关的查询转换有哪些?

    小麦苗DBA宝典
  • 【DB笔试面试590】在Oracle中,什么是反连接(Anti Join)?

    反连接(Anti Join)也是一种特殊的连接类型,通常用于从一个表中返回不在另一个数据源中的数据行。当做子查询展开时,Oracle经常会把那些外部WHERE条...

    小麦苗DBA宝典
  • 【DB笔试面试767】在Oracle中,OGG的命令接口是哪个?

    对OGG进行交互的命令为GGSCI(GoldenGate Software Command Interface),GGSCI提供了十分丰富的命令来对OGG进行各...

    小麦苗DBA宝典
  • 平台安全之文件解析

    1 /xx.asp/xx.jpg IIS会把xx.asp下的文件都当作asp来解析执行

    信安之路
  • Linux下查看进程的启动和运行时间

    总体来说, ps主要是查看进程的,尤其你关心的进程 top主要看cpu,内存使用情况,及占用资源最多的进程由高到低排序,关注点在于资源占用情况

    Y大宽
  • git流水线(Pipeline)导致分支(Branch)无法合并的解决方法

    Pipelines 中文称为流水线,是分阶段执行的构建任务。如:安装依赖、运行测试、打包、部署开发服务器、部署生产服务器等流程。每一次 push 或者 Merg...

    德顺
  • 个性化推荐系统设计(4.1)案例分析

    在过去的十年中,神经网络已经取得了巨大的飞跃。如今,神经网络已经得以广泛应用,并逐渐取代传统的机器学习方法。 接下来,我要介绍一下YouTube...

    两只橙
  • Docker的前世今生

    在引入 Docker之前,或许有必要先聊聊 LXC。在 Linux使用过程中,大家很少会接触到LXC,因为 LXC对于大多数人来说仍然是一个比较陌生的词汇。那为...

    博文视点Broadview
  • Web漏洞 | 文件解析漏洞

    文件解析漏洞主要由于网站管理员操作不当或者 Web 服务器自身的漏洞,导致一些特殊文件被 IIS、apache、nginx 或其他 Web服务器在某种情况下解释...

    天钧
  • 对话图灵奖得主John Hennessy,他说对美国留学签证变化很忧心

    名字不常出现在中文互联网,但绝对是全球科技领域扫地僧一样的大牛,而且鲜有人能达到他一样的高度和成就。

    量子位

扫码关注云+社区

领取腾讯云代金券