I have to create an index on a specific tablespace, on oracle database. I would like to know if there is a way to tell how much space of the tablespace will it take the creation of the index, so I can assure that my tablespace is capable of handling such index.
我必须在oracle数据库上的特定表空间上创建索引。我想知道是否有一种方法可以告诉表空间需要多少空间来创建索引，所以我可以保证我的表空间能够处理这样的索引。
2 个解决方案
#1
1
The dbms_space package has a procedure create_index_cost that will tell you the number of bytes that would be allocated to the index segment (which is presumably what you care about if you're trying to determine whether it will fit in your tablespace) and the number of bytes of that allocation that would actually be used. This procedure relies on the statistics that have been gathered on the underlying table, however, so if those statistics are inaccurate, the procedure's estimates will also be inaccurate.
dbms_space包有一个过程create_index_cost，它将告诉你将分配给索引段的字节数（如果你试图确定它是否适合你的表空间，可能是你关心的）和数量实际使用的那个分配的字节数。但是，此过程依赖于在基础表上收集的统计信息，因此，如果这些统计信息不准确，则过程的估计也将是不准确的。
#2
0
A quick web search yields this (removed the actual calcs for that user's particular case):
快速网络搜索产生了这个（删除了该用户特定情况的实际计算）：
A rough estimate of the space the index will need can be made by adding the expected actual length of each column plus 6 for the rowid plus 2 for the header X number of table rows that will have an entry ...
粗略估计索引所需的空间可以通过添加每列的预期实际长度加上rowid的6加上2将具有条目的表行的标题X数...
we will use 20% for block overhead ...
我们将使用20％的块开销...
The actual allocation will vary depending on your tablespace extent allocation method.
实际分配将根据您的表空间范围分配方法而有所不同。
From here
从这里
Or a more detailed way of estimating here.
或者更详细的估算方法。
#1
1
The dbms_space package has a procedure create_index_cost that will tell you the number of bytes that would be allocated to the index segment (which is presumably what you care about if you're trying to determine whether it will fit in your tablespace) and the number of bytes of that allocation that would actually be used. This procedure relies on the statistics that have been gathered on the underlying table, however, so if those statistics are inaccurate, the procedure's estimates will also be inaccurate.
dbms_space包有一个过程create_index_cost，它将告诉你将分配给索引段的字节数（如果你试图确定它是否适合你的表空间，可能是你关心的）和数量实际使用的那个分配的字节数。但是，此过程依赖于在基础表上收集的统计信息，因此，如果这些统计信息不准确，则过程的估计也将是不准确的。
#2
0
A quick web search yields this (removed the actual calcs for that user's particular case):
快速网络搜索产生了这个（删除了该用户特定情况的实际计算）：
A rough estimate of the space the index will need can be made by adding the expected actual length of each column plus 6 for the rowid plus 2 for the header X number of table rows that will have an entry ...
粗略估计索引所需的空间可以通过添加每列的预期实际长度加上rowid的6加上2将具有条目的表行的标题X数...
we will use 20% for block overhead ...
我们将使用20％的块开销...
The actual allocation will vary depending on your tablespace extent allocation method.
实际分配将根据您的表空间范围分配方法而有所不同。
From here
从这里
Or a more detailed way of estimating here.
或者更详细的估算方法。