Geek Answers Handbook

How to estimate the size of the Oracle index?

I am considering adding an index to an Oracle table, but first I want to estimate the size of the index after creating it (I don't need the exact size - just an estimate.)

Suppose that I have access to all metadata about a table (the 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 like the index definition to be, how can I evaluate this size?

+7
sql oracle
source share
3 answers

You can use these Oracle Performance and Capacity Planning .

For something, it’s not completely complete if you just want to return to the type of envelope approximate 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 a little for the pctfree value for the index to get an overhead factor of perhaps 1.125 for pctfree 10.

the number of indexed rows of the table X avg row len X 1.125

Note. If the index contains null columns, then each row of the table may not appear in the index. On one column index, where 90% of the columns are zero, only 10% will go into the index.

Compare the score with the table space distribution method and adjust the final answer if necessary.

It may also be better, as the index increases, the more data is indexed, the more branches the blocks are needed to support the index structure and the calculation is really just numbers for leaf blocks.

+10
source share

You can estimate the size of the index by running explain plan in the index creation instructions:

 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

Take a look at the Note section below: Estimated Index Size: 4194 Kbytes

+2
source share

Starting with version 10gR2 you can use 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);

In the docs: "This procedure determines the cost of creating an index in an existing table. Input is the DDL statement that will be used to create the index. The procedure will display the storage needed to create the index."

See https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68101

Example (also in 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
0
source share

More articles:


All Articles