topic Re: How to delete bulk data from Oracle DB in Talend Studio

How to delete bulk data from Oracle DB

Anonymous — Mon, 23 Apr 2012 14:54:33 GMT

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

Re: How to delete bulk data from Oracle DB

Anonymous — Mon, 23 Apr 2012 15:17:50 GMT

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

Re: How to delete bulk data from Oracle DB

Anonymous — Mon, 23 Apr 2012 22:09:01 GMT

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***

Re: How to delete bulk data from Oracle DB

Anonymous — Tue, 24 Apr 2012 09:46:04 GMT

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

Re: How to delete bulk data from Oracle DB

Anonymous — Tue, 24 Apr 2012 17:22:25 GMT

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

Re: How to delete bulk data from Oracle DB

Anonymous — Wed, 25 Apr 2012 10:16:03 GMT

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

Re: How to delete bulk data from Oracle DB

Anonymous — Wed, 25 Apr 2012 10:59:25 GMT

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?

Re: How to delete bulk data from Oracle DB

Anonymous — Wed, 25 Apr 2012 11:31:18 GMT

Thanks for your reply. Could solve this by using the following :-
"delete from DTSET_AKAMAI_LOG where REQUESTED_DATE =to_date(sysdate,'dd-MM-yy')-7 "

Re: How to delete bulk data from Oracle DB

Anonymous — Wed, 25 Apr 2012 17:54:14 GMT

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.