• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

oracle database purging

 
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi,

I have to purge oracle database tables that is having records of almost 3 years. I have tried to delete with the help of below procedure but even in 24 hours not even single record got purged. Any help would be useful for me.




Regards,
Jaya
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Does the stored procedure compile? Does it have any errors?

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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's generally a good advice, but it has one caveat: truncate is a DDL operation, therefore it implicitly commits. Consequently the table will appear to be empty to anyone who tries to read it in the (arguably short) interval when the table will be reloaded from the temp table. This need not be a problem if there is a maintenance window to perform this operation, but in 24/7 environments this needs to be addressed somehow.
 
Jayesh A Lalwani
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, thanks for pointing that out. I made an assumption that removing 3 years worth of data would be something you would do in an maintanence window.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi Jayesh,

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 ?


Regards,
Joy
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This seems like a complicated issue. A few notes:

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.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi Martin,

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,
Joy
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please note that the temp table we're speaking about is a "normal" table which will be used only for the deletion of rows (it is not a global temporary table). I'm mentioning this just to make sure you won't lose your data in the process

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
 
Jayesh A Lalwani
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Martin has covered everything you need nicely.

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.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi Martin

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

Jayesh,

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,
Joy
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi Martin,

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


Regards,
Jaya
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

jaya kemmannu wrote:
Hi Martin,

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.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi Martin,

Thanks for you quick reply I will refer links suggested by you and will plan for Db purge as suggested by Jayesh.


Thanks & Regards,
Joy,
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic