Hi,
Thanx for replying.
Here are the 2 tables i want to split which are interlinked by OP_ID.
Table_1
±--------------------±-------------±-----±----±-------- ±------+
| Field | Type | Null | Key | Default | Extra |
±--------------------±-------------±-----±----±-------- ±------+
| OP_ID | int(10) | | | 0 | |
| NUMBER | varchar(20) | YES | | NULL | |
| SET | varchar(50) | YES | | NULL | |
| SESSION_ID | varchar(100) | YES | | NULL | |
| EVENT_NUM | int(4) | YES | | NULL | |
| REPEAT_NUM | int(4) | YES | | NULL | |
| OP_COUNT_IN_SESSION | int(4) | YES | | NULL | |
| APP_ID | varchar(25) | YES | | NULL | |
| CONTAINER | varchar(25) | YES | | NULL | |
| OP_TYPE_ID | int(3) | YES | | NULL | |
| SETKU | varchar(50) | YES | | NULL | |
| IP_ADDRESS | varchar(25) | YES | | NULL | |
| RST_TO_CLIENT | varchar(10) | YES | | NULL | |
| SERVICE_RST | varchar(10) | YES | | NULL | |
| OP_START_TIME | datetime | YES | | NULL | |
| OP_END_TIME | datetime | YES | | NULL | |
| SERVICE_START_TIME | datetime | YES | | NULL | |
| SERVICE_END_TIME | datetime | YES | | NULL | |
| ENTRY_TIME | datetime | YES | | NULL | |
| AFFILIATE_TAG | varchar(10) | YES | | NULL | |
±--------------------±-------------±-----±----±-------- ±------+
Table_2
±------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------+
| OP_ID | int(10) | YES | | NULL | |
| PARAM_NAME | varchar(50) | YES | | NULL | |
| PARAM_VALUE | varchar(50) | YES | | NULL | |
±------------±------------±-----±----±--------±------+
As this is a part of the running application as soon as i split this table using the below queries the table got locked and no insertion and select is executed and its taking a long time so we cann’t pull the application off for such a long time duration.
Create Table Table_1_OLD as select * from Table_1 where date(ENTRY_TIME) <‘2007-01-01’;
Create Table table_2_OLD as Select A.* from Table_2 A, Table_1_OLD B where A.OP_ID=B.OP_ID;
Uptill this point everything is ok as i’m creating a new table having the data older then 2007 to some other tables.
But as soon as i try to delete the data from these tables Table_1 and Table_2 it get locked up and no insertion can be made on this causing a problem.
delete from Table_1 where date (ENTRY_TIME) < ‘2007-01-01’;
delete from Table_2 where OP_ID <= (select max(OP_ID) from Table_1_OLD);
I hope everything is clear now. If you still have any doubt please let me know and also suggest some effective solution for this.
Regards
Neo Pheonix