Archives of the TeradataForum

Message Posted: Wed, 18 Jun 2008 @ 19:27:56 GMT

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

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 .

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 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
     )
     ;

Rgrds,

Bill Gregg

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