在Oracle中不用组函数(max().....,order by)求一列的最大值(要讲究效率) 在Oracle中不用组函数(max().....,order by)求一列的最大值(要讲究效率) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 求一列的最大值,MAX是最有效率的 SELECT MAX(empno) ,deptno FROM scott.emp GROUP BY deptno; SQL> create table test(aaa number);表已创建。SQL> begin 2 for i in 1..10000 loop 3 insert into test values(i); 4 end loop; 5 end; 6 /PL/SQL 过程已成功完成。SQL> commit;SQL> set autotrace on statisticsSQL> select * from ( 2 select * from test 3 order by aaa desc) 4 where rownum=1; AAA---------- 10000统计信息---------------------------------------------------------- 214 recursive calls 0 db block gets 92 consistent gets 28 physical reads 0 redo size 408 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processedSQL> alter system flush buffer_cache;系统已更改。SQL> select max(aaa) 2 from test; MAX(AAA)---------- 10000统计信息---------------------------------------------------------- 631 recursive calls 0 db block gets 139 consistent gets 39 physical reads 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 1 rows processedSQL> alter system flush buffer_cache;系统已更改。SQL> select aaa from ( 2 select aaa,row_number() over(order by aaa desc) rn 3 from test) 4 where rn=1; AAA---------- 10000统计信息---------------------------------------------------------- 221 recursive calls 0 db block gets 70 consistent gets 29 physical reads 0 redo size 408 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed 从这个例子看,好像MAX()开销最大。 TRY IT ..SQL> SELECT DWID,AMOUNT FROM TEST_DW; DWID AMOUNT---------- ---------- 1 5 1 5 2 5 2 5 1 6 2 8 1 107 rows selectedSQL> SQL> SELECT T1.DWID, 2 T1.AMOUNT 3 FROM TEST_DW T1 4 WHERE NOT EXISTS ( 5 SELECT 1 6 FROM TEST_DW T2 7 WHERE T1.DWID = T2.DWID 8 AND T1.AMOUNT < T2.AMOUNT 9 ); DWID AMOUNT---------- ---------- 2 8 1 10SQL> SELECT colume max FROM tableA WHERE tableA.colume NOT IN( SELECT colume notmax FROM tableA t1 JOIN tableA t2 ON (t1.colume < t2.colume)) 有木监控程序执行oracle语句的工具 远程数据库同步【分享】 关于触发器条件中的子查询 oracel的安装问题 关于数据库表结构的问题 谦虚的初学者裸身雪地跪求,如何配置ORACLE开发环境 一个数据库操作的问题 高分求救!!谁知道做报表时,excel连接Oracle出现中文乱码。 请推荐几本好的入门书,Oracle8i版本,可以从网上下载的。谢谢。 orcle 怎么检测到一张表的数据更新,然后把更新的数据同步到另一张表 oracle客户端最精简安装 求助:如何在AS4U6下文字界面安装 oracle 10.2.0.3 的补丁?
2 for i in 1..10000 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /PL/SQL 过程已成功完成。SQL> commit;SQL> set autotrace on statistics
SQL> select * from (
2 select * from test
3 order by aaa desc)
4 where rownum=1; AAA
----------
10000
统计信息
----------------------------------------------------------
214 recursive calls
0 db block gets
92 consistent gets
28 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processedSQL> alter system flush buffer_cache;系统已更改。SQL> select max(aaa)
2 from test; MAX(AAA)
----------
10000
统计信息
----------------------------------------------------------
631 recursive calls
0 db block gets
139 consistent gets
39 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processedSQL> alter system flush buffer_cache;系统已更改。SQL> select aaa from (
2 select aaa,row_number() over(order by aaa desc) rn
3 from test)
4 where rn=1; AAA
----------
10000
统计信息
----------------------------------------------------------
221 recursive calls
0 db block gets
70 consistent gets
29 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT DWID,AMOUNT FROM TEST_DW; DWID AMOUNT
---------- ----------
1 5
1 5
2 5
2 5
1 6
2 8
1 107 rows selectedSQL>
SQL> SELECT T1.DWID,
2 T1.AMOUNT
3 FROM TEST_DW T1
4 WHERE NOT EXISTS (
5 SELECT 1
6 FROM TEST_DW T2
7 WHERE T1.DWID = T2.DWID
8 AND T1.AMOUNT < T2.AMOUNT
9 ); DWID AMOUNT
---------- ----------
2 8
1 10SQL>