我有一个表A ，它的字段有id,name,class,sex,date然后，name的值，是可以重复的。我想做一个查询，查询的结果中，不能有重复的name（重复的name中，保留date最大的那条记录）方法1：
select id,name,class,sex,date
from a t
where not exists(select 1 from a t1
where t.name=t1.name and t.date<t1.date)方法2:
select id,name,class,sex,date
from a t
where t.date = (select max(t1.date)
from a t1
where t1.name = t.name)
这是我用的两个方法，导师告诉我说，要避免用not exists，因为执行效率低，但是我发现我写的第二种方法的执行花的时间貌似更久。希望哪位大侠能帮我想一种执行效率高一点的 方法，如果可以的话，更希望能做下分析~~谢谢了
select id,name,class,sex,date
from a t
where not exists(select 1 from a t1
where t.name=t1.name and t.date<t1.date)方法2:
select id,name,class,sex,date
from a t
where t.date = (select max(t1.date)
from a t1
where t1.name = t.name)
这是我用的两个方法，导师告诉我说，要避免用not exists，因为执行效率低，但是我发现我写的第二种方法的执行花的时间貌似更久。希望哪位大侠能帮我想一种执行效率高一点的 方法，如果可以的话，更希望能做下分析~~谢谢了
在查询中，要尽量用exists来代替in
另外可以试试这种方法select id,name,class,sex,date,
from
(select id,name,class,sex,date ,
row_number()over(partition by name order by date desc) row_num
from a)
where row_num=1
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> CREATE TABLE a AS
2 SELECT t.OBJECT_ID id, t.OBJECT_NAME NAME, t.CREATED FROM dba_objects t;表已创建。SQL> INSERT INTO a
2 SELECT id, NAME, created + 10
3 FROM (SELECT * FROM a ORDER BY dbms_random.value)
4 WHERE rownum <= 2000;已创建2000行。SQL> commit;提交完成。SQL> set autot trace exp stat;SQL> set timing on;
SQL> -------------第一部分测试，未建索引，未收集统计数据------------------
SQL> --方法1：
SQL> SELECT id, NAME, created
2 FROM a t
3 WHERE NOT EXISTS (SELECT 1
4 FROM a t1
5 WHERE t.name = t1.name
6 AND t.created < t1.created);已选择43799行。已用时间: 00: 00: 01.04执行计划
----------------------------------------------------------
Plan hash value: 260003889-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43533 | 6929K| | 584 (2)| 00:00:08 |
|* 1 | HASH JOIN RIGHT ANTI| | 43533 | 6929K| 3896K| 584 (2)| 00:00:08 |
| 2 | TABLE ACCESS FULL | A | 45824 | 3356K| | 87 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL | A | 45824 | 3938K| | 87 (3)| 00:00:02 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("T"."NAME"="T1"."NAME")
filter("T"."CREATED"<"T1"."CREATED")Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3560 consistent gets
0 physical reads
0 redo size
1544889 bytes sent via SQL*Net to client
32494 bytes received via SQL*Net from client
2921 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43799 rows processed
SQL> --方法2:
SQL> SELECT id, NAME, created
2 FROM a t
3 WHERE t.created =
4 (SELECT MAX(t1.created) FROM a t1 WHERE t1.name = t.name);已选择43799行。已用时间: 00: 00: 01.09执行计划
----------------------------------------------------------
Plan hash value: 1255104471----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83739 | 13M| | 591 (3)| 00:00:08 |
|* 1 | HASH JOIN | | 83739 | 13M| 3896K| 591 (3)| 00:00:08 |
| 2 | VIEW | VW_SQ_1 | 45824 | 3356K| | 93 (9)| 00:00:02 |
| 3 | HASH GROUP BY | | 45824 | 3356K| | 93 (9)| 00:00:02 |
| 4 | TABLE ACCESS FULL| A | 45824 | 3356K| | 87 (3)| 00:00:02 |
| 5 | TABLE ACCESS FULL | A | 45824 | 3938K| | 87 (3)| 00:00:02 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("T"."CREATED"="VW_COL_1" AND "NAME"="T"."NAME")Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3560 consistent gets
0 physical reads
0 redo size
1544889 bytes sent via SQL*Net to client
32494 bytes received via SQL*Net from client
2921 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43799 rows processed
SQL> --收集统计数据
SQL> exec dbms_stats.gather_table_stats(user,'a');PL/SQL 过程已成功完成。提交完成。
已用时间: 00: 00: 00.54
SQL> SQL> --方法1：
SQL> SELECT id, NAME, created
2 FROM a t
3 WHERE NOT EXISTS (SELECT 1
4 FROM a t1
5 WHERE t.name = t1.name
6 AND t.created < t1.created);已选择43799行。已用时间: 00: 00: 01.06执行计划
----------------------------------------------------------
Plan hash value: 260003889-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49747 | 3352K| | 409 (2)| 00:00:05 |
|* 1 | HASH JOIN RIGHT ANTI| | 49747 | 3352K| 2304K| 409 (2)| 00:00:05 |
| 2 | TABLE ACCESS FULL | A | 52365 | 1687K| | 87 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL | A | 52365 | 1840K| | 87 (3)| 00:00:02 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("T"."NAME"="T1"."NAME")
filter("T"."CREATED"<"T1"."CREATED")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3560 consistent gets
0 physical reads
0 redo size
1544889 bytes sent via SQL*Net to client
32494 bytes received via SQL*Net from client
2921 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43799 rows processedSQL> --方法2:
SQL> SELECT id, NAME, created
2 FROM a t
3 WHERE t.created =
4 (SELECT MAX(t1.created) FROM a t1 WHERE t1.name = t.name);已选择43799行。已用时间: 00: 00: 01.09执行计划
----------------------------------------------------------
Plan hash value: 2734318881-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 4002 | | 423 (6)| 00:00:06 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 58 | 4002 | | 423 (6)| 00:00:06 |
|* 3 | HASH JOIN | | 91084 | 6137K| 2304K| 410 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| A | 52365 | 1687K| | 87 (3)| 00:00:02 |
| 5 | TABLE ACCESS FULL| A | 52365 | 1840K| | 87 (3)| 00:00:02 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("CREATED"=MAX("T1"."CREATED"))
3 - access("T1"."NAME"="T"."NAME")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3560 consistent gets
0 physical reads
0 redo size
1544889 bytes sent via SQL*Net to client
32494 bytes received via SQL*Net from client
2921 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43799 rows processed
SQL> --创建索引
SQL> create index idx_a_name on a(name);索引已创建。SQL> create index idx_a_created on a(created);索引已创建。已用时间: 00: 00: 00.70SQL> exec dbms_stats.gather_table_stats(user,'a',cascade=>true);PL/SQL 过程已成功完成。提交完成。
已用时间: 00: 00: 01.46
SQL> SQL> --方法1：
SQL> SELECT id, NAME, created
2 FROM a t
3 WHERE NOT EXISTS (SELECT 1
4 FROM a t1
5 WHERE t.name = t1.name
6 AND t.created < t1.created);已选择43799行。已用时间: 00: 00: 01.06执行计划
----------------------------------------------------------
Plan hash value: 260003889-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49747 | 3352K| | 409 (2)| 00:00:05 |
|* 1 | HASH JOIN RIGHT ANTI| | 49747 | 3352K| 2304K| 409 (2)| 00:00:05 |
| 2 | TABLE ACCESS FULL | A | 52365 | 1687K| | 87 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL | A | 52365 | 1840K| | 87 (3)| 00:00:02 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("T"."NAME"="T1"."NAME")
filter("T"."CREATED"<"T1"."CREATED")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3560 consistent gets
0 physical reads
0 redo size
1544889 bytes sent via SQL*Net to client
32494 bytes received via SQL*Net from client
2921 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43799 rows processed
SQL> --方法2:
SQL> SELECT id, NAME, created
2 FROM a t
3 WHERE t.created =
4 (SELECT MAX(t1.created) FROM a t1 WHERE t1.name = t.name);已选择43799行。已用时间: 00: 00: 01.18执行计划
----------------------------------------------------------
Plan hash value: 2734318881-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 4002 | | 423 (6)| 00:00:06 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 58 | 4002 | | 423 (6)| 00:00:06 |
|* 3 | HASH JOIN | | 91084 | 6137K| 2304K| 410 (3)| 00:00:05 |
| 4 | TABLE ACCESS FULL| A | 52365 | 1687K| | 87 (3)| 00:00:02 |
| 5 | TABLE ACCESS FULL| A | 52365 | 1840K| | 87 (3)| 00:00:02 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("CREATED"=MAX("T1"."CREATED"))
3 - access("T1"."NAME"="T"."NAME")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
658 consistent gets
0 physical reads
0 redo size
1916129 bytes sent via SQL*Net to client
32494 bytes received via SQL*Net from client
2921 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43799 rows processed
select id,name,class,sex,date,
from
(select id,name,class,sex,date ,
row_number()over(partition by name order by date desc) row_num
from a)
where row_num=1
from a,(
select name,max(date),rowid
from a
group by name
) bwhere a.rowid=b.rowid