CODEWITHSUNDEEP
databaseoracle-databaseindexingoracle10gtablespace
Cristian
Cristian

Reputation: 309

How to know how much space on tablespace will the creation of an index take

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.

Upvotes: 0

Views: 303

Answers (2)

Justin Cave
Justin Cave

Reputation: 231711

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.

Upvotes: 1

Gerrat
Gerrat

Reputation: 29700

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 ...

we will use 20% for block overhead ...

The actual allocation will vary depending on your tablespace extent allocation method.

From here

Or a more detailed way of estimating here.

Upvotes: 0

Related Questions