Also, I might be wrong, but I think rownum doesn't work in delete queries
If I were you. instead of removing a batch of rows at a time, I would
a) Create a temporary table with the records that I want to keep
b) Truncate the table
c) insert the records from temp table back into original table
This would be much faster if you have lot more rows to delete. Also, you don't have to write a SP with a loop.. just 3 sql styatements On a very large table, 2 small inserts + truncate is faster than a big delete. Doing a big delete will cause the rollback segment to grow. Truncate just nukes data.
There was no problem with stored procedure and it was tested it test server. I have done purging with 20 hours of application downtime. I am sure that no other process was accessing database when i started purging. It looks weird because the script did not delete single record in 20 hour.
More over parent table has 8 child table and I purged record of all child tables but still from parent table i am not able delete record.
regarding the option you suggested,since it is cascade delete taking backup of all 8 table require huge space.
One more query that i have is if i disable all foreign key constraints in child table and purge parent table is that OK ?. Since all tables have huge set of records how long it may take to enable and disable foreign key constraints ?
1) Your solution is really quite expensive in terms of DB resources. Deletes will put big strain on your undo and redo. Frequent commits do not help either (therefore, you should at least try to make your batch size as big as possible).
However, the fastest way would be to create as select a new table from the old one, selecting only rows which you want to keep. Then drop the old table and rename the new one to the original name, and recreate all constraints the old table had. Do one table at a time to minimize space utilization. The temp table Jayesh suggested saves you the work with recreating table constraints, but the data you need to keep after the delete will be copied twice. Moreover, create as select can save lots of undo/redo space (depending on whether your DB is mirrored to a standby).
2) Check carefully for differences between test and production environments. Is the schema identical? Aren't some indexes/constrains unusable or invalid in prod/test? Is the volume/structure of actual data comparable? Is DB utilization the same? Aren't there any other processing active on prod which are not on test?
Some issues can even arise because of data copied by imp/exp from prod to test are stored in a different physical arrangements on the disks.
3) Make sure you've got valid and usable indexes on all affected foreign keys. This is very important, especially if you perform delete cascade. Deleting from a parent table which is referred by unindexed foreign keys causes locking of the whole child table, that may interfere with other processes taking place.
4) Delete cascade performs worse than doing it manually. I've done some experiments in the past and it seems that cascade delete can do perhaps twice as much work compared to deleting the records manually. But compared to the first point this is much less significant.
5) Enabling constraints can indeed take a lot of time needed to validate them. You can enable novalidate a constraint, which happens immediately, but you really should read about possible ramifications in the docs, I'm no expert on this. You might need to add rely to the command. And you'll certainly need to check twice that the constraint you've chosen not to validate is not violated - Oracle won't check, obviously. You can also validate a constraint that was created as novalidate later, I believe this is an online operation.
I will consider your suggestion that is mention in the point 1 i.e. by creating the temp table. Please clarify on below things that need to take care.
1) What will happen to the Indexes, Foreign key costraint (child table), primay key ( If it i parent table) when i truncate original table.
2) Anything to do with table space that is allocated to original tables ?
Regarding point 3, We have indexed one of the datetime column in the parent table, but foreign key constraint present in all the child tables are not indexed. so In my case do you think this this is the reason why purging take lot of time ?
Thanks and Regards,
Truncate removes all data from the table (including indexes), resets the high water mark and frees the segments from the table and indexes. All foreign keys targeting the table being truncated must be disabled, even when the corresponding child tables are empty.
Space used by the truncated table and indexes will be returned to their corresponding tablespace and can be reused in the same tablespace. (Please have a look at the documentation of TRUNCATE, it is all there)
Missing indexes on foreign keys is a serious issue when you're about to delete from a parent table. Think about it - when you delete from a parent table, the database must make sure there are no child records referencing the key being deleted. Without an index, the table has to be fullscanned (and you've got 8, presumably large child tables!), and furthermore locked for the duration of the operation (the locking details might have changed slightly in the 11g, but locking is probably not your concern here). Index on the parent table is not optional - without a unique index on the parent key, you would not be able to create the foreign key in the first place.
You should create the temp table to hold the data to keep using the create as select command. When copying the data back, you should probably use the /*+ append */ hint. Both of these will minimize the amount of undo/redo that will be generated.
There are other considerations as well. I'd suggest reading these two threads on AskTom throughly:
How to Update millions or records in a table
Deleting many rows from a big table
One thing I would note that going forward, you might want to design your schema to make purging of old records easier. If you have partitioning enabled, you could partition the tables by a date field, or maybe the ID of the master table. This way, when you want to get rid of records, you just truncate the partition, easy-peasy. There are other ways of making purging easier. I'm just saying that think about how you will make this process easier in the future.
I got confused with your statement "Truncate removes all data from the table (including indexes)" . since index is created on particular column of table how Index will also be removed with truncate ?. If i am not wrong truncate will not remove column of table.
Thanks for clarification on Temp table and missing indexes. MY parent table have unique index on the primary key.
Ex : CREATE INDEX IDX_COMP_TN_TXN_ID ON NP_HIST.NP_TN_STATUS_STATE_HISTORY (TN,NP_TXN_ID) TABLESPACE NP_HIST_IDX
I am not much into DB side. I am not sure whether my tables are partitioned. How can i check this. can you suggest some link to explore on table partitioning ?.
Thanks & Regards,
jaya kemmannu wrote:
I got confused with your statement "Truncate removes all data from the table (including indexes)" ....
No, indexes themselves are not dropped. But all data kept in indexes are removed and the allocated space is returned to the tablespace (by default).
You can find out whether your tables are partitioned by issuing select * from user_tab_partitions; in a SQL client from your schema. If there are any partitions, they will show up in the view.
However, you don't need just any partitioning, you need to partition the tables by the date column. And partitioning might negatively affect performance of some queries. While the scheme Jayesh suggest is indeed very useful for removing old data periodically, to set things up might require a bit of the DB side knowledge. The Deleting many rows thread on AskTom I posted earlier would be very useful to you if you wanted to setup such a partitioning scheme.
And one more note - I think partitioning is a paid (and perhaps costly) option even in the Enterprise Edition, so first of all, check out whether it is available in your database.
jaya kemmannu wrote:
If you look at my SP delete is based on Date column(REQUEST_START_TIME) which is already indexed. do you think still missing foreign key index matter here
Quite possibly. You said there are child tables with delete cascade FK constraints. It is these tables - the tables which are children of the table you delete from - whose foreign keys need to be indexed.