假设我可以访问有关表的所有元数据(行数,列数,列数据类型等),我可以执行任意Oracle SQL查询以获取有关表的当前状态的其他数据,而我知道我希望索引定义是什么,我该如何估计这个大小？
解决方法
对于不那么全面的东西,如果你只想要信封类型rough estimates for the index：
Calculate the average size of each of
the columns that make up the index key
and sum the columns plus one rowid and
add 2 bytes for the index row header
to get the average row size. Now add
just a little to the pctfree value for
the index to come up with an overhead
factor,maybe 1.125 for pctfree of 10.number of indexed table rows X avg row
len X 1.125Note – if the index contains nullable
columns then every table row may not
appear in the index. On a single
column index where 90% of the columns
are null only 10% would go into the
index.Compare estimate to tablespace extent
allocation method and adjust final
answer if necessary.Also a larger overhead factor may be better as the index gets bigger since the more data indexed the more branch blocks necessary to support the index structure and the calculation really just figures for leaf blocks.
