Archives of the TeradataForum

Message Posted: Sun, 29 Feb 2004 @ 13:35:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Rashi,

I hope your query did finish in the meantime...

My general comment would be - don't use a delete statement in your scenario as you might have the problem you just described.

There are two other possibilities

1. Use an INSERT/Select into a new table like

insert into new_tab select * from old_table where QuarterID <> 10;

and rename afterwards the old to save table and

the new to the table name you need. Drop the save table if everything succeeded.

The approach has the benefit that the source table is not locked and therefore accessible during the operation.

The disadvantage is that you need a lot more space (perm and spool) for this solution.

2. Use your SQL but use the MULTILOAD DELETE feature to submit it. This will lock your table but there won't be a rollback in case the mulitload fails for what ever reason and you just resubmit the job. Needs also less perm,spool and journal space.

Depending on your Teradata release you might also be able to use the partition primary index feature with a range_n on the the quater_id and "delete" by alter the range of the table.

But be careful and consider all other implications which this might have. PPI is good for fast deletes on the partition but might find negative impacts on your query performance - depending on the data model.

Kind regards

Ulrich

	<Prev	Next>		<<First	<Prev	Next>	Last>>