Best way to delete rows via OCI8?

bradinmi · Post by **bradinmi** » Fri May 30, 2003 6:59 am

We have a monthly job to delete rows from an Oracle table. There are two ways to go about this, and I'm wondering which is the most efficient and easiest to recover from if there's a problem.

Option one is to have an OCI8 stage that issues a seperate DELETE command for each row we wish to delete, issueing a COMMIT (Transaction Handling) after every so-many rows, like 50,000. This method would first entail using a SELECT command to get the ID's we need to remove and putting them in a sequential file as input to the stage to do the DELETE.

The second option is to issue one DELETE command with an incoming parm value of a date. For example,
DELETE FROM MY_TABLE WHERE POSTING_DATE < '01-MAR-2003'

Which would work the best?

Brad Vincent
Compuware @ The Detroit Medical Center

steven · Post by **steven** » Fri May 30, 2003 11:28 am

In our current project, we need to do DELETE as well and we use the PRE-SQL option in our Target OCI. Write your load job as you will and in your target OCI, there's a tab for Pre and Post SQL. Put your DELETE statement right there and the records are deleted before the first insert happens.

Don't know if this is suitable for you...but works well for us. We preferred this option to the first one you mention

Steve

vmcburney · Post by **vmcburney** » Sat May 31, 2003 7:33 pm

That's a good point, for a single large transaction Oracle needs rollback space, and if there is a trigger on the table the transaction can become very large. Single delete statements with 1000 row transactions should be more robust than a single delete for large amounts of data.

Vincent McBurney
Data Integration Services
www.intramatix.com