Archives of the TeradataForum

Message Posted: Thu, 24 Aug 2006 @ 17:12:05 GMT

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

Hi again guys,

Hopefully this clarifies what we used to do and what I'd proposed :

Firstly, the (part) definition of the target and work table:

     CREATE MULTISET TABLE DEVCMNCUST_T.CUSTOMER ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           CUS_ID DECIMAL(10,0) NOT NULL,
           CUS_DELETE_CD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
           CUS_UPDT_TD TIMESTAMP(0) NOT NULL,
           CUS_UPDT_USER_ID CHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
           CUS_SOFTLOCK_TS TIMESTAMP(0) NOT NULL,
           COU_ISO_ID CHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
.
.
.
           CUS_PRIMARY_SIC_CD CHAR(4) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL)
     UNIQUE PRIMARY INDEX CUSTOMER_UPI ( CUS_ID );
     /**/
     CREATE MULTISET TABLE CUSTOMER_W02 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          DATABLOCKSIZE = 130560 BYTES, CHECKSUM = DEFAULT
          (
           CUS_ID DECIMAL(10,0) NOT NULL,
           CUS_DELETE_CD CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
           CUS_UPDT_TD TIMESTAMP(0) NOT NULL,
           CUS_UPDT_USER_ID CHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
           CUS_SOFTLOCK_TS TIMESTAMP(0),
           COU_ISO_ID CHAR(2) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
     .
     .
     .
           CUS_PRIMARY_SIC_CD CHAR(4) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL)
     UNIQUE PRIMARY INDEX CUSTOMER_UPI ( CUS_ID );

As you'll see, they have the same PI.

     /* OLD PROCESS :

     1.a Delete entries from Target based on all rows in Work */

     DEL FROM DEVCMNCUST_T.CUSTOMER A2
     WHERE EXISTS
     (SELECT 1 FROM CUSTOMER_W02 B2
           WHERE A2.CUS_ID= B2.CUS_ID) ;

     (Deletes 5000 rows)

     1.b subsequent Insert : contains 'updates' and new rows */

        INS INTO DEVCMNCUST_T.CUSTOMER SEL * FROM CUSTOMER_W02;

     (Inserts 10000 rows)

     /* NEW PROCESS */

     /* 2.a Update followed by subsequent Insert */

        UPDATE DEVCMNCUST_T.CUSTOMER TGT1
     SET
     CUS_DELETE_CD=CUSTOMER_W02.CUS_DELETE_CD
     ,CUS_UPDT_TD=CUSTOMER_W02.CUS_UPDT_TD
     ,CUS_UPDT_USER_ID=CUSTOMER_W02.CUS_UPDT_USER_ID
     ,CUS_SOFTLOCK_TS=CUSTOMER_W02.CUS_SOFTLOCK_TS
     ,COU_ISO_ID=CUSTOMER_W02.COU_ISO_ID
     .
     60 other columns
     .
     ,CUS_PRIMARY_SIC_CD=CUSTOMER_W02.CUS_PRIMARY_SIC_CD
     WHERE
     TGT1.CUS_ID= CUSTOMER_W02.CUS_ID
      ;

As you'll see, I'm not updating the Primary index column (although I have to confess I did trip up on that one in the early stages).

     (Updates 5000 rows)

     /* 2b. Insert new rows */

        INSERT INTO DEVCMNCUST_T.CUSTOMER
     (CUS_ID
     ,CUS_DELETE_CD
     ,CUS_UPDT_TD
     ,CUS_UPDT_USER_ID
     ,CUS_SOFTLOCK_TS
     .
     .
     .
     ,CUS_PRIMARY_SIC_CD
     )
     SELECT
     CUS_ID
     ,CUS_DELETE_CD
     ,CUS_UPDT_TD
     ,CUS_UPDT_USER_ID
     ,CUS_SOFTLOCK_TS
     ,COU_ISO_ID
     .
     .
     .
     ,CUS_PRIMARY_SIC_CD
      FROM CUSTOMER_W02 WRK2
     WHERE NOT EXISTS
     (SELECT 1 FROM DEVCMNCUST_T.CUSTOMER TGT1
           WHERE TGT1.CUS_ID= WRK2.CUS_ID
     ) ;

     (Inserts 5000 rows)

I'm hoping I've done something which is considered to be a complete 'no, no', but I don't think so.

Yes, the figures I gave are averages (but not on many runs) and the box was pretty idle at the time.

It's true that I'm not running it as a Multi-statement Request. Would that make a big difference ? (I could try it).

Hope that helps your ponderings.

Gratefully yours,

Dave Clough
Database Designer
Express ICS

www.tnt.com

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