减少表扫描次数的优化
最近在面试和学习中遇到了一些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 | 1
