Archives of the TeradataForum

Message Posted: Thu, 13 Dec 2001 @ 12:19:02 GMT

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

Hi,

I have a bunch of customer receipts in a small "latest purchase" table (varying within 100 000 - 1.000.000 latest receipts)

and the customer history receipts are stored in the "purchase history table" having the same table structure but the size is growing during to time - up to the 900.000.000 limit which is a history for several weeks.

The customer_ID is the primary key/index in the both receipt tables. Just now there are no other indexes in both of the tables.

The "latest purchase" table is flushed frequently into the "purchase history table" (first with 'fexp' from the "latest purchase" table to a file and from the extract file with 'mload' to the "purchase history table".

Is there any way to speed up the query where the frequency of the customer purchase is calculated by searching the current customer receipt from the "latest purchase" table and the previous receipt either from the "latest purchase" or from the "history table"?

The upper limit to calculate the frequency is however that we are not insterested of history which is longer than 2-3 days. The purchase date is used as a limiting search criteria for both the "latest purchase" and the "purchase history table"?

The query runs quite nice (few minutes) when the history is just a couple of days, but starts to take a longer time when there is a history of several days.

We have tried to set an index for the 'purchase date', but that did not seem to help at all.

Regards,

-------
Sauli Harala
EDS Oy

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