Archives of the TeradataForum
Message Posted: Wed, 18 Jun 2008 @ 19:27:56 GMT
Victor - thanks, the aa should be consistent in clause
> and (aa.partitioning_attr between 8736 and 8766 > or xx.partitioning_attr between 8249 and 7679 > or xx partitioning_attr between 7672 and 7702 ) /** change xx to aa above **/
There are no updates; only inserts are attempted. So the WHEN Matched logic is removed. I think that the Optimzier applies the ON logic to the Update track rather than Update and Insert tracks. I'm OK with that, I expected that the same set considered for update would be used for insert; it doesn't seem that way.
I'll post a corrected copy of the pseudocode below:
Query A runs as expected, query B is at issue (?? maybe not??).
The queries use MERGE logic and perform inserts only (no updates).
1) In Query B (below) partition specific logic is ignored; 11M rows instead of ~800K rows are inserted to the Target
It seems that the ON logic only applies to UPDATEs?? Since the query does inserts, everything (in this case the full table) is inserted.
ANY THOUGHTS ON THIS RATIONALE?
2) Inserts to partitions (via one piece of SQL) to partitions 1, 20, and 36 run faster than an insert to a single partition 36 (only).
Single user; queries were run consecutively, each had a chance to run first. The Target partitions were populated.
I NEED SOME HELP EXPLAINING THIS ONE!
Since I am asking 2 distinct questions, I'll summarize the results and consolidate into one note at the end of the disucsison.
As always, thanks for your feedback. Bill .
The tables have 36 partitions each. The tables have 11M and 15M rows respectively. The partitions have a fairly uniform distribution on both tables (on the 15m row table, partition 1 has about 200K rows, other partitions range from 250 - 550K rows; the 11M row table reflects a similar distribution.) This is identical SL-PPI on both tables.
/*** query A, derived table approach ***/ database wlg35507_temp ; MERGE INTO wlg35507_temp.table_mvc_idx bb USING (select * from wlg35507_temp.table_tst xx where xx.partitioning_attr between 8736 and 8766 /*** or xx.partitioning_attr between 8249 and 8279 or xx partitioning_attr between 7672 and 7702 ***/ ') aa ON aa.PI_attr = bb.PI_attr and aa.partitioning_attr = bb.partitioning_attr WHEN NOT MATCHED THEN Insert ( aa.attr01 ,aa.attr02 ... ,aa.attr_last ) ;
Query B inserts all the rows of the source into the target. (partition specific logic is ignored. The explain reflects this.)
/*** Query B ***/ database wlg35507_temp ; MERGE INTO wlg35507_temp.table_mvc_idx bb USING wlg35507_temp.table_tst aa ON aa.PI_attr = bb.PI_attr and aa.partitioning_attr = bb.partitioning_attr and (aa.partitioning_attr between 8736 and 8766 or aa.partitioning_attr between 8249 and 7679 or aa partitioning_attr between 7672 and 7702 ) WHEN NOT MATCHED THEN Insert ( aa.attr01 ,aa.attr02 ... ,aa.attr_last ) ;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|