|
我正在考虑在Oracle表中添加一个索引,但是我想首先估计索引的大小(我不需要精确的大小 – 只是一个估计.)
假设我可以访问有关表的所有元数据(行数,列数,列数据类型等),我可以执行任意Oracle SQL查询以获取有关表的当前状态的其他数据,而我知道我希望索引定义是什么,我该如何估计这个大小？
解决方法
你可以使用这些
Oracle Capacity planning and Sizing Spreadsheets.
对于不那么全面的东西,如果你只想要信封类型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.125
Note – 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.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
