【书评:Oracle查询优化改写】第一章

BLOG文档结构图：

之前帮助ITPUB上的一位博主修改过一个很明显的错误，ITPUB为了表达感谢特赠予一本技术方面的书籍，我可以自己选择书名，想了想，自己对SQL优化特感兴趣于是就订了一本SQL优化改写方面的书籍，书名为《Oracle查询优化改写》，其实这本书的作者我是认识的，之前数次在公开课上听过他讲过SQL优化改写方面的内容，印象很深刻，好了，不多说了，说多了有打广告的嫌疑。

最近一直在学习rac方面的内容，但是rac高可用性，这个方面很难写，写浅了感觉浪费时间，也浪费读者的时间，写深了又怕出错，误导别人，在自己未完全理解的情况下，不想随便发布blog，但总得发布一点啥吧，想了想不如就写写读后感吧，基于自己本身喜欢SQL优化方面的内容，又有书在手，那就写写书中的内容吧，有好的例子，我试验下给大家分享出来，今天我们就看看这本书的第一章的内容吧。

第一章很简单，就讲了下单表查询的内容，目录参考后边附录部分，大致包含：NULL上常犯的错误、字符串中单引号的处理方式及模糊查询时对通配符的转义。

第 1 章 单表查询

1.1 查询表中所有的行与列

1.2 从表中检索部分行

1.3 查找空值

1.4 将空值转换为实际值

1.5 查找满足多个条件的行

1.6 从表中检索部分列

1.7 为列取有意义的名称

1.8 在 WHERE 子句中引用取别名的列

1.9 拼接列

1.10 在 SELECT 语句中使用条件逻辑

1.11 限制返回的行数

1.12 从表中随机返回 n 条记录

1.13 模糊查询

1  空值

这个很简单，但是容易出错，可以认为是重点，判断为空或者不为空的时候，只能采用is null 或者is not null，不能用=null，任何空值做加减乘除大小比较或相等比较结果都为空，这个没啥实验的，oracle入门必学的一些内容，另外还有一些处理空值的函数，例如nvl、coalesce等，这部分就不做实验了，书中没有如下的内容，我补充一些，也是书是讲SQL优化改写的，又不是讲解语法的。

解释：

1. 空值是无效的，未指定的，未知的或不可预知的值。

2. 空值不是空格也不是0。

3. 包含空值的数学表达式的值(即加减乘除等操作)都为空值null

4. 对空值进行连接字符串的操作之后，返回为被连接的字符串。

5. 为空用is null 来表示，不为空用 is  not  null 来表示，除此之外没有其它的表示方法了，这一点尤为重要！！！！！！

6. 除了count(1) 和count(*) 外的其它函数都不计算空值

7. Null 在排序中默认为最大值，desc在最前，asc在最后，可以加上nulls last 来限制null值的显示

办法：使用 nvl 函数来处理

SELECT  sal*13+nvl(comm,  0)*13  "年薪"  , ename,  comm  FROM  emp;

问题：如何显示没有上级的雇员的情况？

错误写法：select  *  from  emp  where  mgr  =  '';

正确写法：SELECT  *  FROM  emp  WHERE  mgr  is  null;

1. 有如下的数据，请问select avg(age) from stuInfo; 的结果是多少？

答案：5.2   空值是不参与运算的

1.1  让is null走索引

不知道书的后边会不会讲解如何让is null走索引的内容，我这里先给大家讲讲如何让is null去走索引吧。

   解决办法： is null 加伪列创建伪联合索引来使得is null使用索引

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 13 17:29:16 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> create table lhr.t2 as select * from dba_objects;

Table created.

SQL> select * from lhr.t2 where object_id is null;

no rows selected

SQL> set autot on;

SQL> select * from lhr.t2 where object_id is null;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    12 |  2484 |   289   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T2   |    12 |  2484 |   289   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL)

Note

-----

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

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1035  consistent gets

       1032  physical reads

          0  redo size

       1343  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

SQL> create index ind_t2_id on lhr.t2(object_id);

Index created.

SQL>  exec dbms_stats.gather_index_stats('SYS','ind_t2_id'); 

PL/SQL procedure successfully completed.

SQL> select * from lhr.t2 where object_id is null;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    12 |  2484 |   289   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T2   |    12 |  2484 |   289   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL)

Note

-----

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

Statistics

----------------------------------------------------------

        935  recursive calls

          0  db block gets

       1238  consistent gets

       1102  physical reads

          0  redo size

       1343  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          0  rows processed

SQL> DROP INDEX ind_t2_id;

Index dropped.

SQL> create index LHR.ind_t2_id on lhr.t2(object_id,-1);

Index created.

SQL>  exec dbms_stats.gather_index_stats('LHR','ind_t2_id'); 

PL/SQL procedure successfully completed.

SQL> SET LINE 9999

SQL> select * from lhr.t2 where object_id is null;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2868503181

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |    12 |  2484 |    67   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2        |    12 |  2484 |    67   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T2_ID |  4071 |       |    12   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID" IS NULL)

Note

-----

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

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

       1343  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

SQL>

可以看出过滤条件中is null走了索引。

2  sql中的别名

     以我的sql优化经验而言，强烈建议表加别名，尤其对于sql中是多张表关联的时候更应对每一个表加上别名。

3  从表中随机返回N条记录

正确写法：select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum<=3;

错误写法：select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

SQL> select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum<=3;

     EMPNO ENAME

---------- ----------

      7788 SCOTT

      7934 MILLER

      7900 JAMES

SQL> select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum<=3;

     EMPNO ENAME

---------- ----------

      7782 CLARK

      7369 SMITH

      7499 ALLEN

SQL> select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

     EMPNO ENAME

---------- ----------

      7499 ALLEN

      7369 SMITH

      7521 WARD

SQL> select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

     EMPNO ENAME

---------- ----------

      7369 SMITH

      7521 WARD

      7499 ALLEN

SQL> select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

     EMPNO ENAME

---------- ----------

      7499 ALLEN

      7521 WARD

      7369 SMITH

SQL>

4  模糊查询

简单点的模糊查询就不说了，但是在字符串中含有’\’的时候，双写\\即可。

SQL> create or replace view v as select '_\BCEDF' VNAME FROM DUAL;

View created.

SQL> SELECT * FROM V WHERE VNAME LIKE '_\BC%' ESCAPE '\';

SELECT * FROM V WHERE VNAME LIKE '_\BC' ESCAPE '\'

                                 *

ERROR at line 1:

ORA-01424: missing or illegal character following the escape character

SQL> SELECT * FROM V WHERE VNAME LIKE '_\\BC%' ESCAPE '\';

VNAME

-------

_\BCEDF

好了，第一章内容基本上就这些，后边等我阅读了第二章后再给大家分享吧。