oracle创建表模型,大表之困惑 - 数据建模的前期规划十分重要

大表之困惑 - 数据建模的前期规划十分重要

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

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客户写了个Loop循环，通过另外一个表的关联去判断删除，另外一个表具有400万记录，初步计算了一下程序效率，跑完一次大约要200天。

好了，现在的问题是，要么面对不可能，要么建个索引，加快处理，可是在20亿上建一个索引，即便是Online的方式，对应用的性能也会有几大的影响。

要么很复杂的去处理，要么很慢的去处理，当初如果多加个索引，该有多好啊！

-The End-

By eygle on 2009-09-15 08:45 |

Comments (13) |

FAQ | 2398 |

13 Comments

淡出如果多加个索引，该有多好啊！

应该是 当初如果多加个索引，该有多好啊！

PS：揪错有奖不？哈哈。。

能不能这样呢？

新建一个结构和这个表一样的分区表，建好合适的索引

然后把老表的数据库都插入到新表中去，insert(append+nologging)的速度应该远远大于delete的时间。

等insert完毕了，收集统计信息后再进行联合删除。

当然这样也有问题，如果业务一直运行，可能会有新的数据产生，新表和旧表之间的数据如何保持同步是个问题。不过旧表是分区表，既然是分区表，应该会有很多分区暂时是不使用的，可以考虑先把不使用的分区同步过去，最后同步使用的分区。

班门弄斧啊，请指教～～

猜想要删除的行应该不多，不然也不会想着去建一个索引。hash join的性能应该高于索引访问，如果删除行数多的话。

把delete操作转换成CTAS或IIS操作的前提是机器足够强劲。不仅表数据要重建，所有的索引都要重建，当然统计信息可以采用import原来表的方式。(先建索引再insert append会引起很大的索引竞争的)

给个强劲机器的例子吧，呵呵

在exadata下，一个压缩表，650GB左右(相应于业务数据3T)，行数10亿条左右，建立一个全局索引，需要6分钟(CPU利用率平均25%,IO 2.5GB/s)...

这种情况还是会发生的，关注解决方案。

如果我们这个项目中标，也可能在未来遇到类似情况。一天1000万条记录。。。。。

可以考虑先两个表hash join建立一个临时表存放rowid, pk columns

然后按照这个临时表去删除

kaya 是Oracle的吧，Exadata是在国内的应用么？

呵呵，是的

这个例子是在国内的一个benchmark

其实上面没说Exadata为什么建索引这么快的深层原因。一方面当然是硬件强劲了，另一方面是由于smart scan, column projection的运用，也说是说建索引时存贮结点只需要向数据库结点返回建索引所在的列的数据就行了。

在上面我的例子中，这个表有50多列，这个全局索引为5列，也就是与传统的存贮相比，网络带宽就省了90%，同时也大大的释放了DB结点的CPU处理能力。这是一个非常令DBA非常兴奋的速度……

不是索引的问题 是没有对大表没有设置好归档方案

有时候规划往往跟不上用户的需求变化。我有的用户就是说不要条件进行查询数据。。我当时汗！

客户的理解和技术实现之间有很大差距，需要我们来扭转的。