Database Cleanup 1

[THoey]

This is a follow-on to thread186-80645. As mentioned there, I have a large table (approx. 9 million rows) that I am trying to remove approx. 3.5 million old records from. I am currently on the backup system/database. Since this query is looking like it is going to take 2+ days to run, I can't see how I will be able to do this on my production side server.

I have thought about breaking up the deletion into smaller chunks, but it will still require a full table scan, which I think is the reason for this thing running so long.

I have also played with the option of creating duplicate tables (there are actually three large tables linked by a key field, but I have cleaned most of the other two up already), running a query to copy just the data that I want to keep into these duplicates, dropping the PK\FK links, truncating the tables, and then reloading the data from the duplicates. The problem with this is that even though I have been tasked to do this, I have not been given DBA permissions needed for the truncate command. Also, I am not sure how long it would take to rebuild the PK\FK link.

I am looking for suggestions. I have never played with a database this large and just didn't expect the time that it is taking to do things to be soooo long. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

In my opinion you should investigate breaking your delete into smaller chunks.

I don't believe that a table scan of your large table would take anything close to two days to complete. You can test this, of course. Try running a query with a non-indexed column in the where clause. The time that it takes establishes a baseline. Everything beyond that time is the overhead of doing the deletes.

I would also encourage you to drop all indexes and keys prior to the delete, and recreate them afterward. Here too, you can run benchmarks to see how long this will take. I'm betting that it will be much faster than updating the indexes/keys during the deletes. Of course, this presupposes that you can run the cleanup during a time when your customers aren't running queries that need the indexes.

 

[THoey]

*** Posting this in the two threads that I have started on this subject ***

Well, it has been decided by our "DBA" to terminate the script that I was running and wait for it to rollback. Through Oracle DBA Studio, I did a Disconnect Immediate. As far as I can tell, this will terminate the script and allow the rollback segments to roll back. Am I correct in that?

At that time he has suggested the following:

- Alter the table to turn off logging
- Drop all indexes
- Run the delete script for the whole chunk of data
- Re-build all indexes

Since I have so little permissions on this database, my first question is since I own the objects in question, will I have permissions to do what he has suggested?

He also mentioned he could turn off database level logging and that might speed things a little bit more.

Does this sound feasible to you all? I trust this bunch here, more than I do him. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

Hi Terry,

I see a thread of yours from early April deals with this same delete issue. At the time you were attempting to delete a month's worth of data at a time. Even with this restriction the delete was extremely slow, so your experience with breaking the delete into smaller chunks has not been too favorable. No wonder you're looking at alternatives.

If you still want to pursue the &quot;smaller chunks&quot; method, I would try adding &quot;and rownum < nnn&quot; to the where clause of your delete statement. That would give you complete control over the amount of data that you delete in each chunk. You could set the number of rows small enough that your delete would finish in a reasonable length of time. At the very least, this would give you a means to estimate how long the entire cleanup will take.

 

[karluk]

If you are the table owner there shouldn't be a problem with permissions.

I believe that turning off table logging won't do you any good. I think that nologging only applies to certain table operations, and that deletes are not one of them.

What does your dba mean by &quot;turning off database level logging&quot;? Is he planning to turn off archiving? That would suppress archive log file creation, but in my opinion the potential gain isn't worth it.

 

[THoey]

Karluk,

Thanks for your information. Once I am done here I will give you a well deserved star just for putting up with me.

I will wander out to Oracle's site and verify the NOLOGGING not applying to deletes.

As for the database level logging, I gather that is what he is talking about, as he mentions that he will have to shutdown and restart the database to do it.

See what I meant by trusting the advice given here before I trust this DBA? ;-) Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

Don't be too hard on your dba, even if he's wrong about table logging. Problems like this are ususally solved by looking at a number of approaches and picking the one that works best. As a result it's almost always best to get as many opinions as possible.

As a matter of fact, that nologging suggestion gives me an idea that may work quite efficiently. I'm fairly sure that nologging does apply to a &quot;create table ... as select ...&quot; statement. That means you could create a copy of your table, selecting only those rows you want to keep. When you're finished, drop the old table and rename the new one, creating any necessary indexes and keys. If you use the nologging option (and there's enough disk space available), this might be the way to go.

 

[THoey]

Thanks for the advice on the DBA. I'll try not to be too hard on him, but it's difficult sometimes, especially the way the company has this set up. But that's another story that can only be told over a cold one. Or two...

The idea that you came up with seems similar to what I mentioned before of creating a duplicate of the table structure, loading it with the data that I want to keep, truncating the original and then loading it from the dupe. Only difference is that you are creating the table and loading it in one step. Would either way work with the NOLOGGING?

You mention renaming the copy. I don't see an option for renaming in DBA Studio. Matter of fact, if you edit the table, the name is greyed out. Wouldn't I have to move the data back? Or is there something new that I don't know about? (Alright, I KNOW there is LOTS I don't know about ;-) )

And as I typed this, the mail guy delivered my copy of Oracle 8i The Complete Reference and Oracle 8i DBA Handbook. What timing... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

I admit that my idea isn't too original. It's essentially the same as yours, but the nologging possibility gives it more potential than I originally thought.

I think nologging doesn't apply to ordinary dml. That includes both inserts and deletes, so you will need to use the &quot;create table ... as select ... nologging&quot; syntax. That's the command that I hope will suppress logging during the inserts.

&quot;Alter table old_name rename to new_name&quot; works in Oracle 8. I can't remember if it was also available in Oracle 7.

 

[THoey]

Thanks... I'll give it a try. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[sem]

You may also try to manually lock the whole table, to eliminate resource consumptiuon for row locking, and assign this transaction to the largest rollback segment you have. Also avoid utilizing indexes by + full or + no_index hints. These also helps, but maybe no very significantly. As for renaming, I'm not sure that that alter table exists in Oracle 7, but sql*plus command rename works (as far as I know it implicitly exports/imports the table)

 

[THoey]

Okay, the rollback has finally cleared itself and I am trying this again. I turned off the logging on the specific table. I tried running the following script and got this error:

SQLWKS> CREATE TABLE c_contact2 AS (
     2>    SELECT * 
     3>    FROM c_contact
     4>    WHERE date_of_contact >= '01-APR-00'
     5> ) 
     6> TABLESPACE TCL_01_DAT
     7> 
TABLESPACE TCL_01_DAT
*
ORA-00933: SQL command not properly ended

I added the TABLESPACE option because the first time I ran it, it tried to build it in the USER tablespace. I have used this before, but not in a CREATE TABLE ... AS ... statement. I just tried to change my logins default tablespace, but I don't have the privileges to do that.

So, how do I run this and build the temp table in the proper tablespace? Is there something wrong with my SQL? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

Try

CREATE TABLE c_contact2
TABLESPACE TCL_01_DAT
NOLOGGING
AS ( SELECT *
FROM c_contact
WHERE date_of_contact >= '01-APR-00'
)

I think that it's the nologging option on the create table statement that will suppress logging, not altering the original table to nologging.

Make sure that you alter the table after creation to turn on logging:

alter table c_contact2 logging;

You can also use nologging when creating indexes and primary keys. For example

alter table c_contact add constraint c_contact_pk primary key (col1,col2) using index nologging;

Again, turn logging on afterwards

alter index c_contact_pk logging;

 

[sem]

CREATE TABLE c_contact2
TABLESPACE TCL_01_DAT
AS (
SELECT *
FROM c_contact
WHERE date_of_contact >= '01-APR-00'
)

 

[THoey]

Karluk,

You are the man. It is screaming through. I'll let you know how it turns out...

Thanks again... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Karluk and Sem,

I just want to thank you two so much for your help. I got a script all put together that does exactly what I need it to do. I ran it against my DEV instance today and it did the job in just over 20 minutes. I went back through and found a couple things that I didn't think of (losing role permissions when you drop a table and then recreate it), but I added those in and everything looks great. I am going to come in Sunday and run it against my Prod instance.

Thanks again for everything. I only hope that I can get to your level and be able to repay you by helping someone else out. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

Good luck, Terry. Make sure to take a backup after you finish. I haven't researched what all those nologging operations do to database recoverability, but I bet it's not pretty.

 

[THoey]

As a follow up to this. I came in Sunday, did a little prep work and then ran the script that you two helped me write. Took 74 minutes for the clean up to run and deleted more than 5 million records. The only thing that was odd was that the table I did the CREATE AS ... against had a field with a default value and was set to NOT NULL. For some reason, those attributes did not come across. Took a little bit for us to recognize that, but I have taken care of it.

I could not have got it done that smoothly or quickly without your help. Thanks again. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.