Archives of the TeradataForum

Message Posted: Thu, 30 Jul 2009 @ 08:58:28 GMT

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

Hello,

Try reducing the amount of records getting accumulated in spool.

I can think of the following modifications:

1. Convert your view 1 into a derived result set and select only those columns necessary for the view. Shift then the"where...NOT in" clause back up in the derived result set. If there are duplicates and any "amount/quantity" columns are involved with view 1, make sure that you take distinct values by doing a group by(not a distinct). Group by works better than 'distinct'.

2. If at any place you feel that the join results in row-level duplicates....make sure you remove the duplicates first by joining with a derived result set(of the view) rather than the view directly. As a test, just try converting each view into a derived result set and remove row-level duplicates wherever possible. Select only those columns relevant for your view.

3. Check in the explain plan whether partitioning is being used or not. Also you've performed some joins on the PI. Check the explain plan for usage of PI in those joins. I can hardly find a reason for a query that runs slow, despite using the PI and PPI. Consider using a join index as well.

This is a trial and error process.

Regards,

Suhail

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