当前位置：首页 -> 基础 -> 数据库编程

减少表扫描次数的优化(二)

2014-11-24 12:57:24 · 作者: · 浏览: 1
标签: 减少 扫描 次数 优化
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
---------------