Home » RDBMS Server » Performance Tuning » Delete data and transaction log problem
Delete data and transaction log problem [message #294410] Thu, 17 January 2008 08:40 Go to next message
juicyapple
Messages: 92
Registered: October 2005
Member
I have the problem to delete data in few tables with the size more than 5GB each. The transaction took more than 1 hour and would increase the transaction log size as well. It brings another disk size issue when whole database increase surprisingly. Please share with me if anyone got idea to shorten the run time of the transaction. Thanks.
Re: Delete data and transaction log problem [message #294419 is a reply to message #294410] Thu, 17 January 2008 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is none, a transaction is an atomic functional operation.
All you can do it to optimize this operation. The way to do it in highly dependent on your environment and what you do.

Regards
Michel
Re: Delete data and transaction log problem [message #294511 is a reply to message #294419] Thu, 17 January 2008 18:42 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Currently the practice I am doing is:

1. Create two temp tables, called temp1 and temp2.
2. Move the data in tbl_ori which need to be deleted into temp1.
3. Move the data in tbl_ori which need to be kept into temp2.
4. Create index for temp2.
5. Drop table tbl_ori.
6. Rename temp2 to tbl_ori.

But before all the steps, I have to stop all the inserting data into tbl_ori transaction. All these steps took times when I need to apply it for more than 10 tables.



Re: Delete data and transaction log problem [message #294512 is a reply to message #294410] Thu, 17 January 2008 19:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>2. Move the data in tbl_ori which need to be deleted into temp1.
WHY are you doing the step above?
What does it gain you?
Re: Delete data and transaction log problem [message #294516 is a reply to message #294512] Thu, 17 January 2008 20:15 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
>2. Move the data in tbl_ori which need to be deleted into temp1.

Sorry for confusing. The 'deleted data' will actually be kept for few days before it is deleted.

And I forgot to mention one step, after step 2 and also step 3, I will shrink log file to minimize its size.
Re: Delete data and transaction log problem [message #294547 is a reply to message #294511] Thu, 17 January 2008 23:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the number of rows in original table?
What is the percentage of rows you want to delete?
What is the criteria you use to choose those you want to delete?

Regards
Michel
Re: Delete data and transaction log problem [message #294568 is a reply to message #294547] Fri, 18 January 2008 00:53 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
>What is the number of rows in original table?
~50000000 rows
>What is the percentage of rows you want to delete?
30% - 40%
>What is the criteria you use to choose those you want to delete?

I will query the last maintenance date.

SELECT dtLastMaintain FROM tbl_maintain;


SQL
----------
2008-1-1

Then all the data one week after the maintenance date will need to be deleted (copied into tmp1).

INSERT INTO tmp1 SELECT * FROM tbl_ori WHERE dtTimeStamp < '2008-1-8';


And the data with dtTimeStamp => maintenance date will be copied to tmp2.


INSERT INTO tmp2 SELECT * FROM tbl_ori WHERE dtTimeStamp < '2008-1-8';


After that I just drop tbl_ori, rename tmp2 to tbl_ori and create index.

Re: Delete data and transaction log problem [message #294569 is a reply to message #294547] Fri, 18 January 2008 00:56 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Typo problem:

And the data with dtTimeStamp => maintenance date will be copied to tmp2.

INSERT INTO tmp2 SELECT * FROM tbl_ori WHERE dtTimeStamp >= '2008-1-8';
Re: Delete data and transaction log problem [message #294585 is a reply to message #294569] Fri, 18 January 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First:
SQL> select * from dual where sysdate >= '2008-1-8'; 
select * from dual where sysdate >= '2008-1-8'
                                    *
ERROR at line 1:
ORA-01861: literal does not match format string

Worse:
SQL> select sysdate from dual where sysdate >= '2009-1-8'; 
SYSDATE
-------------------
18/01/2008 08:15:03

1 row selected.

NEVER rely on implicit conversion and format. A date is not a string and vice versa.

Then, you should think to partition on dtTimeStamp and use exchange partition/table mechanism, this is almost instantaneous whatever is the number of rows.

Regards
Michel

[Updated on: Fri, 18 January 2008 01:56]

Report message to a moderator

Re: Delete data and transaction log problem [message #294591 is a reply to message #294410] Fri, 18 January 2008 01:37 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Sorry that I do not have oracle installed in this pc currently thus did not try it in sqlplus.
Re: Delete data and transaction log problem [message #294597 is a reply to message #294591] Fri, 18 January 2008 02:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry ...

No problem, it is just to show you the danger of relying in implicit parameters. Your query may work in your environment and not in others.
Note I modify the second query to emphasize this danger.

An important part of my previous post is about partitioning (if you bought this option).

Regards
Michel
Previous Topic: Query wih bind variables
Next Topic: Newbie: SQL runs slow on 10g, not on 9i
Goto Forum:
  


Current Time: Sun Dec 04 20:26:16 CST 2016

Total time taken to generate the page: 0.14257 seconds