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...
~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...