 |
 |
Archives of the TeradataForum
Message Posted: Thu, 16 Aug 2007 @ 18:50:51 GMT
| 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 :-)
Dieter
| |
