欢迎投稿

今日深度：

> > 正文

减少表扫描次数的优化

来源:未知 阅读:18424 评论 342 热度2

标签：

减少表扫描次数的优化


减少表扫描次数的优化
 
最近在面试和学习中遇到了一些sql优化的问题，总结如下
 
一、求工资大于平均工资的员工信息：
 
对于这个题目，我们第一反应写出的查询语句如：select * from e where sal>(select avg(sal) from e); 这是我们最开始想到的，执行计划如下
 
[sql] 
  
[sql] 
create table e as select  * from emp;  
[sql] 
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 |  
----------------------------------------------------------------------------  
[sql] 
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  
[sql] 
  
两次 全表扫描，7次逻辑读，现在我们用分析函数优化一下这个查询
select * from(select avg(sal) over (partition by 1) as avg_sal,e.* from e) w where w.sal>avg_sal;
 
[sql] 
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] 
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' 的商户信息
 
[sql] 
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');
执行计划如下：
 
[sql] 
---------------------------------------------------------------------------  
| 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;
[sql] 
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、分析函数 的方法效率最高，这个例子可以应用到查询用户最后登录的信息上。
 
以上几个优化的宗旨，都是在减少子查询，减少表扫描，从而达到优化查询的目的。

www.htsjk.Com true http://www.htsjk.com/oracle/21013.html NewsArticle 减少表扫描次数的优化 减少表扫描次数的优化 最近在面试和学习中遇到了一些sql优化的问题，总结如下 一、求工资大于平均工资的员工信息： 对于这个题目，我们第一反应写出的查询...
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。
返回首页
相关文章
    暂无相关文章
评论暂时关闭