 |
 |
Archives of the TeradataForum
Message Posted: Fri, 06 Jun 2003 @ 11:02:51 GMT
| 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
| |
