Archives of the TeradataForum
Message Posted: Tue, 17 Feb 2004 @ 23:55:50 GMT
TDATA Version 5.0, merging two tables partitioned on primary index by date range
Whatever I do, in a Multi Statement Request with volumes of data, I obtain ERROR 5728: Partitioning violation for table TARGET.
I have three tables with identical DDL (except the name).
The 2nd was created as first with no data. 3rd likewise.
All 3 have a single column primary index partitioned by range_N(dte_reported between '20003-01-1' and '2004-01-01' each interval '1' month)
Tables 1 and 2 are both over 15Gb and I want to use a Multi-Statement_Request both for this case and to establish the way for several large table merges, avoiding journalling several hundred million individual rows.
In each case, EXPLAIN reveals no problems.
Insert into TARGET sel * from table1 ; Insert into TARGET sel * from table2 ;
obtained ERROR 5728.
Since all the data is in tables that have the same partition range I could not see how there could be invalid data and subsequent checks on the dates identified that
table 1 has 4 valid months in partitions 1,2,3,4 respectively and
table 2 has 4 valid months in partitions 5,7,8,9.
So I tried
sel t1.* from table1 T1 where T1.partition in (1,2,3,4) ; insert into TARGET sel t2.* from table1 T2 where T2.partition in (5,7,8,9) ;
That obtained ERROR 5728
So I tried
insert into TARGET sel t1.* from table1 T1 where T1.partition =1 sample 10 ; insert into TARGET sel t1.* from table1 T1 where T1.partition =2 sample 10 ; insert into TARGET sel t1.* from table1 T1 where T1.partition =3 sample 10 ;insert into TARGET sel t1.* from table1 T1 where T1.partition =4 sample 10 ;insert into TARGET sel t2.* from table2 T2 where T2.partition =5 sample 10 ; etc
When submitted as an MSR it obtains ERROR 5728
although it works when submitted to work in series!
Presumably that proves what I already knew: the data fits in the partition range.
Identical results submitting the SQL via QueryMan and via WinBTEQ.
Ironically, I have tried and failed to replicate the problem with sample data in new test tables.
Where my colleagues have succeeded I suspect that their samples are so small that Tdata may be processing in series even though the SQL is submitted for parallel processing.
This is my first time with partitioned tables and I would really appreciate your ideas.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|