« 关于恩墨科技第二次培训暂时推迟的通知 | Blog首页 | OLTP Database Machine with Sun FlashFire Technology »
大表之困惑 - 数据建模的前期规划十分重要
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2009/09/big_table_design.html
这几天被一个数据库中的大表所困扰，这是一个插入非常频繁的数据表，该表目前大约有20亿记录，是一个分区表：
SQL> SELECT TABLE_NAME,NUM_ROWS,BLOCKS,AVG_SPACE,SAMPLE_SIZE,LAST_ANALYZED然而这个表上建有的都是全局索引，现在需要按照跟索引基本无关的条件进行删除，以下几个索引都无可利用：
2 FROM DBA_TABLES WHERE OWNER='SMS' AND TABLE_NAME='SSMG';
TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE SAMPLE_SIZE LAST_ANALYZE
------------------------------ ---------- ---------- ---------- ----------- ------------
SSMG 2000426788 60053382 0 101416650 12-SEP-09
SQL> select客户写了个Loop循环，通过另外一个表的关联去判断删除，另外一个表具有400万记录，初步计算了一下程序效率，跑完一次大约要200天。
2 INDEX_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,
3 NUM_ROWS,CLUSTERING_FACTOR,last_analyzed
4 from
5 dba_indexes t
6 where
7 table_name = 'SSMG'
8 and table_owner = 'SMS'
9 /
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR LAST_ANALYZE
------------------------ ---------- ----------- ------------- ---------- ----------------- ------------
IDX_SMS_DEST_MDN 3 12186077 19507227 2071388105 1816174507 12-SEP-09
IDX_SMS_SERVICE_ID 3 8184696 48 1990938187 113031962 12-SEP-09
UN_SMS 4 21717598 1973096899 1973096899 138625843 12-SEP-09
好了，现在的问题是，要么面对不可能，要么建个索引，加快处理，可是在20亿上建一个索引，即便是Online的方式，对应用的性能也会有几大的影响。
要么很复杂的去处理，要么很慢的去处理，当初如果多加个索引，该有多好啊！
-The End-
历史上的今天...
>> 2007-09-15文章:
>> 2006-09-15文章:
>> 2005-09-15文章:
------
这篇 【大表之困惑 - 数据建模的前期规划十分重要】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2009-09-15 08:45 | Comments (10) | Posted to FAQ | Edit |
留言 (10)
菜鸟占座观望ing...
呵呵
Posted by: sniperwd at September 15, 2009 11:38 AM
淡出如果多加个索引，该有多好啊！
应该是 当初如果多加个索引，该有多好啊！
PS：揪错有奖不？哈哈。。
Posted by: coastli at September 15, 2009 11:41 AM
我改，多谢指出:)
Posted by: eygle 
at September 15, 2009 12:03 PM
Physical data model.
Posted by: 木匠 at September 15, 2009 12:18 PM
能不能这样呢？
新建一个结构和这个表一样的分区表，建好合适的索引
然后把老表的数据库都插入到新表中去，insert（append+nologging）的速度应该远远大于delete的时间。
等insert完毕了，收集统计信息后再进行联合删除。
当然这样也有问题，如果业务一直运行，可能会有新的数据产生，新表和旧表之间的数据如何保持同步是个问题。不过旧表是分区表，既然是分区表，应该会有很多分区暂时是不使用的，可以考虑先把不使用的分区同步过去，最后同步使用的分区。
班门弄斧啊，请指教～～
Posted by: sunfire at September 15, 2009 2:06 PM
猜想要删除的行应该不多，不然也不会想着去建一个索引。hash join的性能应该高于索引访问，如果删除行数多的话。
把delete操作转换成CTAS或IIS操作的前提是机器足够强劲。不仅表数据要重建，所有的索引都要重建，当然统计信息可以采用import原来表的方式。（先建索引再insert append会引起很大的索引竞争的)
给个强劲机器的例子吧，呵呵
在exadata下，一个压缩表，650GB左右(相应于业务数据3T)，行数10亿条左右，建立一个全局索引，需要6分钟(CPU利用率平均25%,IO 2.5GB/s)...
Posted by: Kaya at September 17, 2009 12:49 AM
这种情况还是会发生的，关注解决方案。
如果我们这个项目中标，也可能在未来遇到类似情况。一天1000万条记录。。。。。
Posted by: zicai at September 17, 2009 5:25 PM
可以考虑先两个表hash join建立一个临时表存放rowid, pk columns
然后按照这个临时表去删除
Posted by: Eagle Fan at September 18, 2009 1:20 PM
kaya 是Oracle的吧，Exadata是在国内的应用么？
Posted by: eygle at September 18, 2009 5:55 PM
呵呵，是的
这个例子是在国内的一个benchmark
其实上面没说Exadata为什么建索引这么快的深层原因。一方面当然是硬件强劲了，另一方面是由于smart scan, column projection的运用，也说是说建索引时存贮结点只需要向数据库结点返回建索引所在的列的数据就行了。
在上面我的例子中，这个表有50多列，这个全局索引为5列，也就是与传统的存贮相比，网络带宽就省了90%，同时也大大的释放了DB结点的CPU处理能力。这是一个非常令DBA非常兴奋的速度……
Posted by: Kaya at September 19, 2009 1:28 AM