Target Table option Delete Data from table before loading

dataservices job design
1

Hi,

Can anyone tell me in which case the Target Table option “Delete data from table before loading” is NOT the best scenario?

Expertise on my current project tells me using this property might slowdown the process of deleting records when you have lots of rows? Anyone familiar with this problem?

At this moment we have an alternative ; we do a “truncate table” designed at workflow level!
But I was always teached to use the property in the Target table :?

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

2

If you are using this to delete records in a fact table with referenced key, this option might take a longer time as you said. But other wise it should not.

Arun.K :us: (BOB member since 2011-10-18)

3

Hi,

In our DWH our first step is to load data to a staging table. These tables can be very big. The next time we do this we like to delete all records before the Dataflow starts. That is our situation.

We have DB2 as a database. The problem is when we use the property “Delete data from table before loading” the Dataflow takes too much time. That is because actually what this property does is a delete statement. That is different with a truncate statement because this statement acts differently “under water”.
A delete to the database takes much longer than a truncate. This extra waiting time is not appreciated.

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

4

Truncate is always faster than Delete, whether done at DB or DS level.

Delete is an operation on per row basis, that is each row is logged and deleted physically from the table.

Where as, truncate does not logs the rows that need to be deleted. The above is a very simple explanation, there is much more to it :slight_smile:

How many records are you dealing with in the staging table?

Arun.K :us: (BOB member since 2011-10-18)

5

You’ll want to avoid using that feature if the target is Oracle and you are not in as the schema owner. An Oracle user that is not the schema owner of a table cannot directly use TRUNCATE TABLE.

If your ETL is not logging in as the schema owner then do this:

  1. As the schema owner deploy a stored procedure that has in it the syntax to do a TRUNCATE TABLE using the EXECUTE IMMEDIATE command.
  2. Grant EXECUTE rights to the stored procedure to the user that the ETL logs in as.
  3. In your ETL import the stored procedure into your Datastore. (this is optional but handy)
  4. In script, call the stored procedure passing in the name of the table to truncate.

eganjp :us: (BOB member since 2007-09-12)