Archives of the TeradataForum

Message Posted: Tue, 14 Feb 2006 @ 21:24:19 GMT


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

Subj:		Re: Optimizer won't use PPI

From:		Victor Sokovin

          > select big.prdid, big.txn_id from bigtable_ppi big, calendartable cal
          > where cal.txn_dte = big.txn_dte
          > and cal.txn_dte between 1060206 and 1060213;

Explain - Uses PPI, spool joins to a single partition, no dynamic partition elimination, explain 8 seconds, actual 12 seconds

Could this increase in run time be caused by date format conversion? Try to synchronize the date formats between the big table and the calendar table, and also try to use the same formats on the date literals you use in the BETWEEN clause. There might be some potential for easy tuning there.

          > select big.prdid, big.txn_id from bigtable_ppi big ,calendartable cal
          > where cal.txn_dte = big.txn_dte
          > and cal.txn_dte between DATE - 7 and 1060213;

Explain - uses PPI, spool joins to all partitions, enhanced by > dynamic partitioning, explain 7.65 seconds, actual 6 mins 29 secs

This one looks like a bug but, again, you might get a better execution by minimizing date format conversions. This won't fix the bug but might speed up the query.

I think the PPI implementation is still in its early days. It is not like we can fire any queries and they will be optimized; we have to search for that sometimes unique form of the query which will give the right performance. Of course, marketing literature suggests otherwise but it is often ahead of its time.

Thank you for posting the test results.

Regards,

Victor


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

Attachments

Library

Quick Reference