Space for constraints.
Hi, I've two questions . First is : - how can I check the amount of disk space used by one index or constraint ? Second one is involved with the first: - when I create constraint there is created index for the constraint ; the first character of the name of the index is space " " ; there is no way for me to calculate disk amount needed for the index because there is no information about the index in sysextents table. The question is how to calculate the necessary disk space ? Regards JG
Formula to calculate ~ bytes that an index will use:
(sum of column sizes + 9) * rows * 1.25
For example, if there is a table:
create table person
(
person_id serial,
fname char(25),
lname char(25),
ssn char(char9)
);
create index ix1 on person(person_id);
create index ix2 on person(ssn);
create index ix3 on (fname, lname);
then the index space needed for 1,000,000 rows would be approximately:
ix1: (4 +9) * 1000000 * 1.25 ----> 16,250,000 bytes or 16,250 Kb
ix2: (9+9) * 1000000 * 1.25 -----> 22,500,000 bytes or 22,500 Kb
ix3: (25+25+9) * 1000000 * 1.25 ---> 73,750,000 bytes or 73,750 Kb
If you have put your indexes in their own dbspace, you can see how much space
they have consume (sort of) using oncheck -pe.
John wrote:
> Hi,
> I've two questions .
> First is :
> - how can I check the amount of disk space used by one index or constraint ?
> Second one is involved with the first:
> - when I create constraint there is created index for the constraint ;
> the first character of the name of the index is space " " ; there is no
> way for me to calculate
> disk amount needed for the index because there is no information about
> the index in sysextents table.
> The question is how to calculate the necessary disk space ?
>
> Regards JG