'FORMAT' clause on index column & hashing

[SiMCard]

I've recently had an 'issue' with the follwoing:
~320m row table, UPI A, B, C where A, B and C are all INTEGER. Each of these cols has a "FORMAT '9'" clause (where 'n' differs). I am selecting from this table, joined to a table (8 rows, UPI 'B') on index 'B' which is defined identically. This is then dumped into a target table, indexed identically to the big source table.

The EXPLAIN is spot on - dupes the second table, then joins to big table. When it runs, it racks up 30Gb spool, drops to 12Gb (as expected - calculation -> answer set). This happens in about 10 mins (48-AMP 5250). It then sits there for *hours* (I had to kill the job after ~15 hrs) doing diddly squat. This reeks of data transformation (from experience in a previous existence). I copied the source data into a mirror table with no 'FORMAT' clauses - it now runs completely in ~8 mins(!).

I was wondering if anyone has any idea whether FORMAT would affect hashing (I've never been told so, and my mate the NCR trainer has never heard of it either).

Thoughts much appreciated

SiM Card...

 

[tdgulbra]

The first question I have is whether the target table for the insert is empty or not. If not, you are probably spending the time doing duplicate checks.

I have seen a format clause force a conversion to character (which would then have to be converted back to integer in your case), but I thought that was only on early releases of Teradata.

From experience: I usually try to do formatting in a view and do as little "data presentation" stuff as possible on the base table.

Hope this helps (even though I know it is not a very clear answer)

Tony

 

[SiMCard]

Thanks - I'll answer your points in order:

It's going into an empty multiset table. This is force of habit after working on a VA project with *huge* tables!

We're using V2R4, and there are times I'm still using 1=1 in an olde-school style in some places to get the machine to behave. The on-site NCR bods are informed each time. I did expect the EXPLAIN to tell me if any data transformation was taking place, which is what confused me.

I also never put FORMAT clauses in DDL (that was the way I found it, honest, guv), and with this experience, and others I can now quote, I hope to convince the project data architect to ditch them as well.

Thanx for the response - I'm glad I'm not going mad.

Si M...

 

[tdatgod]

Hi,
Have you filed a Report with the teradata GSC? If you are having this problem maybe other people are too. If you could provide them with the table definitions maybe they could simulate it in house and our developers can look at it.

Its possible that it is the row duplicate elimintation stuff, it possible there is a SORT going on.