估计创建索引需要的排序空间数量
表 T 3765888 rows 准备在2个字段上建立索引(object_type,object_name) ，分析过后从user_tab_columns中获得平均列长度的统计信息
avg_row_len object_name 23 avg_row_len object_type 8
9i得utlxpls虽然能够估计查询使用的临时表空间，但是仍然不能估计创建索引所需要的临时表空间数量，如下
SQL> explain plan for create index tidx on t(object_type,object_name) ;
Explained.
SQL> @C:oracleora92rdbmsadminutlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 3765K| 111M| 4975 |
| 1 | INDEX BUILD NON UNIQUE| TIDX | | | |
| 2 | SORT CREATE INDEX | | 3765K| 111M| |
| 3 | TABLE ACCESS FULL | T | 3765K| 111M| 4975 |
----------------------------------------------------------------------
111M就是oracle根据avg_row_len 和表的num_rows计算而得；如果使用select排序操作，测试得到的结果显然过大。
SQL> explain plan for select object_type,object_name from t order by object_type
,object_name;
Explained.
SQL> @c:oracleora92rdbmsadminutlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3765K| 111M| | 26877 |
| 1 | SORT ORDER BY | | 3765K| 111M| 288M| 26877 |
| 2 | TABLE ACCESS FULL | T | 3765K| 111M| | 4975 |
----------------------------------------------------------------------------
Note: cpu costing is off
SQL> select 288*1024/8 from dual;
288*1024/8
----------
36864 个block,不清楚cbo是使用多少sort_area_size估计的288M,或许是multiple merge sort pass
实际测试 create index on t(object_type,object_name)使用了24192个临时表空间blocks （v$sort_usage）
索引创建后，index segment使用了 23424 blocks（受pctfree影响）.且创建索引是在 one merge pass sort 情况下 (oracle9i可以从v$sesstat的workarea%统计数据察看).
按照每个索引项长度44计算 1+6+1+8+1+23+3= 44 20226 block。
按照每个索引项长度48计算 2+6+2+8+2+23+4= 48 22065 block。
感觉还比较接近。保证20%或者30%的自由临时空间就可以了
理论支持
创建大索引的时候，保证sort_area_size足够大，使排序操作只需要one merge pass sort。我一般都设置几百M到几G.不过设置太大，可能会遇见pga sort-heap allocate的错误，遇见过，还不知道为何，可能是32bit的限制。
one merge pass sort情况下，需要的临时表空间的数量就是参与排序的数据源大小。因此创建索引的时候，要参与排序的数据源包括所有要索引的列和rowid,还有每个列的长度，还有index entry header. 但我还不肯定column length是占1bytes还是2bytes；index entry header是2，3 or 4 bytes？
先计算每个index entry的总长度
每个indexed column的平均长度+2*索引烈的数目+rowid长度（6 for non-partition 10 for partition）+rowid column length(maybe 1 or 2)+index header length( 3 or 4 bytes which I'm not sure)
折合为近似4的整数。 将计算的平均索引项长度* num_rows (*_tables)，就可以估计出需要使用的临时表空间（它并不受索引pctfree参数影响）。
内存排序的时候，每个block肯能会有60字节的浪费(here are some bytes waste in block during sort,maybe 60 bytes per block).但不会对估计的结果有大的影响。 最好保证实际可用的临时表空间大小比我们估计的大20%或30%，不然出现unable to extend sort segment in TEMP的错误可就浪费时间了。
