Archives of the TeradataForum

Message Posted: Wed, 06 Apr 2005 @ 21:36:20 GMT

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

All:

I'm running V2R5.1.2, and have the following query:

     select a.cal_date, b.txn_id, b.str_nbr, b.register_nbr, b.txn_type,
     b.txn_nbr
       from dim_time a
        join pos_txn  b
          on a.per_id = b.per_id
       JOIN DIM_TIME C
         ON C.CAL_DATE = CAST('2005-04-03' AS DATE FORMAT 'YYYY-MM-DD')
       JOIN DIM_TIME D
         ON D.CAL_DATE = CAST('2005-04-04' AS DATE FORMAT 'YYYY-MM-DD')
      where b.per_id between C.PER_ID and D.per_id.

Table a - DIM_TIME has a UPI on per_id and statistics collected on the UPI.

Table b - POS_TXN is partitioned by per_id, has statistics collected at the column level on that field.

The period ids can't be explicitly passed, however, they can be derived via join logic. The bad news is the explain shows all partitions being scanned rather than specific partitions referenced in the predicate - no partition elimination.

Is this behavior correct? I thought dynamic partition elimination in V2R5.1 would handle the above scenario.

Any insight is appreciated -

Wayne Schachtel
Wayne Schachtel Consulting

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