Archives of the TeradataForum

Message Posted: Fri, 18 Mar 2005 @ 21:55:47 GMT

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

I've been doing some limited testing with partition elimination on my laptop version of Teradata (V2R5.0.X) and understand without explicitly referencing partitioning column values, partition elimination will not be performed.

My question is, with software release V2R5.1.X and later, can indirect (non explicit) partition elimination be performed? After reading the V2R5.1 documentation, I'm still not sure. For example, TBL1 is partitioned on txn_date, TBL2 is not. Will any of the three test queries perform partition elimination w/later versions of TD software?

     select a.*, b.*
       from TBL1 a
        join TBL2 b
          on a.txn_date = b.txn_date
        and a.txn_str    = b.txn_str
        and a.txn_reg   = b.txn_reg
     where a.txn_date between current_date - 14 and current_date + 10;

     .import vartext '|' file=xxx;
     using (d1 varchar(10), d2 varchar(10))
     select a.*, b.*
       from TBL1 a
        join TBL2 b
          on a.txn_date = b.txn_date
        and a.txn_str    = b.txn_str
        and a.txn_reg   = b.txn_reg
     where a.txn_date between cast(:d1 as date) and cast(:d2 as date);

     select a.*,b.*
       from TBL1 a
        join TBL2 b
          on a.txn_date = b.txn_date
        and a.txn_str    = b.txn_str
        and a.txn_reg   = b.txn_reg
     where a.txn_date in
        (select txn_date from TBL2) ;

Any help is appreciated,

Wayne Schachtel
Wayne Schachtel Consulting

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