Archives of the TeradataForum

Message Posted: Tue, 21 Nov 2006 @ 18:39:19 GMT

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

<-- Anonymously Posted: Tuesday, November 21, 2006 12:49 -->

We are three weeks into our first production use of PPI and Join Index and are experiencing a problem. Not sure if I have enough information to enable you to help me but I'll try.

We have a PPI of (code and date), partitioned by date (7 days per partition), on a 10.5 million row table. We also have a sparse single table Join index on this table that has a different PI and only half as many rows as the underlying base table.

This table is loaded every day with new data from the previous date, about 8 million rows per day. We are loading this data into a staging table first and then insert/selecting the data from the staging table into the target table's latest partition. This process has run in about 2 hours for the past three weeks. All of a sudden, this process is now taking over 6.5 hours. The only thing that changed the day before this went south is stats were collected on the 10.5 million row table.

I have checked and fresh stats exist on the base table, join index and staging table.

I'm not sure if we have just hit some tipping point where the size of the Join Index or table has caused some alternate plan to be chosen during the load but we are out of ideas at this point. I hope someone has encountered a similar situation in the past and can point me in the right direction.

Thanks in advance.

     Object       Disk Space    Row Count
     Base table   1,213TB     10.5 million  (Note: table contains a number of NUSIs.)
     Join Index     702GB      5.2 million  (Note: unable to do column compression in Join Index.)
     Stage table    565MB      8.7 million

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