同时查询最大最小值的效率问题
来源:互联网 发布:linux mysql5.7 编辑:程序博客网 时间:2024/05/21 19:37
有时候我们需要同时查询一部分数据中的最大和最小值,如何才能最快取得我们要的结果呢?
--在CREATED字段创建了索引,如果直接使用MAX和MIN函数来获取最大最小值,如下所示:
--消耗时间:Elapsed: 00:00:00.54,从执行计划我们可以看出改语句并没有走索引,因为这里没有指定谓词。
--如果指定谓词之后,则结果如下,并没有因此而提高效率,
--因为同时指定最大MAX最小MIN值并不能走索引的 INDEX FULL SCAN (MIN/MAX) 扫描,
--对于INDEX FAST FULL SCAN的读取方式是:从段头开始,读取包含位图块,ROOT BLOCK,所有的BRANCH BLOCK,LEAF BLOCK,
--读取的顺序完全有物理存储位置决定,并采取多块读,每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。
--查询某个表记录总数的时候,往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的,但是在这里并不是最合适的。
--INDEX FULL SCAN的读取方式是:先定位到索引树的根节点,然后分支,再读取叶子节点,根据叶子节点的双向链表顺序读取,
--这种读取方式是顺序的并且也是经过排序的,所以我们希望走这种方式:
--既然放在一起查询不行,我们将两个函数分开来做:
--分别让其走索引的全表扫描:
--执行时间:Elapsed: 00:00:00.10
我们知道索引已经对数据进行了排序,所以这时走索引效率是最高的,下面看一个例子
--在一个有将近两百万数据的表中创建日期索引,需要同时取出日期的最大值和最小值:
SQL> select COUNT(*) from USER_OBJECTS_TMP t; COUNT(*)---------- 1933312CREATE INDEX user_objects_create_dt ON user_objects_tmp(created) TABLESPACE tbs_lubinsu_idx;SQL> desc user_objects_tmpName Type Nullable Default Comments -------------- ------------- -------- ------- -------- OBJECT_NAME VARCHAR2(128) Y SUBOBJECT_NAME VARCHAR2(30) Y OBJECT_ID NUMBER Y DATA_OBJECT_ID NUMBER Y OBJECT_TYPE VARCHAR2(19) Y CREATED DATE Y LAST_DDL_TIME DATE Y TIMESTAMP VARCHAR2(19) Y STATUS VARCHAR2(7) Y TEMPORARY VARCHAR2(1) Y GENERATED VARCHAR2(1) Y SECONDARY VARCHAR2(1) Y ;
--在CREATED字段创建了索引,如果直接使用MAX和MIN函数来获取最大最小值,如下所示:
--消耗时间:Elapsed: 00:00:00.54,从执行计划我们可以看出改语句并没有走索引,因为这里没有指定谓词。
SQL> set timing onSQL> set autotrace traceonlySQL> set linesize 200 SQL> SELECT MIN(created), MAX(created) AS MIN FROM user_objects_tmp;Elapsed: 00:00:00.54Execution Plan----------------------------------------------------------Plan hash value: 3066201300---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 1718 (4)| 00:00:21 || 1 | SORT AGGREGATE | | 1 | 9 | | || 2 | TABLE ACCESS FULL| USER_OBJECTS_TMP | 1600K| 13M| 1718 (4)| 00:00:21 |---------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 5 recursive calls 0 db block gets 8639 consistent gets 659 physical reads 0 redo size 481 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--如果指定谓词之后,则结果如下,并没有因此而提高效率,
--因为同时指定最大MAX最小MIN值并不能走索引的 INDEX FULL SCAN (MIN/MAX) 扫描,
--对于INDEX FAST FULL SCAN的读取方式是:从段头开始,读取包含位图块,ROOT BLOCK,所有的BRANCH BLOCK,LEAF BLOCK,
--读取的顺序完全有物理存储位置决定,并采取多块读,每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。
--查询某个表记录总数的时候,往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的,但是在这里并不是最合适的。
--INDEX FULL SCAN的读取方式是:先定位到索引树的根节点,然后分支,再读取叶子节点,根据叶子节点的双向链表顺序读取,
--这种读取方式是顺序的并且也是经过排序的,所以我们希望走这种方式:
SQL> SELECT MIN(created), MAX(created) AS MIN FROM user_objects_tmp a WHERE a.created IS NOT NULL;Elapsed: 00:00:00.62Execution Plan----------------------------------------------------------Plan hash value: 3784617757------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 1661 (1)| 00:00:20 || 1 | SORT AGGREGATE | | 1 | 9 | | ||* 2 | INDEX FAST FULL SCAN| USER_OBJECTS_CREATE_DT | 1600K| 13M| 1661 (1)| 00:00:20 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("A"."CREATED" IS NOT NULL)Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 5 recursive calls 0 db block gets 8647 consistent gets 267 physical reads 0 redo size 481 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--既然放在一起查询不行,我们将两个函数分开来做:
--分别让其走索引的全表扫描:
--执行时间:Elapsed: 00:00:00.10
SQL> SELECT MIN, MAX 2 FROM (SELECT MIN(created) AS MIN FROM user_objects_tmp) a, 3 (SELECT MAX(created) AS MAX FROM user_objects_tmp) b;Elapsed: 00:00:00.10Execution Plan----------------------------------------------------------Plan hash value: 4210122108-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 18 | 3437 (4)| 00:00:42 || 1 | NESTED LOOPS | | 1 | 18 | 3437 (4)| 00:00:42 || 2 | VIEW | | 1 | 9 | 1718 (4)| 00:00:21 || 3 | SORT AGGREGATE | | 1 | 9 | | || 4 | INDEX FULL SCAN (MIN/MAX)| USER_OBJECTS_CREATE_DT | 1600K| 13M| | || 5 | VIEW | | 1 | 9 | 1718 (4)| 00:00:21 || 6 | SORT AGGREGATE | | 1 | 9 | | || 7 | INDEX FULL SCAN (MIN/MAX)| USER_OBJECTS_CREATE_DT | 1600K| 13M| | |-------------------------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 195 recursive calls 0 db block gets 159 consistent gets 72 physical reads 0 redo size 472 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed--可见,这种情况效率是最高的SELECT MIN, MAXFROM (SELECT MIN(created) AS MIN FROM user_objects_tmp) a, (SELECT MAX(created) AS MAX FROM user_objects_tmp) b;
- 同时查询最大最小值的效率问题
- 同时得到最大最小值的最优算法
- 单表查询某列最大最小值的性能问题
- 同时求最大最小值
- 查询最大与最小值
- POJ 3264 ST表(RMQ问题:查询区间最大最小值)
- 同时查找数组中最大和最小值
- 冒泡排序同时得到最大、最小值位置
- 分组查询最大/最小值sql
- 同时查询出最大值与最小值
- 类型的最大最小值
- 数据类型的最大最小值
- 用递归二分法实现同时获得一个数组内的最大最小值
- 同时创建两个STL容器map的效率问题
- 线段树+最大最小值查询+敌兵布阵
- 线段树查询区间最大最小值
- 转载access的分页查询效率问题
- access的分页查询效率问题
- 后缀数组--倍增法注解
- java学习笔记之线程并发库
- 抽象类可以继承实体类吗?
- oracle 11g 在线重定义(online redefinition)介绍
- 《JavaScript语言精粹》--附录A :毒瘤
- 同时查询最大最小值的效率问题
- 某大型银行深化系统之二:设计策略
- poj 1488 TEX Quotes(简单的串)
- 设计模式一 概念
- 收集的有效的修改按钮背景色的方法
- oracle11g新特性
- viewState 与session比较
- 5. Drawable、Bitmap、Canvas和Paint的关系
- 祝福自己