如何估计 Oracle 索引的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/827123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I estimate the size of an Oracle index?
提问by Jared
I'm considering adding an index to an Oracle table, but I'd like to first estimate the size of the index after it has been built (I don't need a precise size - just an estimate.)
我正在考虑向 Oracle 表添加索引，但我想先估计索引构建后的大小（我不需要精确的大小 - 只是一个估计值。）
Supposing I have access to all of the meta-data about the table (number of rows, columns, column data types, etc) that I can execute any arbitrary Oracle SQL query to get additional data about the current state of the table, and I know what I would want the index definition to be, how can I estimate this size?
假设我可以访问有关表的所有元数据（行数、列数、列数据类型等），我可以执行任意 Oracle SQL 查询来获取有关表当前状态的其他数据，并且我知道我想要索引定义是什么，我如何估计这个大小？
回答by cletus
You can use these Oracle Capacity planning and Sizing Spreadsheets.
您可以使用这些Oracle 容量规划和规模调整电子表格。
For something not quite as full-blown, if you just want back of the envelope type 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.
计算构成索引键的每一列的平均大小，并将列加一个 rowid 相加并为索引行标题添加 2 个字节以获得平均行大小。现在只需为索引的 pctfree 值添加一点即可得出开销因子，对于 10 的 pctfree 可能是 1.125。
索引表行数 X 平均行 len X 1.125
注意 - 如果索引包含可为空的列，那么每个表行可能不会出现在索引中。在 90% 的列为空的单列索引上，只有 10% 会进入索引。
将估计与表空间范围分配方法进行比较，并在必要时调整最终答案。
此外，随着索引变大，更大的开销因子可能会更好，因为索引的数据越多，支持索引结构所需的分支块就越多，并且计算实际上只是叶块的数字。
回答by Chris Saxon
You can estimate the size of an index by running an explain planon the create index statement:
您可以通过explain plan在 create index 语句上运行 来估计索引的大小：
create table t as
select rownum r
from dual
connect by level <= 1000000;
explain plan for
create index i on t (r);
select *
from table(dbms_xplan.display(null, null, 'BASIC +NOTE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1744693673
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | CREATE INDEX STATEMENT | |
| 1 | INDEX BUILD NON UNIQUE| I |
| 2 | SORT CREATE INDEX | |
| 3 | TABLE ACCESS FULL | T |
---------------------------------------
Note
-----
- estimated index size: 4194K bytes
Look at the "Note" section at the bottom: estimated index size: 4194K bytes
查看底部的“注意”部分：估计索引大小：4194K 字节
回答by GWu
Starting from version 10gR2 you can use DBMS_SPACE.CREATE_INDEX_COST
从版本 10gR2 开始，您可以使用 DBMS_SPACE.CREATE_INDEX_COST
DBMS_SPACE.CREATE_INDEX_COST (
ddl IN VARCHAR2,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER,
plan_table IN VARCHAR2 DEFAULT NULL);
From the docs: "This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index."
来自文档：“此过程确定在现有表上创建索引的成本。输入是将用于创建索引的 DDL 语句。该过程将输出创建索引所需的存储。”
See https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68101
见https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68101
Example(also at sqlfiddle):
示例（也在sqlfiddle）：
DECLARE
ub NUMBER;
ab NUMBER;
BEGIN
DBMS_SPACE.CREATE_INDEX_COST (
ddl => 'CREATE INDEX x_1 ON t1 (a,b,c) TABLESPACE users',
used_bytes => ub,
alloc_bytes => ab
);
DBMS_OUTPUT.PUT_LINE('Used MBytes: ' || ROUND(ub/1024/1024));
DBMS_OUTPUT.PUT_LINE('Alloc MBytes: ' || ROUND(ab/1024/1024));
END;
/
Output:
输出：
Used MBytes: 1
Alloc MBytes: 2
回答by santosh tiwary
The below SQL query can be used to know the estimated index sizeoccupied by a table in oracle.
下面的 SQL 查询可用于了解oracle 中某个表所占用的估计索引大小。
SELECT (row_size_in_bytes * cnt_of_rows)/1000/1000/1000 index_size_in_GB
FROM
(
SELECT table_name ,
(sum (column_length) / 1048576) * 1000000 row_size_in_bytes
FROM user_ind_columns
WHERE table_name=UPPER('&Enter_Table_Name')
GROUP BY table_name
) A,
(SELECT count(1) cnt_of_rows FROM &Enter_Table_Name );
