ITPub博客

首页 > 数据库 > Oracle > 【书评:Oracle查询优化改写】第一章

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

原创 Oracle 作者:lhrbest 时间:2015-05-13 17:53:41 2 删除 编辑

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

 

 

BLOG文档结构图:

wpsE751.tmp 

 

之前帮助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; 的结果是多少?

wpsE762.tmp 

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

 

wpsE763.tmp 

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

 

 

 

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

 

...........................................................................................................................................................................................

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

ITPUB BLOG:http://blog.itpub.net/26736162

本文地址:http://blog.itpub.net/26736162/viewspace-1652985/

本文pdf版:http://yunpan.cn/QCwUAI9bn7g7w  提取码:af2d

QQ:642808185 注明:ITPUB的文章标题

<版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任!>

...........................................................................................................................................................................................

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1652985/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8351731