Archives of the TeradataForum

Message Posted: Wed, 18 Jun 2008 @ 16:51:02 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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 nate at the end of the disucsison.

As always, thanks for your feedback. Bill .

Environment:

Running v12.0.1.4

Table Demographics:

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 xx.partitioning_attr between 8249 and 7679
        or xx partitioning_attr between 7672 and 7702 )

     WHEN NOT MATCHED THEN
     Insert (
       aa.attr01
     ,aa.attr02
         ...
     ,aa.attr_last
     )
     ;

Thanks,

Bill Gregg
Teradata Team
Thomson-Reuters Healthcare

	<Prev	Next>		<<First	<Prev	Next>	Last>>