Home » RDBMS Server » Server Administration » Index segment size estimation
Index segment size estimation [message #224326] Tue, 13 March 2007 21:09 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi ,
I have a table segment of size 20GB.Now i want to create an index on two column on that table.Any equation to find estimate size of index that will create.
(purpose is to adjust the tablespace size)

Report message to a moderator

Re: Index segment size estimation [message #224328 is a reply to message #224326] Tue, 13 March 2007 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
(size col1 + size col2)*num_rows

Report message to a moderator

Re: Index segment size estimation [message #224332 is a reply to message #224326] Tue, 13 March 2007 22:03 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thank you .
i have two column.
Column1 char(2)
culumn2 Date

and total number of records is 100000 .

(2+7)*100000 is the correct size of index ?

Report message to a moderator

Re: Index segment size estimation [message #224334 is a reply to message #224326] Tue, 13 March 2007 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>(2+7)*100000 is the correct size of index ?
Will give a decent estimate; especially if both are NOT NULL columns.

Report message to a moderator

Re: Index segment size estimation [message #224404 is a reply to message #224334] Wed, 14 March 2007 03:19 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I tried this in production.my estimation was 900MB .But index took 2760MB Mad

Report message to a moderator

Re: Index segment size estimation [message #224525 is a reply to message #224326] Wed, 14 March 2007 09:40 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
As with many things Oracle, it depends.
It depends upon type of tablespace (dictionary or locally managed).
With LMT %FREE can have a noticable impact.
This is why one should ALWAYS have a test/development system so you can actually verify what happens rather than depend upon answers from unknown folks who may not always know YOUR details.

Report message to a moderator

Previous Topic: Problem with ASM Disk on Linux
Next Topic: problem with remote administration,help
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 03:48:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.