【书评: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
错误写法：select empno,ename from scott.emp where rownum
SQL> select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum
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
EMPNO ENAME
---------- ----------
7782 CLARK
7369 SMITH
7499 ALLEN
SQL> select empno,ename from scott.emp where rownum
EMPNO ENAME
---------- ----------
7499 ALLEN
7369 SMITH
7521 WARD
SQL> select empno,ename from scott.emp where rownum
EMPNO ENAME
---------- ----------
7369 SMITH
7521 WARD
7499 ALLEN
SQL> select empno,ename from scott.emp where rownum
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
好了，第一章内容基本上就这些，后边等我阅读了第二章后再给大家分享吧。
...........................................................................................................................................................................................
本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1652985/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取码：af2d
QQ：642808185 注明：ITPUB的文章标题
...........................................................................................................................................................................................