Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Calculate storage requirements for an index

kenny chia
Regular Advisor

Calculate storage requirements for an index

Hi
I wish to create an index for a table and place the index in a new tablespace. Lets say I want to index only one column

How do I determine the SIZE requirements for the new tablespace?

CREATE TABLESPACE "TABLE" DATAFILE '/users/data/Index_tbs7.dbf' SIZE ????M

For the index itself, what should the initial size be?

Note that the table is maintained automatically and will never grow in size
All Your Bases Are Belong To Us!
5 REPLIES
Fred Ruffet
Honored Contributor

Re: Calculate storage requirements for an index

What version of Oracle do you use ?
You say it will never grow... what about updates ?
What is cardinality for index columns ? How many indexed columns ?

There are many questions related to index estimation size. None will be as efficient as a test : Create a copy of the table and create index. Of course it's not usable if your table is too big. In that xase, some tool may help you. For example, Toad estimate index size before creation and some other may do the same.

Regards,

Fred
--

"Reality is just a point of view." (P. K. D.)
Yogeeraj_1
Honored Contributor

Re: Calculate storage requirements for an index

hi,

is it true that you are trying to create a tablespace for ONE index???

The normal trend is to create Locally managed tablespaces with uniform extents (depending whether it is small, medium, large or xlarge) and stop worrying about sizing of tables as such!

if you need any further info, please let us know.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Fred Ruffet
Honored Contributor

Re: Calculate storage requirements for an index

Yogeeraj is right. The only advantage I can see in using a tablespace for an index is to locate this index on particular disks for performances purpose. And this may be useful only for a very big and used index.

Regards,

Fred
--

"Reality is just a point of view." (P. K. D.)
kenny chia
Regular Advisor

Re: Calculate storage requirements for an index

Hi all
I am using Oracle 7.
All Your Bases Are Belong To Us!
Eric Antunes
Honored Contributor

Re: Calculate storage requirements for an index

Hi,

If the table is not to grow, it's simple: put the same size of the table for the index. But, ever if the table grows, size of the index will never be a problem since you can rebuild it with a new size:

alter index . rebuild
storage (initial M next M);

This is why I recomend you to use more 50% of the table size for the tablespace/datafile!

You can determine the size of the table with this SQL:

select segment_name, sum(bytes) ebytes
from dba_extents
where segment_name =
group by segment_name;

Best Regards,

Eric Antunes
Each and every day is a good day to learn.