Delete performance from one large table or multiple small tables.
943510Jun 13 2012 — edited Jun 13 2012I have a table with 30+ MIllion rows, that is used as part of a statistic processing application developed in house.
As part of the process, there is an application that will parse out old records, and then delete them from the database.
I am looking for a way to improve this, and I am currently facing the alternatives:
Option #1
Seperate the older records into multiple smaller tables, and then run multiple copies of the application, one instance of the App per smaller table.
Option #2
Use additional criteria when choosing rows from the table (which would be indexed upon), an run mulltiple copies of the app based on the criteria.
From a logistical point of view I would prefer Option #2, my only concern with it is what is the performance comparrison between:
50 processes deleteing form 50 individual small tables with 100,000 rows in each
vs
50 processes deleting from a table with 30+million rows
Anyone have any experience on insight ?
We are using an Oracle 9i instance, no partitioning.
Unfortunatly I don't have a lot of control in the App or doing any major Oracle changes.