| Is it possible? [message #60301] |
Fri, 30 January 2004 14:31  |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
Hi,
Is it possible that the data in a index tablespace could be larger than the data in the corresponding data tablespace ?
We have two tablespaces. Tablespace A (Data) is used by 14Gigs of data, whereas tablespace B (Index) is used by about 20 gigs of index data. It is just weird that 14 gigs of data needs 20 gigs of index.
-tony
|
|
|
|
| Re: Is it possible? [message #60312 is a reply to message #60301] |
Sun, 01 February 2004 02:29  |
Bola
Messages: 3
Registered: February 2004
|
Junior Member |
|
|
|
Yes, It is possible. Remember that you may have multiple indices for a single table. Additionally, a school of thought says that you should allocate tablespace filesizes in a ratio of DATA:INDEX as 2:3 (ish)
|
|
|
|
| Re: Is it possible? [message #60321 is a reply to message #60301] |
Mon, 02 February 2004 05:06 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Sure,its possible for a few reasons.
i) Index fragments(holes) more than tables. Not all the deleted space may get reused. [[Deleted space does get reused in Indexes usually]]
ii) Index needs to store ROWIDs in addition to the key values
iii) If you are talking about tablespace size,then the major contributor is multiple indexes per table,many of them concatenated and repeated columns .
Index being bigger than table or Index tablespace being bigger than 'table' tablespace is not a general rule though. Usually ,its the other way around.
-Thiru
|
|
|
|
| Re: Is it possible? [message #60323 is a reply to message #60312] |
Mon, 02 February 2004 05:11 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Quote "Additionally, a school of thought says that you should allocate tablespace filesizes in a ratio of DATA:INDEX as 2:3 (ish)"
Not necessarily. It all depends on the Table/Index number/size/type etc.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
