Archives of the TeradataForum

Message Posted: Tue, 14 Feb 2006 @ 20:04:33 GMT

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

First of all, apologies for the length of the mail. However, following the previous e-mail on this topic relating to forcing dynamic partition elimination by equijoining the big PPI table to a calendar table and filtering on the calendar table, I've done some testing but I'm afraid I'm none the wiser in terms of how to enforce best performance.

Performance appears to relate not to whether you can enforce dynamic partition elimination but to whether the optimiser recognises your spool file of duplicated dates can join to one partition or all partitions and I must admit I'm a bit confused as to when this does happen and when it doesn't. (See examples below) For info, we have recently upgraded to V2R.05.01.02.23.

Baseline - table without PPI

     select big.prdid, big.txn_id from bigtable big, calendartable cal
     where cal.txn_dte = big.txn_dte
     and cal.txn_dte = DATE

Explain - does not use PPI, duplicates dates table, product joins to all partitions, 3 mins 24 secs, actual 1 minute 10 sec

Tests - all against PPI table

     select big.prdid, big.txn_id from bigtable_ppi big ,calendartable cal
     where cal.txn_dte = big.txn_dte
     and cal.txn_dte = DATE

Explain - uses PPI, spool joins to a single partition, enhanced by dynamic partition elimination, 8 seconds, actual 3 seconds

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

Explain - uses PPI, spool joins to a single partition, enhanced by dynamic partition elimination, 8 seconds, actual 3 seconds

     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

     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 DATE;

Explain - Uses PPI, spool joins to all partitions, enhanced by dynamic partitioning, 8 seconds, actual 3 mins 58

     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 DATE;

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

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

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

     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

Any views ?

Regards,

Alan Okell
LTSB

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