Archives of the TeradataForum

Message Posted: Mon, 20 Feb 2006 @ 13:33:53 GMT

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

Rishi,

How large is your system?

If I understand this correctly, you have a 4.5 billion row staging table (300 million X 15 days).

The PPI is not helping this query much because your constraint is qualifying 11 out of 15 partitions. So, even with the PPI, you are scanning 73% of the table (still 3.3 billion rows). You can verify that the system is only accessing 11 partitions by running an explain on the query. If your system is fairly small, having an aggregation of that many rows take more than one hour is not bad.

If you are v2r6 or later, adding a NUSI on some non-unique column would help this query (something more unique than date), since it could simply count the rows in the NUSI sub-table, rather than scanning all of the rows (I believe that the NUSI sub-table is PPI-aware in v2r6 and later).

Rather than trying to improve the performance of this query though, I would recommend that you focus on the query/queries that are going to be accessing this table and tuning for those. You mentioned that this is a "staging" table. Does that mean that the users will not be accessing it? If so, what process will be running against this table?

If users will be accessing this table, what criteria will they be providing to the queries? Once you know this, you can start creating indices/collecting stats/etc., to tune for these queries.

Thanks,

Barry

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