估计创建索引需要的排序空间数量

表 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的错误可就浪费时间了。

转载于:https://www.cnblogs.com/rootq/archive/2009/02/02/1382531.html