减少表扫描次数的优化

最近在面试和学习中遇到了一些sql优化的问题，总结如下

一、求工资大于平均工资的员工信息：

对于这个题目，我们第一反应写出的查询语句如：select * from e where sal>(select avg(sal) from e); 这是我们最开始想到的，执行计划如下

create table e as select  * from emp;

SQL> select * from e where sal>(select avg(sal) from e);
Execution Plan
----------------------------------------------------------
Plan hash value: 1174284194

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    87 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | E    |     1 |    87 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |      |     1 |    13 |            |          |
|   3 |    TABLE ACCESS FULL| E    |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("SAL"> (SELECT AVG("SAL") FROM "E" "E"))

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

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

两次 全表扫描，7次逻辑读，现在我们用分析函数优化一下这个查询

select * from(select avg(sal) over (partition by 1) as avg_sal,e.* from e) w where w.sal>avg_sal;

SQL> select * from(select avg(sal) over (partition by 1) as avg_sal,e.* from e) w where w.sal>avg_sal;

Execution Plan
----------------------------------------------------------
Plan hash value: 1316326686

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |  1400 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |  1400 |     3   (0)| 00:00:01 |
|   2 |   WINDOW BUFFER     |      |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| E    |    14 |  1218 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("W"."SAL">"AVG_SAL")

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


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

可以看到只进行了一次表扫描，且逻辑读下降到了3。

二、由上面的例子我们可以拓展到求大于部门平均工资的情况

普通语句：select * from e ,(select deptno,avg(sal) avg_sal from e group by deptno) w where e.deptno=w.deptno and e.sal>w.avg_sal;

用分析函数语句：select * from(select avg(sal) over (partition by deptno) as avg_sal,e.* from e) w where w.sal>w.avg_sal;

SQL> select * from e ,(select deptno,avg(sal) avg_sal from e group by deptno) w where e.deptno=w.deptno and e.sal>w.avg_sal
Plan hash value: 3522624553

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   339 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     3 |   339 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | E    |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |    14 |   364 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |    14 |   364 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| E    |    14 |   364 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> select * from(select avg(sal) over (partition by deptno) as avg_sal,e.* from e) w where w.sal>w.avg_sal;

Plan hash value: 798854145

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |  1400 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |  1400 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |  1218 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| E    |    14 |  1218 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

三、查询只出售一种商品'PC' 的商户信息

create table test(name nvarchar2(10),type nvarchar2(20));
insert into test values ('A','PC');
insert into test values ('B','PC');
insert into test values ('B','PRINTER');
insert into test values ('C','PRINTER');

select * from test;


NAME       TYPE
---------- --------------------
A          PC
B          PC
B          PRINTER
C          PRINTER

我之前的理解是只要把有出售非'PC‘产品的商户排除，就得到了只卖'PC’的商户了，所以得如下查询语句
select name from test a where not exists( select 1 from test b where a.name = b.name and b.type <>'PC');

执行计划如下：

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   184 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     4 |   184 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST |     4 |    48 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST |     2 |    68 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

优化语句：select name from test group by name having sum(decode(type, 'PC', 0, 1)) = 0;

Plan hash value: 1349321023

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |   136 |     4  (25)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     4 |   136 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST |     4 |   136 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

四、求各部门最高工资信息

1、自连接方式：select * from emp a where sal=(select max(sal) max_sal  from emp w where  w.deptno=a.deptno) order by a.deptno;

2、内联视图：select  a.* from emp a ,(select max(sal) max_sal ,deptno from emp group by deptno) w where w.deptno=a.deptno and a.sal=w.max_sal  order by a.deptno;

3、分析函数：select * from (select max(sal) over(partition by deptno ) max_sal ,a.* from emp a) w where w.max_sal=w.sal order by w.deptno;

其中3、分析函数 的方法效率最高，这个例子可以应用到查询用户最后登录的信息上。

以上几个优化的宗旨，都是在减少子查询，减少表扫描，从而达到优化查询的目的。

以后遇到其他情况再慢慢更新