Archives of the TeradataForum

Message Posted: Tue, 21 Nov 2006 @ 22:07:31 GMT

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

Anom,

First your row counts do not make sense. If you are loading 8 million rows a day, yoru base table would have more than 10.5 million rows. If this is the case then check to make sure you are cleaning up your stage table.

I would look at Skew on your primary index. It might be a NUPI duplicate row check. You really should only have about 5000 rows per PI value. Real formula for finding max number of rows for a PI value before NUPI duplicate row checks cause a problem is (block size / average_row_length)

You really want every row to fit into one block. I have found that it is usually OK if every row can fit into two blocks.

Use query like one below to find out how many occurrences per PI value.

If Primary index (code_col, ppi_date_col)

     Select code, ppi_date_col, count(*) from databasename.tablename
     -- Where ppi_date_col = '2006-11-01'
     group by 1 order by 2 desc having count(*) > 1000

If Primary index (code_col)

     Select code, count(*) from databasename.tablename
     group by 1 order by 2 desc having count(*) > 1000

You should be able to run this for one of Partitions where it took a long time to insert to save time.

You should also look for hash collisions, but my past expeiricnces find it on PI values. (not including query since I do not have readily available).

It could also be the maintenance of the secondary indices on the base table.

Use Query Logging to see if they are used. If not drop them.

I also wonder if your insert select does any checking to make sure row does not already exist in the base table. If it does try one of two things.

1) use NOT EXISTS instead of NOT IN as NOT IN will be a full table scan of your Base table

2) dynamically create insert select NOT EXISTS or NOT IN to only include PPI range for incoming data.

Hope this helps.

Thanks,

Bob Diehl

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