Archives of the TeradataForum

Message Posted: Tue, 10 Sep 2013 @ 16:08:49 GMT

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

If you're deleting a large number of rows it may be more efficient (if you have the space available) to copy just the rows you want to keep to a new table, drop the old, and rename. Deleting in place requires transient journaling, and that can take a while for 10.9 million rows.

As for partition elimination, casting T2.LAST_PACE_ID back into an integer would save a data type conversion. However, since the optimizer is accessing the large table via the NUSI, I doubt you'll get any partition elimination.

Partition elimination and NUSI access are typically mutually exclusive (at least they are on the version I'm running-v12). You may be able to get partition elimination if you drop the NUSI on hotel_id, although it may slow down the join and the initial extract into spool. But again, if the merge step is taking most of the time, this probably won't help much.

If none of the above works, MLOAD delete may also be an option to explore if you need to delete in place.

Dirk Anderson

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