Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 16 Aug 2007 @ 18:50:51 GMT

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

Subj:   Re: Only diff in Tables is PI column definition:
From:   Dieter Noeth

Anomy.Anom wrote>

  Three tables are identical in every respect except one: the unique PI column is defined as Decimal (9, 0) in two cases, as Integer in the third. (NOTE: column spelling is identical.) In each case the PI is demog_key.  

  Does Teradata (particularly the Optimizer) treat the PI definitions as being identical? Will joins of the tables generate comparable explains/access_paths? I thought that the DEC vs INT would create some significant differences.  

Decimals are stored as scaled integers, if there's no fractional digit, both columns are stored exactly the same.

And hashing is based on internal storages, thus:

     SELECT HASHROW(1234567 (INT)), HASHROW(1234567(DEC(9,0)));

       *** Query completed. One row found. 2 columns returned.
       *** Total elapsed time was 1 second.

     HASHROW(1234567)  HASHROW(1234567.)
     ----------------  -----------------
     89665E05          89665E05

  I have done some testing with a simple query (below) using a fairly small table (100M rows)and have provided the Explains which are slightly different. Note: the age_months column always contains NULL. The full table contains 56 columns. No Join Indexes.  

  Any experience or thoughts with this situation?  

The join planning is the same in all queries (as expected), just the estimated rows are quite different, so the timings differ, too.

  I have done some testing with approx 4M row table and found that the Perm Space Usage by AMP is identical (same number of rows and values in columns).  

Same hash, same AMP -> exactly the same distribution. And spool usage for all three queries should be exactly the same.

  Note, stats have been collected on the columns present in the query(s) and are identical.  

Don't ask me why the optimizer calculates totally different estimated rows for int vs. decimal :-)


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023