Oracle高级查询_技术笔记

斯巴达汉子 2022-01-31

找到员工表中薪水大于本部门平均薪水的员工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);

-- 用多表查询实现
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal;

--- 查询sql语句运算效率
explain plan for
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);

select * from table (dbms_xplan.display);

explain plan for
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal;

select * from table (dbms_xplan.display);

************************************************************************
SQL> select rownum,r,empno,ename,sal
2 from (select rownum r,empno,ename,sal
3 from (select rownum,empno,ename,sal from emp order by sal desc) e1
4 where rownum<=8) e2
5 where r>=5;

ROWNUM R EMPNO ENAME SAL
---------- ---------- ---------- ---------- ----------
1 5 7698 BLAKE 2850
2 6 7782 CLARK 2450
3 7 7499 ALLEN 1600
4 8 7844 TURNER 1500

SQL> select e.empno,e.ename,e.sal,d.avgsal
2 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
3 where e.deptno=d.deptno and e.sal > d.avgsal;

EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7499 ALLEN 1600 1566.66667
7566 JONES 2975 2175
7698 BLAKE 2850 1566.66667
7788 SCOTT 3000 2175
7839 KING 5000 2916.66667
7902 FORD 3000 2175

已选择6行。

SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
2 from emp e
3 where sal>(select avg(sal) from emp where deptno=e.deptno);

EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7499 ALLEN 1600 1566.66667
7566 JONES 2975 2175
7698 BLAKE 2850 1566.66667
7788 SCOTT 3000 2175
7839 KING 5000 2916.66667
7902 FORD 3000 2175

已选择6行。

SQL> explain plan for
2 select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
3 from emp e
4 where sal>(select avg(sal) from emp where deptno=e.deptno);

已解释。

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
Plan hash value: 2385781174

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 8 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL | EMP | 5 | 35 | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 43 | 8 (25)| 00:00:01 |
| 4 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 5 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
| 6 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

2 - filter("DEPTNO"=:B1)
3 - access("ITEM_1"="E"."DEPTNO")
filter("SAL">"AVG(SAL)")

已选择21行。

SQL> explain plan for
2 select e.empno,e.ename,e.sal,d.avgsal
3 from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
4 where e.deptno=d.deptno and e.sal > d.avgsal;

已解释。

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 43 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
-----------------------------------------------------------------------------

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

1 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."SAL">"D"."AVGSAL")

已选择18行。

0赞 · 0采集
慕设计4302654 2021-10-13

案例二：
使用explain plan for 得到Sql语句的执行计划，查看其耗费的系统资源，查看执行计划：select* from table (dbms_xplan.display)；得到使用相关子查询的方式效果更好一些

0赞 · 0采集
qq_羽悦_03291187 2020-09-28

查看执行计划，耗费CPU资源

截图
0赞 · 0采集
qq_羽悦_03291187 2020-09-28

explain plan for 生成SQL的执行计划

截图
0赞 · 0采集
qq_羽悦_03291187 2020-09-28

多表查询

截图
0赞 · 0采集

zycsyd 2020-08-07

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno)


select e.empna,e.ename,e.sal,d.avgsal
from emp e,select deptno,avg(sal) avgsal from emp group by deptno)d

where e.deptno=d.deptno and e.sal>d.avgsal

0赞 · 0采集

慕粉13485244983 2020-03-15

explain pian for 语句 select from table(dbms.)

截图
0赞 · 0采集
qq_慕标5384168 2020-03-04

示例2
找到员工表中薪水大于本部门平均薪水的员工
1、相关子查询
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal >(select avg(sal) from emp where deptno=e.deptno);
-------------------------------------------
2、多表查询嵌套子查询
select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal;
---------------------------------------------
写执行计划
explain plan for
select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal;
查看执行计划
select * from table (dbms_xplan.display);
用相关子查询方式更省资源

0赞 · 0采集
BZzzzzzz 2020-01-04

explain plan for
select 。。。
select * from table (dbms_xplan.display);
相关子查询，需要先从主查询查询出一个值后，子查询才能执行查询

截图
0赞 · 0采集
慕粉2136255539 2019-08-14

针对此问题用相关子查询比用多表查询效率高很多

截图
0赞 · 0采集
慕粉2136255539 2019-08-14

两种方式求某部门员工的薪资大于本部分的平均薪资的相关信息方法一：相关子查询方法二：多表查询

截图
0赞 · 0采集
慕仰5014332 2019-07-26

执行计划: explain plan for sql语句
查看执行计划:select * from table(dbms_xplan.display);

0赞 · 1采集
Amousy 2019-03-31

执行计划对比
样式：
explain plan for
select ...
from...
where...
select * from table(dbms_xplan.display);【查看】

0赞 · 0采集
qq_星_20 2019-03-17
当一个结果有两种解法，就是实现一个功能可以是多种sql语句时，可以通过执行计划查看哪个的效率更高 -----
1. explain plan for sql语句；
2. select * from table (dbm_xplan.display);
0赞 · 0采集
枫林天涯 2019-02-20

oracle：执行计划生成
explain plan for sql语句；
查询执行计划消耗资源
select * from table (dbms_xplan.display);
相关子查询优于多表查询

0赞 · 1采集
weibo_吉祥物mz_0 2019-01-09

使用相关子查询比多表查询性能更好，消耗cpu的性能更少

0赞 · 1采集
weibo_fate自身难保_0 2018-12-19

explain for select a from b;
select * from table(dbms_xplan.display);

select * from v$sql;
select * from v$sql_plan where sql_id;

select * from v$sql
select * from table(dbms_xplan.display_cursor(sql_id,0));

select * from table(dbms_xplan.display_cursor(nul,null));

截图
0赞 · 1采集
慕斯9756157 2018-06-03

--执行计划：explain plan for select .......

--查看执行计划：select * from table(dbms_xplan.display);

explain plan for
(
select *
from emp e,(select e.deptno,avg(e.sal) avgsal from emp e group by e.deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal
)
select * from table(dbms_xplan.display);

0赞 · 1采集
周班班 2018-05-29

执行计划：explain plan from select .......
查看执行计划：select * from table(dbms_xplan.display);
select empno,empname,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e
where sal>(select avg(sal) from emp where e.deptno=deptno)

0赞 · 0采集
周班班 2018-05-29

select e.depno,e.empno,e.empname,f.avgsal
from emp e,(select depno,avg(sal) avgsal from emp group by depno) f
where e.depno=f.depno and e.sal>f.avgsal

0赞 · 0采集
小光头 2018-05-21

查询工资大于部门平均工资的员工
--相关子查询：
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsalfrom emp e
where sal>(select avg(sal) from emp where deptno=e.deptno)
--多表查询：
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal
--比较解释计划窗口的相关子查询比多表查询占用cpu少

0赞 · 0采集

小乌or小鸟 2018-04-04

explain plan for
select * from table;
select * from table(dbms xplan.display);

0赞 · 0采集

qq_堯壽_0 2018-01-24

001

截图
0赞 · 0采集
火灵云51cto 2018-01-14

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);

0赞 · 0采集
进击第一护妻狂魔 2017-11-23

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);//比较优先 ---------------------------------------------------------- select * from table(dbms_xplan.display); select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal;

0赞 · 0采集
慕小寒 2017-11-06

如何查看执行计划的方法，通过对比语句执行时占用cpu的资源大小来确定用那种方法，非常适用于几个大表关联时判断不同方法打我效率问题！切记

截图
0赞 · 0采集
nize1989 2017-10-13

怎么查看执行计划

截图
0赞 · 0采集
nize1989 2017-10-13

通过sql语句的执行计划判断那条sql号

截图
0赞 · 0采集
jazia 2017-08-27

案例二： --查询本部门薪水大于平均工资的员工；（使用表连接查询）相关子查询： select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) 多表查询： select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal 相关子查询比多表查询占用cpu少

0赞 · 1采集
秋天的木头 2017-07-20

执行计划

截图
0赞 · 0采集

数据加载中...