最近在面试和学习中遇到了一些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 |
---------------------------------------------------------------------------
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、分析函数 的方法效率最高,这个例子可以应用到查询用户最后登录的信息上。
以后遇到其他情况再慢慢更新