Index rebuild grows database size [message #481008] |
Fri, 29 October 2010 02:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
preet_kumar
Messages: 204 Registered: March 2007
|
Senior Member |
|
|
Our database size is 100GB and i removed few records from a table and rebuild the index.The size of Index reduced considerably after Index rebuild but now i see our database size increased to 115GB.I know online rebuild creates second index which is also removed after the build is finished then why the increase in database size?Is there a way so it shows up more space ?
select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
8GB
ALTER INDEX abc_index_1 REBUILD ONLINE ;
select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1';
2GB
|
|
|
|
Re: Index rebuild grows database size [message #481015 is a reply to message #481011] |
Fri, 29 October 2010 03:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It really is necessary to show exactly what you did. For instance, the queries you gave assume that the abc.abc_index_1 segment is named in lower case. This is highly unlikely, so I know that you have probably not told the truth. Therefore I cannot place any reliance on anything else. For example, you say the database size increased. Did it? I don't know, perhaps you are mis-interpreting something.
I know that some forum users get dis-heartened when a question is, in effect, rejected. But it is sometimes impossible to assist without better information
|
|
|
Re: Index rebuild grows database size [message #481044 is a reply to message #481015] |
Fri, 29 October 2010 10:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
preet_kumar
Messages: 204 Registered: March 2007
|
Senior Member |
|
|
Sorry that i did not paste the exact sql as the index name specified one of our organization name.
To make things more clear
After the rebuild of an Index i see the size of Index from dba_segments gone down from 8GB to 2GB whereas the database size increased.
My only question is why does the datafile grows after the index rebuild ?
|
|
|
|
|
Re: Index rebuild grows database size [message #481110 is a reply to message #481008] |
Sat, 30 October 2010 22:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/85219.jpg) |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
What do you mean by database size? The "database size" could have grown due to TEMP space usage during the index rebuild. Maybe your UNDO has grown. Does the "database size" take into account free space?
As others have said...too many unknowns.
|
|
|