Home » RDBMS Server » Performance Tuning » Deleting from Huge table
Deleting from Huge table [message #117549] Thu, 28 April 2005 02:22 Go to next message
abc123
Messages: 24
Registered: March 2005
Location: sa
Junior Member
I have a table containing aroung 30000000 records.

This table is used frequently in delete & update queries in some stored procedures which are executed daily and it takes huge time for the quries to execute.
Is there any way to speed up queries on this table.(Indexing is already done on all necessary columns.)
Re: Deleting from Huge table [message #117578 is a reply to message #117549] Thu, 28 April 2005 06:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://asktom.oracle.com/pls/ask/f?p=4950:8:5200313302144283370::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330

The above URL would explore all the possibilities.
Re: Deleting from Huge table [message #117579 is a reply to message #117549] Thu, 28 April 2005 06:11 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Try moving the table to different tablespace
or else
take an export & import of this table.
That'll make the high water mark level for this table to go down.
That'll improve performance.
Before doing exp/import do check for all exp/import options.
Re: Deleting from Huge table [message #117604 is a reply to message #117549] Thu, 28 April 2005 08:53 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Reorging the table via a move or an exp/imp isn't going to help (it probably would hurt) if you keep inserting and deleting a relatively stable volume of data over and over.

If you are doing a one-time large delete then asktom has some good advice.

Your situation sounds (although not nearly enough info) like it is totally what partitioning is for. Don't know if asktom talks about it in that link or not, but you should see about partitioning your table so that your operations are dealing with smaller chunks. Preferably avoid the need to delete altogether, just truncate partitions instead.

And of course keep in mind that the more indexes you have, the more things oracle must do when you insert and delete.
Re: Deleting from Huge table [message #117798 is a reply to message #117604] Fri, 29 April 2005 11:48 Go to previous message
pscjhe
Messages: 38
Registered: April 2005
Member
Apparently your stored procedures doing deletes/updates are real time and online. But couple things you need to clarify/observe before any actions can be taken.
  1. Is query very slow when thoese deletes/updates happening
     at same time OR the queries are slow anytime ?
  2. Are deletes/updates happening relateively clustered ?
     Are queries relatively clustered as well ?
     clustered: just a word I coined to say data access is 
     relatively close together vs spread out to whole data set. 

If yes to 1, you need to look at undo issue. is it well tuned
If no to 1. you need to look at execution plan, IO, index etc.
If Yes to 2, range partition may be helpful.
If no to 2, hash or list partition may be helpful.



Previous Topic: V$SYSSTAT
Next Topic: what should be approximate Size of SGA ?
Goto Forum:
  


Current Time: Tue Sep 29 08:50:00 CDT 2020