Hi, I need to run a job to insert yesterday's data to one table in Oracle and once that job is successful i need to delete old data from the same table. The data is huge around 400,000 for a day. What is the best way to delete the rows from DB quickly? Many Many Thanks, Manju
Hi, Can i use tOracleRow inorder to perform the same? I couldnt find any posts which explains so. Can anyone please help me on this? Many Thanks, Manju
Hi Manju, Yes, tOracleRow would be the component to use. Assuming you are always deleting yesterday's data: DELETE FROM myTable WHERE myDay = current_date - 1; Use a variable in Talend to place the Things that could make this quicker. 1) Have an index on myDay. Big improvement. 2) Use a variable in Talend to place the actual date value instead of "current_date - 1". Slight improvement.
****BE CAREFUL : TRUNCATE IS NOT TRANSACTIONAL AND CANNOT BE ROLLED BACK*** 3) If the table only contains yesterdays data, you could use TRUNCATE myTable. Much faster than delete. ****BE CAREFUL : TRUNCATE IS NOT TRANSACTIONAL AND CANNOT BE ROLLED BACK***
Hi, Thank you very much for your reply. Can you please tell me what other components should i be using with tOracleRow? Should the job look like this? tOracleInput---->tOracleRow---->tOracleOutput And i should be placing the query you mentioned in the tOracleRow settings right? Can you please clarify my doubts? Many Thanks, Manju
Don't delete the data from the source table until it has been successfully loaded into the target table. -- on SubJobOK --> tOracleRow. The delete statement goes in the query window of the component view for the tOracleRow tab, surrounded by quotes, and without the semi-colon. "DELETE FROM myTable WHERE myDay = current_date - 1" Thanks, Ben
Hi Ben, Many thanks for your reply. When I try this , "delete from DTSET_AKAMAI_LOG " i works fine. But when i include the current_date in the query , like this "delete from DTSET_AKAMAI_LOG where REQUESTED_DATE =current_date -1" it doesnt delete the rows. The date format in DB is 25-APR-2012. Is that the reason? Please advice me how to go about this. Thanks, Manju
Hi,
Try using the below one it should work
"delete from DTSET_AKAMAI_LOG where REQUESTED_DATE =to_char(current_date-1, 'dd-MON-yyyy')"
Thanks and Regards,
Pavan
But when i include the current_date in the query , like this "delete from DTSET_AKAMAI_LOG where REQUESTED_DATE =current_date -1" it doesnt delete the rows. The date format in DB is 25-APR-2012. Is that the reason?
Thank you Manju and tpk. I understand why you used the to_date function (because current_date includes the time and the query would only have caught records loaded the previous day at that exact time), but switch to "days - 7"? Also, for references, in Oracle you can select "from dual" to see what the expected result would be. example : select to_date(sysdate,'dd-MM-yy')-7 from dual This way you can see what the function returns and tweak it to match what you expect.
