600万条数据的select count(*)问题。

xyzhh 2006-07-04 11:09:11
我的一张表里有6百多万条数据(没有作分区),执行一个select count(*) from tab;
1。需要一分多钟的时间,这是否正常呢?

后来,我在这个表上和他的索引上作了一次分析,然后执行select count(*) from tab;发现需要20多分钟时间甚至更长。(优化器是choose)
2。为什么越分析越慢呢

经过跟踪发现,没有作分析前,需要作全表扫描,此时能看到操作系统的磁盘i/o很大;分析后,在其中的一个索引上作快速扫描,处理的block数要比全表扫描少了一大半(看来优化器的选择是没错的),但是,此时到操作系统的磁盘i/o很小,执行速度非常慢,我把此索引rebuild也没有用。(表里是日志,仅仅每天批量插入一次,很少做查询。
3。 为什么处理的块越少磁盘i/o越低(处理速度大幅下降)?

4。对于这种海量数据库的表,大家在使用和维护上有什么好的建议?


5。谢谢!
...全文
3870 44 打赏 收藏 转发到动态 举报
写回复
用AI写文章
44 条回复
切换为时间正序
请发表友善的回复…
发表回复
longlongago8 2006-07-19
  • 打赏
  • 举报
回复
learning .....
xiaosheng2008 2006-07-18
  • 打赏
  • 举报
回复
当查询大量数据中的少部分的时候用索引才有意义
coun(*)还用索引不符合使用索引的本意
LinZhongBao 2006-07-18
  • 打赏
  • 举报
回复
呵呵,强贴,
狂顶!!!!
  • 打赏
  • 举报
回复
select count(-1) from tab;

试试??
teacher1998 2006-07-17
  • 打赏
  • 举报
回复
第一次来oracle这碰到强贴,我顶
xiaoxiao1984 2006-07-11
  • 打赏
  • 举报
回复
to xyzhh(逍遥者寒号) :
1. set autot on; 和 set timing on;后统计出的时间 = 执行语句需要的时间 + 花在 autotrace上的时间;所以这个时间不能直接进行比较;如果需要比较时间,应该是 set autot off 之后执行的时间进行比较;
2. 在执行sql语句之前,先 alter system flush shared_pool;清空共享池,保证语句经过解析和执行,如果在共享池中已经存在该语句的话,可能只是经过软解析和执行,所消耗的时间并不准确
即: 执行第一条语句之前 alter system flush shared_pool
执行第一条语句之后,再次alter system flush shared_pool
再执行第二条语句
xyzhh 2006-07-11
  • 打赏
  • 举报
回复
另说一句,我的数据是在raw上,排除文件系统缓冲的可能性
xyzhh 2006-07-11
  • 打赏
  • 举报
回复
看一下我的set autotrace on set timing on
第一次是分析过的表,也就是基于成本的优化;第二次是将分析删除后的表,是基于规则的优化(我的版本是9i)
SQL> select count(*) from dvd_list;

COUNT(*)
----------
7444349

Elapsed: 00:05:13.52

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1309 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'INDEX_TIME' (NON-UNIQUE) (Cos
t=1309 Card=7142872)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13925 consistent gets
9792 physical reads
0 redo size
382 bytes sent via SQL*Net to client
503 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 count(*) from dvd_list;

COUNT(*)
----------
7444349

Elapsed: 00:04:10.55

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DVD_LIST'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69267 consistent gets
48916 physical reads
0 redo size
382 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

大家可以看到,无论是物理读还是逻辑读后者都比前者大得多,但是,时间却少了,这是为什么?
xyzhh 2006-07-11
  • 打赏
  • 举报
回复
看了BoningSword(【浩少】) 的话我明白了:对于这样的select count(*) 是不能根治的。。
至于统计表。。。我们的查询组合太多了,基本上到原子级的了,做不出来。。。
sanoul 2006-07-11
  • 打赏
  • 举报
回复
BoningSword(【浩少】) 说的非实时日志有点意思,以后我也考虑做做
charlenelin 2006-07-11
  • 打赏
  • 举报
回复
您好,我们是“2006中国杰出数据库工程师评选”活动组委会。
您的帖子已经被我们转载到本次评选官方网站的“专家在线答疑”区。
http://www.bestdba.cn/match_discussion.aspx

在那里,进入本次评选复选的90位数据库工程师将与您展开积极的互动。
一方面,他们会为您的问题提供满意的答案,
另一方面,也邀请您为他们投上宝贵的选票。

2006-7-8 ~ 2006-7-25日,每天我们将从当天参与"有奖投票"的网友
中抽取3名幸运者,赠送由IBM提供的精美礼品一份!

此外,您还可以在“专家在线答疑”区提出新的问题并参与讨论。

您的帖子位于:
http://www.bestdba.cn/match_discussion3.aspx?pointid=129&pointid2=1&agains=1&pointid3=5

非常感谢您对本次活动的支持!
xyzhh 2006-07-11
  • 打赏
  • 举报
回复
1。在没有set autotrace on之前也是基于规则的快。
2。 consistent gets应该和shared_pool无关吧
caryyang 2006-07-08
  • 打赏
  • 举报
回复
不过没有使用BoningSword(【浩少】) 的方法,而是在表中仅仅保留了(table_id, rows_count, last_update_date)
caryyang 2006-07-08
  • 打赏
  • 举报
回复
添加统计表,以前做电力系统的时候没添都有上百万的数据插入表。为了统计记录数目就对关键表设置了单独的统计表,记录更新时使用触发器更新统计表
BoningSword 2006-07-07
  • 打赏
  • 举报
回复
我看了上面有些朋友的发言,有些观点不能苟同。
1、关于位图索引,一般来说,位图索引对需要做修改操作的表来说,性能损失是非常大的,除非你很清楚位图索引的作用,否则最好不好使用。详细的说明,我打字太累了,就不多说了。这方面的资料很多。
2、创建xxx索引对select count(*) 的影响。理论上来说,是没有任何影响的,你尝试了有性能提高,很有可能是其他原因导致的,不一定就是因为创建了索引。
3、很久以前,itput上就有过争论:select count(*) 和select count(id) 性能孰优孰劣。但oracle9i的优化器的实践证明,两者没有任何区别的。
4、另外,大家在做速度测试时,别忽略了缓存的影响。就是说第一次执行select count(*)是有可能是10秒,第二次执行时是1秒,如果中间你做了什么操作,你就想当然的认为是你的这个操作引起性能提高,实际很有可能只是缓存在起作用。
BoningSword 2006-07-07
  • 打赏
  • 举报
回复
更正:
>>你可以当时用一个job
你可以定时用一个job
BoningSword 2006-07-07
  • 打赏
  • 举报
回复
我来说两句吧,不管怎么优化,select count(*) from .. 都是要做全表扫描,就是说不管用什么方法都不能从数量级上提高速度。都是治标不治本的方法。

但是,我们不妨换个思路。针对你的需求,你可以再新建一个统计表
count_aaa(id,count_date,count_num)
用来离线统计你的日志表,如果你的日志表更新比较频繁,你可以当时用一个job,统计一条数据插到表count_aaa,如果只是每天固定时间更新日志表的话,就在更新完日志表以后,插一条统计数据到count_aaa,这样你以后读取统计数的话,就不用执行select count(*) from 了,而是直接面向count_aaa表。

不过注意,后者是根据你的目前情况最好的解决方案,前者非实时更新,需要看客户是否可以接受。

我面对的电信行业的,表中数据大多是千万级的,用的小型机性能也非常好,但还是从不敢用select count(*) 这样的语句,都是用非实时方式的。

希望对你有帮助。
sanoul 2006-07-07
  • 打赏
  • 举报
回复
说错了,是0.0501秒
sanoul 2006-07-07
  • 打赏
  • 举报
回复
索引不要乱建~
在update, insert, delete非常剧烈的表上,尽量少建index,因为每次更新,oracle都需为index付出代价,虽然index在select时也发挥了很大的作用.

其次,充分利用默认建的主键index.

---------------------------------------------
DML语句对index消耗在于它的影响行数,定期做index rebuild可以很好地修正某些损失,如磁盘碎片.

对一个表进行独力的select,索引还是可以起非常重要的作用,问题无非是select count(*)重要还是select *重要,如果是*建议楼主还是多用primary key,如果是count(*),那还是用块扫描好了.


另外我有一张300万条数据的表,上面有各种INDEX,分析是用某个bitmap index做扫描的,COUNT(*)的速度是0.501秒.
xyzhh 2006-07-07
  • 打赏
  • 举报
回复
优化模式是choose,我说的就是分析与未分析(两种优化器)的差别。(数据库是9i)
加载更多回复(24)

3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧