大侠们,帮个忙!我现在有一张表student,如下:create table student
(
sno varchar2(50),
sname varchar2(50),
ssex integer,
sage integer
);insert into student(sno,sname,ssex,sage)values(10,'张三',1,25);
insert into student(sno,sname,ssex,sage)values(10,'张三',1,25);
insert into student(sno,sname,ssex,sage)values(20,'李四',0,22);
insert into student(sno,sname,ssex,sage)values(30,'王五',1,23);
insert into student(sno,sname,ssex,sage)values(40,'陈六',0,24);
insert into student(sno,sname,ssex,sage)values(50,'刘七',0,20);
insert into student(sno,sname,ssex,sage)values(60,'赵八',1,21);
insert into student(sno,sname,ssex,sage)values(40,'陈六',0,24);
insert into student(sno,sname,ssex,sage)values(20,'李四',0,22);
insert into student(sno,sname,ssex,sage)values(70,'李四',1,21);
commit;
这张学生表中有很多重复数据,我想查出不重复的数据,提取rowid最小的那条。现在有两种方法:方法一:
select *
from scott.student t
where rowid=
(select max(rowid)
from scott.student
where sno=t.sno
and sname=t.sname
and ssex=t.ssex
and sage=t.sage);
执行计划
----------------------------------------------------------
Plan hash value: 2535995633-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | STUDENT | 10 | 160 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 16 | | |
|* 4 | TABLE ACCESS FULL| STUDENT | 1 | 16 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter(ROWID= (SELECT MAX(ROWID) FROM "SCOTT"."STUDENT"
"STUDENT" WHERE "SNO"=:B1 AND "SNAME"=:B2 AND "SSEX"=:B3 AND
"SAGE"=:B4))
4 - filter("SNO"=:B1 AND "SNAME"=:B2 AND "SSEX"=:B3 AND "SAGE"=:B4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
709 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed方法二:
select *
from scott.student
where (sno,sname,ssex,sage,rowid) in
(select sno,sname,ssex,sage,max(rowid)
from scott.student b
group by sno,sname,ssex,sage);
执行计划
----------------------------------------------------------
Plan hash value: 2978743956----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 6 (34)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 108 | 6 (34)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 10 | 920 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 160 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | STUDENT | 10 | 160 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY USER ROWID| STUDENT | 1 | 16 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 5 - filter("SNO"="$nso_col_1" AND "SNAME"="$nso_col_2" AND
"SSEX"="$nso_col_3" AND "SAGE"="$nso_col_4")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
708 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed方法一的CPU指标消耗较低,但是consistent gets较高,
方法二的CPU指标消耗较高,但是consistent gets较低。
不知道这两个句子哪个谁优谁劣?有什么比较的标准吗?或者说是根据什么指标判断出哪个执行效率好?
(
sno varchar2(50),
sname varchar2(50),
ssex integer,
sage integer
);insert into student(sno,sname,ssex,sage)values(10,'张三',1,25);
insert into student(sno,sname,ssex,sage)values(10,'张三',1,25);
insert into student(sno,sname,ssex,sage)values(20,'李四',0,22);
insert into student(sno,sname,ssex,sage)values(30,'王五',1,23);
insert into student(sno,sname,ssex,sage)values(40,'陈六',0,24);
insert into student(sno,sname,ssex,sage)values(50,'刘七',0,20);
insert into student(sno,sname,ssex,sage)values(60,'赵八',1,21);
insert into student(sno,sname,ssex,sage)values(40,'陈六',0,24);
insert into student(sno,sname,ssex,sage)values(20,'李四',0,22);
insert into student(sno,sname,ssex,sage)values(70,'李四',1,21);
commit;
这张学生表中有很多重复数据,我想查出不重复的数据,提取rowid最小的那条。现在有两种方法:方法一:
select *
from scott.student t
where rowid=
(select max(rowid)
from scott.student
where sno=t.sno
and sname=t.sname
and ssex=t.ssex
and sage=t.sage);
执行计划
----------------------------------------------------------
Plan hash value: 2535995633-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | STUDENT | 10 | 160 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 16 | | |
|* 4 | TABLE ACCESS FULL| STUDENT | 1 | 16 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter(ROWID= (SELECT MAX(ROWID) FROM "SCOTT"."STUDENT"
"STUDENT" WHERE "SNO"=:B1 AND "SNAME"=:B2 AND "SSEX"=:B3 AND
"SAGE"=:B4))
4 - filter("SNO"=:B1 AND "SNAME"=:B2 AND "SSEX"=:B3 AND "SAGE"=:B4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
709 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed方法二:
select *
from scott.student
where (sno,sname,ssex,sage,rowid) in
(select sno,sname,ssex,sage,max(rowid)
from scott.student b
group by sno,sname,ssex,sage);
执行计划
----------------------------------------------------------
Plan hash value: 2978743956----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 6 (34)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 108 | 6 (34)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 10 | 920 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 160 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | STUDENT | 10 | 160 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY USER ROWID| STUDENT | 1 | 16 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 5 - filter("SNO"="$nso_col_1" AND "SNAME"="$nso_col_2" AND
"SSEX"="$nso_col_3" AND "SAGE"="$nso_col_4")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
708 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed方法一的CPU指标消耗较低,但是consistent gets较高,
方法二的CPU指标消耗较高,但是consistent gets较低。
不知道这两个句子哪个谁优谁劣?有什么比较的标准吗?或者说是根据什么指标判断出哪个执行效率好?
解决方案 »
- oracle权限问题 sysdb和normal
- pb9.0连接oracle的问题
- oracle客户端连接不上服务器上的数据库
- 菜鸟关联子查询的疑问
- 关于SELCET检索字段中实现分支
- 请问oracle里对应mssql里addday()的函数是什么?
- 如何实现备份的文件名自动生成?
- asp 如何连接 远程 Oracle服务器
- 客户端登陆到Oracle Management Server时的错误!!!
- win8.1 oracle 11g 创建数据库失败
- imp命令导入dmp文件,提示IMP-00003:ORA-23404:ORA-06512:IMP-00017: ORA-00001: 等错误
- 急!如何在ORACLE中创建一个用户,只能访问我的表和视图
两种写法,感觉都不好!
用分析函数来替代你的写法!
select * from
(
select sno,sname,ssex,sage,row_number()over(partition by sno,sname,ssex,sage order by rowid, sno) rn from student
) where rn=1
(
select sno,sname,ssex,sage,row_number()over(partition by sno,sname,ssex,sage order by rowid, sno) rn from student
) where rn=1---------分析函数的写法效率比较高~~~~~