Archives of the TeradataForum

Message Posted: Wed, 23 Mar 2005 @ 10:02:22 GMT

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

Naveen,

Before we can make any suggestions, we need to know some demographics and other answers.

Do you need to cleanup the table every day? Or just once a month. Volume by day or month.

Number of amps

Utilization of your system. How big is your window for the maintenance. Do you want to keep the data and archive it to tape?

The first thing I would question is the choice of the primary index. If it is truly the date and the number of rows is over several thousand per day, It is a poor choice for the primary index, as it is not unique enough.

It is also probably not what column you use to join to other tables, so all joins causes data redistribution. Loads to the table would also be very slow due to duplicate row checks unless it was a multiset table or had a unique secondary index.

If your date id is not the primary index, my first choice is normally to use Multiload delete to remove the tables. This technique is very efficient and also avoids transient journal usage.

Another option is if the dateid is the Primary Partition Index (PPI), you drop the rows by dropping the partition.

This would look something like

     alter table rdiehl.page_request
     modify primary index (session_id)
     drop range between DATE '2004-08-01' AND DATE '2004-08-31'  EACH
     INTERVAL '1' DAY
     with insert into rdiehl.page_request_arc;

The with insert moves the data to another table that must already exist.

If the table is rather small it might be acceptable to just use a DELETE statement.

Thanks,

Bob Diehl
Travelcoity.com

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