Oracle查询优化改写技巧与案例(第一章) —— 单表查询

全屏书中详细目录：Oracle查询优化改写技巧与案例 —— 目录

第1章 单表查询

1.1 查询表中所有的行与列

在进行查询操作之前，一般先看一下表的结构，例如 desc emp;

查询所有的信息，直接使用 select * from 表名 即可

SQL> select * from emp;


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

1.2 从表中检索部分行

查询表中有多少个销售人员，加上where过滤条件即可，职位列是job

SQL> select * from emp where job='SALESMAN';


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

1.3 查找空值

若要查某一列为空的数据，比如，返回提成（comm）为空的数据

SQL> select * from emp where comm = null;
未选定行

明显的数据库有数据，但是却没有查到，问题在哪儿呢？ 

实际上，NULL是不能用“=”运算符的，要用 IS NULL判断，正确语法如下

SQL> select * from emp where comm is null;


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

NULL不支持加减乘除，大小比较，相等比较，否则只能为空

SQL> select * from dept where 1>=null;
未选定行
SQL> select * from dept where 1<=null;
未选定行
SQL> select * from dept where 1+null<=0;
未选定行
SQL> select * from dept where 1*null<=0;
未选定行

对于其他函数，在使用时最好测试一下有NULL时，会返回什么结果。

SQL> select replace('abcdef','a',NULL) as str from dual;

STR
-----
bcdef

SQL> select greatest(1,null) from dual;

GREATEST(1,NULL)
----------------


SQL>

可以看到，不同的函数对NULL的支持也不一样，所以大家遇到NULL时最好先测试一下结果受什么影响，而不是仅凭经验甚至是臆测

知识点：Oracle函数之greatest函数详解、Oracle中replace函数的使用

1.4 将空值转换为实际值

对于下列语句

select coaleace(comm, 0) from emp;

有人会问，为什么是coalesce函数，而不是nvl函数？

这是因为coalesce函数更好用，看一下示例，返回多个值中第一个不为空的值。

/* 创建视图 */
create or replace view v as
select null as c1, null as c2, 1 as c3, null as c4, 2 as c5, null as c6 from dual union all
select null as c1, null as c3, null as c3, 3 as c4, null as c5, 2 as c6 from dual;

select * from v;

select coalesce(c1,c2,c3,c4,c5,c6) as c from v;

可以看到，相对于nvl来说，coalesce支持多个参数，能很方便的返回第一个不为空的值，如果上面的值改为nvl，就需要嵌套很多层了

select nvl(nvl(nvl(nvl(nvl(c1,c2),c3),c4),c5),c6) as c from v;

知识点：Oracle中scott用户创建视图权限不足(解决)、Oracle中coalesce函数的使用、【Oracle】decode 和 nvl 函数的用法及区别？

1.5 查找满足多个条件的行

对于简单的査询,操作起来比较容易,那么复杂一点的呢?比如,我们要査询部门10 中的所有员工、所有得到提成的员工, 以及部门20中工资不超过2000美元的员工。

这是三个条件的组合, 符合上述任一条件即可。

我们把这三个条件整理成逻辑表达式的形式: (部门10中的员工 0R 所有得到提成的员工 0R(工资<=2000and部门号=20))

注意: 对于多个条件的组合,要使用括号, 这样在更改维护语句时可以不必再考虑优先级问题, 而且可以很容易地借助各种工具找到各组合条件的起止位置。

那么我们可以这样写

select * from emp where (deptno = 10
       /* 所有的到提成的员工，注意千万不要写成 comm <> null */
       or comm is not null
       /* 部门(20)中工资超过2000美元的员工 */
       or (sal <=2000 and deptno = 20)
   );

如上述语句，可以清楚看到起止位置，对于复杂的组合条件来说，要方便阅读许多

1.6 从表中检索部分列

前面我们都是取表中所有的列, 但实际的场景中, 常常只需要返回部分列的数据就可以。如只需员工编码、员工名称、雇佣日期、工资。所以一般要明确指定査询哪些列, 而不是用“*”号来代替。另外,明确要返回的列也会使语旬的维护更简单, 而不必每次看到语句时都需要査看表结构才知道会返回什么数据。

select empno, ename, hiredate, sal from emp where deptno= 10;

1.7 为列取有意义的名称

不是每个人都能看懂那些简写的字母是什么意思，所以应该给列取个别名。你可以如下面所示在 as 后面跟别名，也可以不要 as，直接在列名后跟别名即可。看看下面这个报表数据, 就会一目了然。

SQL> select ename as 姓名, deptno as 部门编号, sal 工资 from emp;

姓名         部门编号       工资
---------- ---------- ----------
SMITH              20        800
ALLEN              30       1600
WARD               30       1250
JONES              20       2975
MARTIN             30       1250

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

写报表时经常会加上各种条件，而直接在条件中使用别名比列名(如: B01、 B02、B03) 要清晰得多，引用别名时千万别忘了嵌套一层，因为这个别名是在select之后才有效的。 

如下示例是寻找那些拖了国家 GDP后腿的人:

SQL> select * from (select sal as 工资, comm as 提成 from emp) x where 工资<1000;

      工资       提成
---------- ----------
       800
       950

1.9 拼接列

若有人不喜欢看表格式的数据，希望返回的数据都像“ CLARK 的工作是MANAGER” 这样的显示 。 我们可以用字符串连接符“||”来把各列拼在一起 。

SQL> select ename || ' 的工作是 ' || job as msg from emp where deptno = 10;

MSG
-----------------------------
CLARK 的工作是 MANAGER
KING 的工作是 PRESIDENT
MILLER 的工作是 CLERK

当然，拼接列对我们来说还有其他意义，看下面的例子

SQL> select 'truncate table ' || owner || '.' || table_name || ';' as 清空表 from all_tables where owner = 'SCOTT';

清空表
-----------------------------------------------------------------------------
truncate table SCOTT.DEPT;
truncate table SCOTT.EMP;
truncate table SCOTT.BONUS;
truncate table SCOTT.SALGRADE;

没错，就是用SQL来生成SQL，当需要大量类似的SQL需要生成时，就可以先写一个语句然后进行修改，直接用基础数据或数据字典来批量生成，不过这个例子中的SQL不要去执行，否则会丢失数据。

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

有时为了更清楚地区分返回的信息，需要做如下处理。

如：当职员工资小于或等于2000美元时,就返回消息“过低”，大于或等于4000美元时，就返回消息“过高”，如果在这两者之间,就返回“0K”

类似这种需求也许会经常遇见，处理这样的需求可以用 casec when 来判断转化

select ename,
       sal,
       case
         when sal <= 2000 then
          '过低'
         when sal >= 4000 then
          '过高'
         else
          'ok'
       end as status
  from emp
 where deptno = 10

这种方式还经常用在报表中，比如按工资分档次统计人数

select 档次, count(*) as 人数
  from (select (case
                 when sal <= 1000 then
                  '0000-1000'
                 when sal <= 2000 then
                  '1000-2000'
                 when sal <= 3000 then
                  '2000-3000'
                 else
                  '好高'
               end) as 档次,
               ename,
               sal
          from emp)
 group by 档次
 order by 1;

1.11 限制返回的行数

在线查询时，并不要求每次都返回所有的数据，比如，进行抽查时候会要求只返回两条数据

我们可以利用伪列 rownum 来过滤，rownum 依次对返回的每一条数据做一个标识。

SQL> select * from emp where rownum <=2;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

如果直接用 rownum = 2来查询会出现什么情况呢？

SQL> select * from emp where rownum = 2;

未选定行

因为 rownum 是依次对数据做标识的，就像上学时依据考分排名一样，需要有第一名，后面才会有第二名，所以，需要先把所以的数据取出来，才能有第二名。

正确的取第二行数据的查询应该是下面这样，先生成序号。

SQL> select * from (select rownum as sn, emp.* from emp where rownum <=2) where sn = 2;

        SN      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
         2       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

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

为了防止作假，像 1.11 节中那样抽查数据还不行，还需要随机抽查。

我们可以先用 dbms_random 来对数据进行随机排序，然后取其中的前三行。

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

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7566 JONES
      7844 TURNER

有人会问，为什么要嵌套一层呢？直接这样用多好。

select empno, ename from emp where rownum <= 3 order by dbms_random.value;
/* 为了观察方便，对结果进行排序 */
select * from (select empno, ename from emp where rownum <= 3 order by dbms_random.value) order by 1;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD

多运行几次会发现，每次的得到数据都是一样的，并不是随机，这是为什么呢？

为了便于解释，先对上面的语句进行等价改写：

select empno, ename, dbms_random.value ran
  from emp
 where rownum <= 3
 order by ran;

查看语句中这几处的执行顺序为：

• select

• rownum

• order by

也就是说，要先取出数据，然后在生成序号，最后才是排序

我们可以通过子查询把排序前后的序号分别取出来对比

select rownum as 排序后,
       排序前,
       empno  as 编码,
       ename  as 姓名,
       ran    as 随机数
  from (select rownum as 排序前, empno, ename, dbms_random.value ran
          from emp
         where rownum <= 3
         order by ran);

同样，你再多运行几次，看看是不是和刚才的描述一致。

因此，正确的写法是：先随机排序，再取出数据

知识点：利用rownum伪列实现分页查询内容、【Oracle】dbms_random函数用法快速生成多条测试数据

1.13 模糊查询

先建立如下视图

create or replace view v as
  select 'ABCEDF' as  vname from dual 
  union all
  select '_BCEFG' as  vname from dual 
  union all
  select '_BCEDF' as  vname from dual
  union all
  select '_\BCEDF' as  vname from dual
  union all
  select 'XYCEF' as  vname from dual;

要求一：查处 vname 中包含字符串"ced"的

方法比较简单。常见的模糊查询如下

SQL> select * from v where vname like '%CED%';

    VNAME
    -------
    ABCEDF
    _BCEDF
    _\BCEDF

要求二：查出 vname 中包含字符串"_BCE"的

SQL> select * from v where vname like '_BCE%';

    VNAME
    -------
    ABCEDF
    _BCEFG
    _BCEDF

发现多出了一个 ABCEDF，因为在like子句中有两个通配符：%(替代一个或多个字符)、“_”(替代一个或多个字符)。

在这里，“_”被当成通配符了，怎么办呢？

莫着急，我们可以使用转义字符来处理

SQL> select * from v where vname like '\_BCE%' escape '\';

    VNAME
    -------
    _BCEFG
    _BCEDF

ESCAPE 把'\'标识为转义字符，而'\'把'_'转义为字符，而非其原来的通配符含义

或许有人注意到其中有一行值为'_\BCEDF'，那么加了 ESCAPE '\' 后返回什么样的数据呢？

SQL> select * from v where vname like '_\BCE%' escape '\';
第 1 行出现错误:
ORA-01424: 转义符之后字符缺失或非法

看到没，会报错，熟悉开发的朋友都知道，需要双写转义字符即可。

SQL> select * from v where vname like '_\\BCE%' escape '\';

    VNAME
    -------
    _\BCEDF

对于字符串中包含“%”的情况也是一样。转义即可