oracle 10g之 max 、min的写法
来源:互联网 发布:关于交通事故的数据 编辑:程序博客网 时间:2024/05/29 08:35
一、有主键情况
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就永远是最好的。
阅读全文
0 0
- oracle 10g之 max 、min的写法
- oracle性能优化技巧:max(),min()的高效写法
- Oracle(三) - Oracle关于Max和Min函数的几种写法比较
- Max Min的使用
- oracle min(date), max(date)
- oracle min(date), max(date)
- oracle 的使用 聚合函数的 max和min 使用
- 归纳法之找max&min
- VS2015--win32工程配置的一些想法之算法min/max与windows中的 min/max宏冲突
- max(min)-device-width和max(min)-width的区别
- max(min)-device-width和max(min)-width的区别
- max min的小问题
- max/min函数的用法
- Oracle 排序 max() min() SQL语句
- oracle max()函数和min()函数
- oracle max()函数和min()函数
- oracle max()函数和min()函数
- oracle max()函数和min()函数
- vue项目打包后打开空白解决办法
- 训练集---dp崭露头角篇
- NOIP 考前提醒
- Asp.Net添加水印
- MFC :atoi不能将"CString" 转化为"char *"
- oracle 10g之 max 、min的写法
- SQL Server 数据库表结构导出
- BZOJ 4726 [POI 2017] 树DP 解题报告
- 伺服电机、舵机、步进电机的区别
- 复制对象时不要忘记每一个成分
- 链式队列一种实现
- UVa1388
- FYN OI 奋斗之路7~
- POJ-1001-Exponentiation