返回最大值所在行的几种方式比较
来源：互联网 发布：深圳可变数据印刷 编辑：程序博客网 时间：2024/05/17 08:16
本例用dba_objects建表，因表大小不一样，与你的结果可能也不一样，如要参考时需要实测
建立测试表如下
SQL> create table test1 as select * from dba_objects;Table createdSQL> create index idx_test1 on test1(owner,object_name);Index createdSQL> alter table test1 modify owner not null;Table alteredSQL> select count(*) from test1; COUNT(*)---------- 87034
用keep语句，这种方法object_name不能有重复值
SELECT * FROM test1 a INNER JOIN (SELECT MAX(b.rowid) keep(dense_rank LAST ORDER BY object_name) AS rid FROM test1 b GROUP BY b.owner) b ON b.rid = a.rowid;------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | | | | 1937 (100)| | 30 |00:00:07.31 | 1764 | | | ||* 1 | HASH JOIN| | 1 | 95M| 20G| 2296K| 1937 (14)| 00:00:24 | 30 |00:00:07.31 | 1764 | 1645K| 1645K| 1529K (0)|| 2 | VIEW| | 1 |97907 | 1147K| | 147(3)| 00:00:02 | 30 |00:00:02.66 | 514 | | | || 3 | SORT GROUP BY| | 1 |97907 | 9083K| | 147(3)| 00:00:02 | 30 |00:00:02.66 | 514 | 9216 | 9216 | 8192 (0)|| 4 | INDEX FAST FULL SCAN| IDX_TEST1 | 1 |97907 | 9083K| | 144(0)| 00:00:02 | 87034 |00:00:01.23 | 514 | | | || 5 | TABLE ACCESS FULL| TEST1 | 1 |97907 | 20M| | 347(1)| 00:00:05 | 87034 |00:00:01.18 | 1250 | | | |------------------------------------------------------------------------------------------------------------------------------------------------------------------
用row_number返回rowid后再自关联
SELECT /*+ use_nl(b,a)*/ * FROM test1 a INNER JOIN (SELECT ROWNUM AS sn, rid FROM (SELECT ROWID AS rid, row_number() over(PARTITION BY owner ORDER BY object_name) AS seq FROM test1) WHERE seq = 1) b ON b.rid = a.rowid;--------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name| Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time| Buffers | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 1 | | | 98456 (100)| |30 |00:00:07.15 | 537 | | | || 1 | NESTED LOOPS || 1 | 97907 | 22M| 98456 (1)| 00:19:42 |30 |00:00:07.15 | 537 | | | || 2 | VIEW || 1 | 97907 | 2390K| 529 (1)| 00:00:07 |30 |00:00:07.14 | 509 | | | || 3 | COUNT || 1 | | | | |30 |00:00:07.14 | 509 | | | ||* 4 | VIEW || 1 | 97907 | 2390K| 529 (1)| 00:00:07 |30 |00:00:07.14 | 509 | | | ||* 5 | WINDOW NOSORT || 1 | 97907 | 9083K| 529 (1)| 00:00:07 | 87034 |00:00:05.91 | 509 |9M| 1232K| || 6 | INDEX FULL SCAN | IDX_TEST1 | 1 | 97907 | 9083K| 529 (1)| 00:00:07 | 87034 |00:00:02.33 | 509 | | | || 7 | TABLE ACCESS BY USER ROWID| TEST1| 30 |1 | 219 |1 (0)| 00:00:01 |30 |00:00:00.01 | 28 | | | |--------------------------------------------------------------------------------------------------------------------------------------------------------------
用两个列自关联并强制走索引
SELECT /*+ index(a,idx_test1) */ a.* FROM test1 a WHERE (a.owner, a.object_name) IN (SELECT b.owner, MAX(b.object_name) FROM test1 b GROUP BY b.owner);Elapsed: 00:00:05.24------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows |A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | | | 2041 (100)| | 31 |00:00:02.45 | 611 | 1 | | | || 1 | NESTED LOOPS | |1 | 1 | 290 | 2041 (1)| 00:00:25 | 31 |00:00:02.45 | 611 | 1 | | | || 2 | NESTED LOOPS | |1 | 1 | 290 | 2041 (1)| 00:00:25 | 31 |00:00:02.44 | 580 | 0 | | | || 3 | VIEW | VW_NSO_1 |1 | 97907 | 7935K| 147 (3)| 00:00:02 | 30 |00:00:02.43 | 514 | 0 | | | || 4 | HASH GROUP BY | |1 | 1 | 7935K| 147 (3)| 00:00:02 | 30 |00:00:02.43 | 514 | 0 | 17M| 4248K| 2249K (0)|| 5 | INDEX FAST FULL SCAN | IDX_TEST1 |1 | 97907 | 7935K| 144 (0)| 00:00:02 | 87034 |00:00:01.14 | 514 | 0 | | | ||* 6 | INDEX RANGE SCAN | IDX_TEST1 | 30 | 1 | | 2 (0)| 00:00:01 | 31 |00:00:00.01 |66 | 0 | | | || 7 | TABLE ACCESS BY INDEX ROWID| TEST1 | 31 | 1 | 207 | 3 (0)| 00:00:01 | 31 |00:00:00.01 |31 | 1 | | | |------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ index(a,idx_test1) */ a.* FROM test1 a WHERE a.object_name = (SELECT MAX(b.object_name) FROM test1 b WHERE b.owner = a.owner);--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows |A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | | | | 67184 (100)| | 31 |00:00:12.82 | 66062 | 18 | | | ||* 1 | HASH JOIN | |1 | 144K|39M| 9088K| 67184 (1)| 00:13:27 | 31 |00:00:12.82 | 66062 | 18 | 1229K| 1229K| 1908K (0)|| 2 | VIEW | VW_SQ_1 |1 | 97907 | 7935K| | 147 (3)| 00:00:02 | 30 |00:00:02.67 | 514 | 0 | | | || 3 | HASH GROUP BY | |1 | 97907 | 7935K| | 147 (3)| 00:00:02 | 30 |00:00:02.67 | 514 | 0 | 17M| 4248K| 3145K (0)|| 4 | INDEX FAST FULL SCAN | IDX_TEST1 |1 | 97907 | 7935K| | 144 (0)| 00:00:02 | 87034 |00:00:01.21 | 514 | 0 | | | || 5 | TABLE ACCESS BY INDEX ROWID| TEST1 |1 | 97907 |19M| | 65581 (1)| 00:13:07 | 87034 |00:00:06.49 | 65548 | 18 | | | || 6 | INDEX FULL SCAN | IDX_TEST1 |1 | 97907 | | | 529 (1)| 00:00:07 | 87034 |00:00:01.30 | 509 | 0 | | | |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
用row_number()返回各组第一行
SELECT * FROM(SELECT test1.*, row_number() over(PARTITION BY owner ORDER BY object_name) AS seq FROM test1)WHERE seq = 1;---------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows |A-Time | Buffers | OMem | 1Mem | Used-Mem |---------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | | | | 4751 (100)| | 30 |00:00:03.40 | 1248 | | | ||* 1 | VIEW | |1 | 97907 |20M| | 4751 (1)| 00:00:58 | 30 |00:00:03.40 | 1248 | | | ||* 2 | WINDOW SORT PUSHED RANK| |1 | 97907 |19M|23M| 4751 (1)| 00:00:58 | 65 |00:00:03.39 | 1248 |14M| 1437K| 12M (0)|| 3 | TABLE ACCESS FULL | TEST1 |1 | 97907 |19M| | 347 (1)| 00:00:05 | 87034 |00:00:01.26 | 1248 | | | |---------------------------------------------------------------------------------------------------------------------------------------------------------------
0 0
- 返回最大值所在行的几种方式比较
- sql如何找到某字段的最大值所在行
- python提取关键字所在行的后边几行
- 获取数据表分组的最大值所在行所有信息的方法
- DataGridView重查后，返回原来所在行
- 调用HttpHanlder的几种返回方式
- 返回按钮的几种实现方式
- Struts2返回json的几种方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- SpringMVC返回值的几种方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- SpringMVC的几种返回方式
- 正则表达式使用 NSPredicate使用
- Intellij IDEA 调试Hadoop 源码
- ECMALL的登录过程机制解析
- Java编程思想读书笔记——第四章
- Wireshark抓包和tcpdump
- 返回最大值所在行的几种方式比较
- linux下printf打印带颜色
- HTTP常用请求--响应实例
- 数论专题训练D
- C# Asp.net Active控件开发全过程
- 软件开发中团队首领的好坏之分
- jQuery实现图片延迟加载
- [Mugeda HTML5技术教程之5] 创建新作品
- MYSQL导入导出.sql文件
