oracle 10g之 max 、min的写法
发布时间：2020-12-12 14:26:28 所属栏目：百科 来源：网络整理
导读：一、有主键情况 1、单查最大的主键object_id（cost:2、consistent gets:100） select max(object_id) from t; 2、单查最小的主键object_id(cost:2、consistent gets:69) select min(object_id) from t; 3、一起查max(object_id),min(object_id) (cost:47、co
|
一、有主键情况
1、单查最大的主键object_id（cost:2、consistent gets:100）
select max(object_id) from t;
2、单查最小的主键object_id(cost:2、consistent gets:69) select min(object_id) from t; 3、一起查max(object_id),min(object_id) (cost:47、consistent gets 224) select max(object_id),min(object_id) from t; 4、最优一起查询(cost:4、consistent gets:138) select max,min from (select max(object_id) max from t)a,(select min(object_id) min from t)b; 结论： 在字段为主键有索引的情况下，单查单个max、min效率最快 在字段为主键有索引的情况下，优化方式查询最快 在字段为主键有索引的情况下，直接max、min的方式最慢 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 二、在t表中object_id不是主键没有唯一索引 并且有null值的情况 SQL> select max(object_id) from t; MAX(OBJECT_ID) -------------- 178100 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 288 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 48 recursive calls 0 db block gets 1099 consistent gets 1024 physical reads 0 redo size 343 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select min(object_id) from t; MIN(OBJECT_ID) -------------- 2 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 288 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1095 consistent gets 0 physical reads 0 redo size 342 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select max(object_id),min(object_id) from t; MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 178100 2 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 288 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 1095 consistent gets 0 physical reads 0 redo size 406 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select max,min from (select max(object_id) max from t) a,(select min(object_id) min from t) b; MAX MIN ---------- ---------- 178100 2 执行计划 ---------------------------------------------------------- Plan hash value: 1937292215 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 575 (1)| 00:00:07 | | 1 | NESTED LOOPS | | 1 | 26 | 575 (1)| 00:00:07 | | 2 | VIEW | | 1 | 13 | 288 (1)| 00:00:04 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 | | 5 | VIEW | | 1 | 13 | 288 (1)| 00:00:04 | | 6 | SORT AGGREGATE | | 1 | 13 | | | | 7 | TABLE ACCESS FULL| T | 69433 | 881K| 288 (1)| 00:00:04 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 7 recursive calls 0 db block gets 2190 consistent gets 0 physical reads 0 redo size 384 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 结论： 优化的方式查询还没有一起查询快，一起查询及单个查询花费的cpu几乎一样多。 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 最终结论: 本实验为看别人思路模拟的本地环境测试，表明需要根据实际场景进行sql优化，不一定在某个环境及情况下优化过的sql就永远是最好的。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |