| Delete data and transaction log problem [message #294410] |
Thu, 17 January 2008 08:40  |
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 #294511 is a reply to message #294419] |
Thu, 17 January 2008 18:42  |
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 #294516 is a reply to message #294512] |
Thu, 17 January 2008 20:15 |
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 #294568 is a reply to message #294547] |
Fri, 18 January 2008 00:53 |
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 #294585 is a reply to message #294569] |
Fri, 18 January 2008 01:22 |
 |
Michel Cadot
Messages: 68665
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 #294597 is a reply to message #294591] |
Fri, 18 January 2008 02:00 |
|
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
