SUM等聚合函数的优化
认证徽章论坛徽章:40
ilsz 发表于
暂时还拿不到跟踪记录，但是我一直在考虑索引会对SUM操作起作用吗？因为他们SUM的是表中的所有行记录，索 ...
空值对SUM（）无影响，而索引中是不存储空值的。所以，对索引中的值进行SUM（）不会影响最终结果的正确性。因此，适当的索引一定会起到积极的作用的。
如10楼所提供的实验所示，SUM（）完全可以利用到索引。所以，我觉得现在的关键是要搞清楚：为什么不加WHERE条件的情况下，对指定列（该列为索引列）的SUM（）操作，没有走索引的原因。
求职 : 论坛徽章:10
C:\Users\Administrator&sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 25 11:10:58 2014
Copyright (c) , Oracle.&&All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL& create table tb as select * from dba_
Table created.
SQL& create index ind_tb_objid on tb(object_id);
Index created.
SQL& select sum(object_id)
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------
| Id&&| Operation& && && & | Name | Rows&&| Bytes | Cost (%CPU)| Time& &&&|
---------------------------------------------------------------------------
|& &0 | SELECT STATEMENT& &|& && &|& &&&1 |& & 13 |& &154& &(1)| 00:00:02 |
|& &1 |&&SORT AGGREGATE& & |& && &|& &&&1 |& & 13 |& && && && &|& && && & |
|& &2 |& &TABLE ACCESS FULL| TB& &| 56761 |& &720K|& &154& &(1)| 00:00:02 |
---------------------------------------------------------------------------
& &- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
& && && &28&&recursive calls
& && && & 0&&db block gets
& && &&&776&&consistent gets
& && && & 0&&physical reads
& && && & 0&&redo size
& && &&&525&&bytes sent via SQL*Net to client
& && &&&492&&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 /*+index_ffs(tb ind_tb_objid)*/ sum(object_id)
Execution Plan
----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------
| Id&&| Operation& && && & | Name | Rows&&| Bytes | Cost (%CPU)| Time& &&&|
---------------------------------------------------------------------------
|& &0 | SELECT STATEMENT& &|& && &|& &&&1 |& & 13 |& &154& &(1)| 00:00:02 |
|& &1 |&&SORT AGGREGATE& & |& && &|& &&&1 |& & 13 |& && && && &|& && && & |
|& &2 |& &TABLE ACCESS FULL| TB& &| 56761 |& &720K|& &154& &(1)| 00:00:02 |
---------------------------------------------------------------------------
& &- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
& && && & 4&&recursive calls
& && && & 0&&db block gets
& && &&&774&&consistent gets
& && && & 0&&physical reads
& && && & 0&&redo size
& && &&&525&&bytes sent via SQL*Net to client
& && &&&492&&bytes received via SQL*Net from client
& && && & 2&&SQL*Net roundtrips to/from client
& && && & 0&&sorts (memory)
& && && & 0&&sorts (disk)
& && && & 1&&rows processed
SQL& create index ind_tb_objid_multi on tb(object_id,1);
Index created.
SQL& select sum(object_id)
Execution Plan
----------------------------------------------------------
Plan hash value:
-------------------------------------------------------------------------------
------------
| Id&&| Operation& && && && & | Name& && && && && &| Rows&&| Bytes | Cost (%CPU
| Time& &&&|
-------------------------------------------------------------------------------
------------
|& &0 | SELECT STATEMENT& && &|& && && && && && &&&|& &&&1 |& & 13 |& & 31& &(0
| 00:00:01 |
|& &1 |&&SORT AGGREGATE& && & |& && && && && && &&&|& &&&1 |& & 13 |
|& && && & |
|& &2 |& &INDEX FAST FULL SCAN| IND_TB_OBJID_MULTI | 56761 |& &720K|& & 31& &(0
| 00:00:01 |
-------------------------------------------------------------------------------
------------
& &- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
& && && & 0&&recursive calls
& && && & 0&&db block gets
& && &&&139&&consistent gets
& && && & 0&&physical reads
& && && & 0&&redo size
& && &&&525&&bytes sent via SQL*Net to client
& && &&&492&&bytes received via SQL*Net from client
& && && & 2&&SQL*Net roundtrips to/from client
& && && & 0&&sorts (memory)
& && && & 0&&sorts (disk)
& && && & 1&&rows processed
SQL& drop index ind_tb_objid_
Index dropped.
SQL& select sum(object_id) from tb where object_
Execution Plan
----------------------------------------------------------
Plan hash value:
-------------------------------------------------------------------------------
| Id&&| Operation& && && && & | Name& && && &| Rows&&| Bytes | Cost (%CPU)| Tim
-------------------------------------------------------------------------------
|& &0 | SELECT STATEMENT& && &|& && && && &&&|& &&&1 |& & 13 |& & 27& &(4)| 00:
|& &1 |&&SORT AGGREGATE& && & |& && && && &&&|& &&&1 |& & 13 |& && && && &|
|*&&2 |& &INDEX FAST FULL SCAN| IND_TB_OBJID | 56761 |& &720K|& & 27& &(4)| 00:
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &2 - filter(&OBJECT_ID& IS NOT NULL)
& &- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
& && &&&142&&recursive calls
& && && & 0&&db block gets
& && &&&215&&consistent gets
& && &&&111&&physical reads
& && && & 0&&redo size
& && &&&525&&bytes sent via SQL*Net to client
& && &&&492&&bytes received via SQL*Net from client
& && && & 2&&SQL*Net roundtrips to/from client
& && && & 4&&sorts (memory)
& && && & 0&&sorts (disk)
& && && & 1&&rows processed
看出来在oracle 10g和oracle 11g情况下，优化器对于sum的处理是有变化的，由于全部为null的索引key是不存储在索引中的，在oracle 10g的优化器下，cbo认为这个会影响查询的结果，其实null是对sum没有影响的，所以这里优化器这里是有需要改善的地方，而在oracle 11g的优化器下，cbo知道null对于sum是没有影响的，那么object_id的所有值是否都存储在索引上没有影响，因为没有存储在索引上的object_id必然是null的，而这个null对于sum是没有影响的，那么这里也就是在oracle 11g下可以直接走全索引快速扫描的原因
求职 : 认证徽章论坛徽章:15
dotaddjj 发表于
我个人认为这两个sql没有可比性，首先第一个sql语句select SUM(origin_value) from ATable这个如果这个列上 ...
注意：复合索引在复合索引列值全部为NULL时，这行值的索引值是不会被存储的，也就是说不会使用存储。
所以如果是单列有NULL想使用索引，建议使用类型这样的复合索引（column,1）
论坛徽章:1
dotaddjj 发表于
C:\Users\Administrator&sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 2 ...
多谢两位的指教，昨天我也想到单列索引是不会被我的查询语句使用到的问题。再加了复合索引后(BID,origin_value)之后,查看执行计划已经使用上了此索引，但是我在清空缓存后测试的时候，第一遍运行该语句还是会慢，第一遍之后无论是否改变查询条件，速度都很快。这是否是因为第一遍时该表或该索引没有被读入缓存导致的？这可能也跟我的测试数据库配置很低有关系。
论坛徽章:1
ilsz 发表于
多谢两位的指教，昨天我也想到单列索引是不会被我的查询语句使用到的问题。再加了复合索引后(BID,origin_ ...
第一遍，全部从磁盘load到内存，第二遍在数据未被置换出去的情况下，内存计算。
个人认为，你这个B表是不是有些问题，尝试一下move到另一个tablespace，重建相关索引后，再次在B表上进行sum试一下。
认证徽章论坛徽章:40
dotaddjj 发表于
C:\Users\Administrator&sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 2 ...
10g和11g 在SUM上处理的差异，通过你的实验，有所了解和收获了。谢谢。
论坛徽章:185
SCOTT@test& @ver
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t as select rownum id , cast('test' as varchar2(10)) name from dual connect by level &=10;
create index i_t_id on t(id);
execute dbms_stats.gather_table_stats(user,'t',cascade=&true,method_opt=&'for all columns size 1 ',no_invalidate=&false);
SCOTT@test& select sum(id)
& &SUM(ID)
----------
& && &&&55
SCOTT@test& @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID&&g0kq5fuvf0px0, child number 0
-------------------------------------
select sum(id) from t
Plan hash value:
---------------------------------------------------------
| Id&&| Operation& && &&&| Name& &| E-Rows | Cost (%CPU)|
---------------------------------------------------------
|& &0 | SELECT STATEMENT |& && &&&|& && &&&|& &&&1 (100)|
|& &1 |&&SORT AGGREGATE&&|& && &&&|& && &1 |& && && && &|
|& &2 |& &INDEX FULL SCAN| I_T_ID |& &&&10 |& &&&1& &(0)|
---------------------------------------------------------
SCOTT@test& select sum(id)-0
----------
& && &&&55
SCOTT@test& @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID&&az79x12myw188, child number 0
-------------------------------------
select sum(id)-0 from t
Plan hash value:
---------------------------------------------------------
| Id&&| Operation& && && & | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|& &0 | SELECT STATEMENT& &|& && &|& && &&&|& &&&3 (100)|
|& &1 |&&SORT AGGREGATE& & |& && &|& && &1 |& && && && &|
|& &2 |& &TABLE ACCESS FULL| T& & |& &&&10 |& &&&3& &(0)|
---------------------------------------------------------
--可以发现一个小小的变化，如果sum加入了运算，执行计划有变为全部扫描。
alter table t modify(id&&not null);
SCOTT@test& select sum(id)-0
----------
& && &&&55
SCOTT@test& @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID&&az79x12myw188, child number 0
-------------------------------------
select sum(id)-0 from t
Plan hash value:
---------------------------------------------------------
| Id&&| Operation& && &&&| Name& &| E-Rows | Cost (%CPU)|
---------------------------------------------------------
|& &0 | SELECT STATEMENT |& && &&&|& && &&&|& &&&1 (100)|
|& &1 |&&SORT AGGREGATE&&|& && &&&|& && &1 |& && && && &|
|& &2 |& &INDEX FULL SCAN| I_T_ID |& &&&10 |& &&&1& &(0)|
---------------------------------------------------------
--可以发现这样可以使用索引。
论坛徽章:0
楼主，[性能调整] SUM等聚合函数的优化 ，请问这个问题是怎么解决的呢？
itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：10　广播电视节目制作经营许可证：编号（京）字第1149号以下试题来自：
单项选择题内部SQL聚合函数Sum的功能是（）
A.计算指定字段所有值的和
B.计算表中所有数字类型字段值的和
C.计算指定记录中所有数字字段值的和
D.计算一个记录中所有数字字段值的和
为您推荐的考试题库
您可能感兴趣的试卷
你可能感兴趣的试题
A.只能关闭当前的数据库
B.可以关闭指定的数据库
C.可以设置不提示直接关闭当前数据库
D.可以关闭除宏以外的其他数据库对象
A.模运算符
B.过程名称
C.定义变量
D.函数名称
A.可以设置不保存当前数据库即退出Access
B.只关闭当前数据库，不保存所有修改
C.自动保存所有修改后关闭当前数据库
D.若将&选项&参数设置为&退出&，则自动保存所有修改后退出Access6029人阅读
Oracle（6）
oracle中的聚合函数count、max、min、sum、avg以及NVL函数的用法
分组函数聚合函数对一组行中的某个列执行计算执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数。这类函数通常应用于报表统计中，以下展示Oracle常用的聚合函数的应用。
分组函数的介绍 作用于一组数据，并对一组数据返回一个值.
常见的分组函数有：
用来计算有效数据的数量
返回一个数字列或计算列的最小值
select gi.ID,
gi.game_instance_name,
gi.draw_no,
gi.draw_date,
count(*) ticketNumber,
---用来计算有效数据的数量
nvl(min(tt.total_bets),
0) totalEntry,--用来计算给定字段的最小值，如果为NULL就用0代替
nvl(min(tt.total_amount),
0) TotalTurnover----用来计算给定字段的最小值，如果为NULL就用0代替
from te_bg_ticket tt, BG_GAME_INSTANCE gi, game g
where tt.BG_GAME_INSTANCE_ID = gi.ID
and gi.game_id = g.game_id
and gi.status =
and tt.ticket_type =
and tt.is_count_in_pool =
and g.game_id =
'fddc4000d'
and g.game_type_id =
groupby gi.ID, gi.game_instance_name, gi.draw_no,
gi.draw_date
havingcount(*) &
orderby gi.draw_no
返回一个数字列或计算列的最大值
和最小值用法一样
返回一个数字列或计算列总和
select gi.ID,
gi.game_instance_name,
gi.draw_no,
gi.draw_date,
count(*) ticketNumber,
---用来计算有效数据的数量
nvl(sum(tt.total_bets),
0) totalEntry,--用来计算给定字段的总和，如果为NULL就用0代替
nvl(sum(tt.total_amount),
0) TotalTurnover----用来计算给定字段的总和，如果为NULL就用0代替
from te_bg_ticket tt, BG_GAME_INSTANCE gi, game g
where tt.BG_GAME_INSTANCE_ID = gi.ID
and gi.game_id = g.game_id
and gi.status =
and tt.ticket_type =
and tt.is_count_in_pool =
and g.game_id =
'fddc4000d'
and g.game_type_id =
groupby gi.ID, gi.game_instance_name, gi.draw_no,
gi.draw_date
havingcount(*) &
orderby gi.draw_no
返回一个数字列或计算列的平均值
用法和Max，min,Sum一样
NVL(expr1,expr2) 如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
----座右铭------
书山有路勤为径
学海无涯苦作舟
---联系方式-----
阅读：40785
阅读：47418oracle中的聚合函数count、max、min、sum、avg以及NVL函数的用法
oracle中的聚合函数count、max、min、sum、avg以及NVL函数的用法
分组函数聚合函数对一组行中的某个列执行计算执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数。这类函数通常应用于报表统计中，以下展示常用的聚合函数的应用。
分组函数的介绍 作用于一组数据，并对一组数据返回一个值.
常见的分组函数有：
用来计算有效数据的数量
返回一个数字列或计算列的最小值
select gi.ID,
gi.game_instance_name,
gi.draw_no,
gi.draw_date,
count(*) ticketNumber, ---用来计算有效数据的数量
nvl(min(tt.total_bets), 0) totalEntry,--用来计算给定字段的最小值，如果为NULL就用0代替
nvl(min(tt.total_amount), 0) TotalTurnover----用来计算给定字段的最小值，如果为NULL就用0代替
from te_bg_ticket tt, BG_GAME_INSTANCE gi, game g
where tt.BG_GAME_INSTANCE_ID = gi.ID
and gi.game_id = g.game_id
and gi.status = 1
and tt.ticket_type = 1
and tt.is_count_in_pool = 1
and g.game_id = 'fddc4000d'
and g.game_type_id = 6
groupby gi.ID, gi.game_instance_name, gi.draw_no, gi.draw_date
havingcount(*) & 0
orderby gi.draw_no
返回一个数字列或计算列的最大值
和最小值用法一样
返回一个数字列或计算列总和
select gi.ID,
gi.game_instance_name,
gi.draw_no,
gi.draw_date,
count(*) ticketNumber, ---用来计算有效数据的数量
nvl(sum(tt.total_bets), 0) totalEntry,--用来计算给定字段的总和，如果为NULL就用0代替
nvl(sum(tt.total_amount), 0) TotalTurnover----用来计算给定字段的总和，如果为NULL就用0代替
from te_bg_ticket tt, BG_GAME_INSTANCE gi, game g
where tt.BG_GAME_INSTANCE_ID = gi.ID
and gi.game_id = g.game_id
and gi.status = 1
and tt.ticket_type = 1
and tt.is_count_in_pool = 1
and g.game_id = 'fddc4000d'
and g.game_type_id = 6
groupby gi.ID, gi.game_instance_name, gi.draw_no, gi.draw_date
havingcount(*) & 0
orderby gi.draw_no
返回一个数字列或计算列的平均值
用法和Max，min,Sum一样
NVL(expr1,expr2) 如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。