| Deleting from Huge table [message #117549] |
Thu, 28 April 2005 02:22  |
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 #117579 is a reply to message #117549] |
Thu, 28 April 2005 06:11  |
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
