Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 06 Jun 2003 @ 11:02:51 GMT


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


Subj:   Re: PPI's with INSERT
 
From:   David Clough

Thanks for your initial comments, Carrie. You'll see here that they have the same Primary Index (the Integer column, CON_SRKY_ID).

The Explain plan certainly leaves me a bit confused. I could accept some sort of local sort - in order to get the new rows into Partition sequence - but a complete all Amps redistribution was a total surprise. (I still feel as though I've done something stupid and the error is staring me in the face).

It does seem a shame that Inserting into an empty partition should invoke Transient Logging because it means, for a Time Based Partition scheme (which this is), the process of adding a partition at a time (which will be the case in my situation) would have been lighting fast. Perhaps there are issues involved that I haven't considered that make such a possibility extremely difficult.

CREATE MULTISET TABLE TESTCONS_T.CONKPI ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     DATABLOCKSIZE = 130560 BYTES
     (
      CON_SRKY_ID INTEGER NOT NULL,
      DTE_YRWK_NR_KPN INTEGER NOT NULL,
      CON_DELIVERY_DT DATE FORMAT 'yyyy-mm-dd',
      CON_DELIVERY_TM TIME(0),
      CON_INCMPL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_NEG_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_UNDEL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_PTPND_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_DELIV_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'Y',
      CON_NOSTAT_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'Y',
      CON_DAMAGE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_UNSEND_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_MCODED_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_MRTED_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_HCUST_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_LHAUL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_OPP_PARTDEL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_OTDEL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'Y',
      CON_OCTRL_FLRE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_DCTRL_FLRE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_CCTRL_FLRE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_IR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'Y',
      CON_HHMP_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_HCMP_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_ONTIME_POD_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'Y',
      CON_LATE1_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_LATE2_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_EARLY_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_MANNRCVD_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_NO_TR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'N',
      CON_NONEG_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS 'Y')
PRIMARY INDEX OPPCON_UPI ( CON_SRKY_ID );
/**/
CREATE MULTISET TABLE TESTCONS_T.CONKPI_NEW ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     DATABLOCKSIZE = 130560 BYTES
     (
      CON_SRKY_ID INTEGER NOT NULL,
      CON_TYPE_CD CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL
COMPRESS 'WI',
      DTE_YRWK_NR_KPN INTEGER NOT NULL,
      CON_DELIVERY_DT DATE FORMAT 'yyyy-mm-dd',
      CON_DELIVERY_TM TIME(0),
      CON_INCMPL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_NEG_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_UNDEL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_PTPND_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_DELIV_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_NOSTAT_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_DAMAGE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_UNSEND_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_MCODED_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_MRTED_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_HCUST_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_LHAUL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_OTDEL_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_OCTRL_FLRE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_DCTRL_FLRE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_CCTRL_FLRE_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_IR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_HHMP_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_HCMP_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_ONTIME_POD_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_LATE1_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_LATE2_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_EARLY_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_MANNRCVD_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_NO_TR_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'),
      CON_NONEG_IN CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL COMPRESS ('N','Y'))
PRIMARY INDEX OPPCON_UPI ( CON_SRKY_ID )
PARTITION BY RANGE_N(DTE_YRWK_NR_KPN  BETWEEN
200301  AND 200552  EACH 1 );
/**/
INSERT INTO TESTCONS_T.CONKPI_NEW SELECT
CON_SRKY_ID
,'WI'
,DTE_YRWK_NR_KPN
,CON_DELIVERY_DT
,CON_DELIVERY_TM
,CON_INCMPL_IN
,CON_NEG_IN
,CON_UNDEL_IN
,CON_PTPND_IN
,CON_DELIV_IN
,CON_NOSTAT_IN
,CON_DAMAGE_IN
,CON_UNSEND_IN
,CON_MCODED_IN
,CON_MRTED_IN
,CON_HCUST_IN
,CON_LHAUL_IN
,CON_OTDEL_IN
,CON_OCTRL_FLRE_IN
,CON_DCTRL_FLRE_IN
,CON_CCTRL_FLRE_IN
,CON_IR_IN
,CON_HHMP_IN
,CON_HCMP_IN
,CON_ONTIME_POD_IN
,CON_LATE1_IN
,CON_LATE2_IN
,CON_EARLY_IN
,CON_MANNRCVD_IN
,CON_NO_TR_IN
,CON_NONEG_IN
FROM TESTCONS_T.CONKPI WHERE DTE_YRWK_NR_KPN = 200316;
/**/
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct TESTCONS_T."pseudo table" for write on a RowHash to prevent global deadlock for TESTCONS_T.CONKPI_NEW.  
  2)Next, we lock a distinct TESTCONS_T."pseudo table" for read on a RowHash to prevent global deadlock for TESTCONS_T.CONKPI.  
  3)We lock TESTCONS_T.CONKPI_NEW for write, and we lock TESTCONS_T.CONKPI for read.  
  4)We do an all-AMPs RETRIEVE step from TESTCONS_T.CONKPI by way of an all-rows scan with a condition of ("TESTCONS_T.CONKPI.DTE_YRWK_NR_KPN = 200316") into Spool 1 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to partition Spool 1 by rowkey. The size of Spool 1 is estimated with high confidence to be 620,622 rows. The estimated time for this step is 11.12 seconds.  
  5)We do an all-AMPs MERGE into TESTCONS_T.CONKPI_NEW from Spool 1 (Last Use).  
  6)We spoil the parser's dictionary cache for the table.  
  7)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->No rows are returned to the user as the result of statement 1.  


Dave Clough
Database Designer
Database Design Group



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016