Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Space Usage more than Data Space Usage

Re: Index Space Usage more than Data Space Usage

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 10 Sep 2002 21:57:33 +1000
Message-ID: <sZkf9.28910$g9.82440@newsfeeds.bigpond.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US