Oracle执行计划的问题

[palosky]

大侠们，帮个忙！我现在有一张表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较低。


不知道这两个句子哪个谁优谁劣？有什么比较的标准吗？或者说是根据什么指标判断出哪个执行效率好？

[cosio]

[Quote=引用楼主 palosky 的回复:]
大侠们，帮个忙！我现在有一张表student，如下：

SQL code

create table student
(
sno varchar2(50),
sname varchar2(50),
ssex integer,
sage integer
);

insert into student(sno,sname,ssex,sage)values……
[/Quote]

两种写法,感觉都不好!
用分析函数来替代你的写法!

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 * 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

---------分析函数的写法效率比较高~~~~~