Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index Space Usage more than Data Space Usage
Hi Ora,
Yes it's quite possible for an index tablespace to take up more space than it's corresponding table tablespace (as indeed it's possible for an individual index to take up more space than it's parent table).
Note that an index has all the intermediate pages as "overhead" as well as storing the 10 byte rowid for each non null row. Also an index may not be too efficient in reusing deleted space for incrementally increasing values. Then of course there's always the issue of inappropriate PCTFREE and depending on what you mean by "used" space, there's inappropriate storage clause values that could cause grief. Last (and certainly not least) you may have many indexes on the same table, many of which could use the same column values. The sum of all these columns could easily exceed the sum of the columns on a table.
It is an indication that the overheads associated with your indexes may be a bit OTT and it might be worth some investigation.
Cheers
Richard
"Ora" <ora_geek_at_yahoo.com> wrote in message
news:fa5d6718.0209092108.7cacba74_at_posting.google.com...
> I administer an OLTP database.
>
> I have separate tablespaces on different Disk for Table data and index
> Objetcts .
>
> FOr one Module, the Space used in Index Tablespace is almost double
> than that of the corresponding Data Tablespace.
>
> Is it possible that Index objects occupy such large space vis-a-visa
> its Table Data ?
>
> OrA
Received on Tue Sep 10 2002 - 06:57:33 CDT