Calculate storage requirements for an index

[kenny chia]

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

[Fred Ruffet]

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

[Yogeeraj_1]

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

[Fred Ruffet]

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

[kenny chia]

Hi all
I am using Oracle 7.

[Eric Antunes]

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