Home » SQL & PL/SQL » SQL & PL/SQL » Table index taking huge amount of space
Table index taking huge amount of space [message #204848] Wed, 22 November 2006 06:39 Go to next message
srinivasan544
Messages: 3
Registered: November 2006
Junior Member
Hi All

We have a package which runs under a transaction weekly - In this package I need to delete the whole data from a table and insert the data from another table. This table is having an non-unique index. This table is having 3-4 million records which gets deleted every week and inserted.
And the problem is after the above package run, the index space
shown to be 13GB. And the thing is I can't drop the index before running this procedure as this table is used by our online application.
Please suggest some goog solution for this problem.


Thanks & Regards
Srinivas.M
Re: Table index taking huge amount of space [message #204849 is a reply to message #204848] Wed, 22 November 2006 06:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Most probably it is because of HWM.
Instead of deleting the whole data from table, truncte the table
and rebuild the index.
>>And the thing is I can't drop the index before running this procedure
But there is no baseline data. Right?
So just rebuild hte index online.
And, collect statistic on tables and indexes.

[Updated on: Wed, 22 November 2006 06:42]

Report message to a moderator

Re: Table index taking huge amount of space [message #204850 is a reply to message #204848] Wed, 22 November 2006 06:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you are deleting and re-inserting the same number of rows, I would not expect the size of the index to change significantly.

When you say 'the index space', do you mean the space occupied by this one index, or the size of the Index tablespace?
Re: Table index taking huge amount of space [message #204851 is a reply to message #204849] Wed, 22 November 2006 06:45 Go to previous messageGo to next message
srinivasan544
Messages: 3
Registered: November 2006
Junior Member
Hi Mahesh

I have less knowledge about online creation of indexes, could you please provide some more details.

>>>>>>But there is no baseline data. Right?
>>>>>>So just rebuild hte index online.
>>>>>>And, collect statistic on tables and indexes.

Thanks and Regards
Srinivas.m
Re: Table index taking huge amount of space [message #204854 is a reply to message #204850] Wed, 22 November 2006 06:49 Go to previous messageGo to next message
srinivasan544
Messages: 3
Registered: November 2006
Junior Member
This is the size of the Index tablespace.
please suggest some solution.
Re: Table index taking huge amount of space [message #204869 is a reply to message #204854] Wed, 22 November 2006 07:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the size of the index tablespace before you perform this operation?

I'm guessing that it's still about 15Gb.
Re: Table index taking huge amount of space [message #204874 is a reply to message #204854] Wed, 22 November 2006 08:00 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
srinivasan544 wrote on Wed, 22 November 2006 07:49
This is the size of the Index tablespace.



Well, your tablespace is not going to change in size after an operation like this. I know, I know, if you are maxed out in the tablespace, yes, it may autoextend to add another extent.
Previous Topic: call procedure in function
Next Topic: with out union
Goto Forum:
  


Current Time: Tue Dec 06 15:50:14 CST 2016

Total time taken to generate the page: 0.32739 seconds