(原创)在数据仓库中创建索引

三里屯摇滚

(原创)在数据仓库中创建索引

创建索引很简单，但如果给一个超过1TB表创建索引呢？ 你的临时空间够用吗？ There is a helpful tip share to us: How to estimate the exact temp space usage during the index creation on a huge table ? 这里有一个有用的技巧share给大家：如果精确计算创建索引所需的最大临时空间。 算法是： The algorithm is : ( ColumnA length + ColumnB length + .. + Rowid length ) * row_number For example : create index DW.ixd_1 on DW.tableA (ColumnA ,ColumnB); 1) 在统计信息中检查字段的平均长度（in case your stats is not too stale）希望你的统计不要太陈旧。 SELECT COLUMN_NAME, DATA_TYPE, AVG_COL_LEN FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '' AND OWNER = '' AND COLUMN_NAME IN ('ColumnA ', 'ColumnB') COLUMN_NAME, DATA_TYPE, AVG_COL_LEN ____________ _________ ___________ ColumnA vachar2 10 ColumnB number 7 2) 检查row_number SELECT num_rows FROM DBA_TABLES A WHERE TABLE_NAME=('TABLEA'); NUM_ROWS ---------- 100,000,000 3) 检查当前 database version下的rowid的长度 SELECT lengthb(ROWID) FROM dual ; --10g ______________ 18 4) 计算 .. (10 +7 +1 Byte *1,000,000 = 3337.86Mb 5) If you have the stale table statistics, you also can estimate this by table defining, but it will be the MAX size. A couple weeks ago, I created an index on a FACT table with 3，804，025，560 records, finally it consumed 190Gb tempspace (v$sort_usage). It did give me a surprise ;-O , I had to borrow space from other where .. So when you will create an index in a dataware house, I hope it won't give you surprise on temp space usage. 几周以前，我在一个拥有38亿记录的表创建了一个索引，通过观察temp space的usage，它最大消耗了190GB的tempspace。 为了成功创建它，我不得不从其它地方借了些空间来扩张我的temp space。 希望你以后，在创建怪物级索引的时候，遇到temp space error，don‘t be too surprise

小忻黑夜

谢谢分享