|
Archives of the TeradataForumMessage Posted: Thu, 05 Jun 2003 @ 13:41:06 GMT
Hi guys, perhaps I've misunderstood some the features of PPI's, but I was under the impression that adding new rows to a populated table will act like adding rows to an empty table, should the new rows not already exist in a partition, i.e. Transient logging would not be necessary. I have this table definition : 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, 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_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 ('Y','N')) PRIMARY INDEX OPPCON_UPI ( CON_SRKY_ID ) PARTITION BY RANGE_N (DTE_YRWK_NR_KPN BETWEEN 200301 AND 200552 EACH 1 ) which will hold our Key Performance Indicators for one of our main entities. The intention of the partitioning scheme is to have one partition per week. I added ~700K rows into a partition for DTE_YRWK_NR_KPN = 200315, which took 8 seconds. I then added ~ 620K rows into another partition (DTE_YRWK_NR_KPN = 200316) which took 35 seconds. I then added again the same 620K rows (i.e. DTE_YRWK_NR_KPN = 200316) which also took 35 seconds. What I would not have expected was the time taken on the second of the three inserts (I was expecting something like 6 seconds). It might be worth pointing out that I was adding the data from a similartable (exactly the same Primary Index) but not Partitioned. The Insert explain I got was this
No mention of partitions. In fact, data has been REDISTRIBUTED, which I didn't expect or understand. Also, I am running in ANSI mode. Certainly if I read the table on week 15, thus Select * from TESTCONS_T.CONKPI_NEW where DTE_YRWK_NR_KPN = 200315 I get the expected partition elimination :
Also If I delete from only 1 partition, I also get an explain that performs partition elimination,
but even this took 24 seconds to remove 1.2 odd million rows (i.e. twice the 620k row inserts). So ... have I partly misunderstood PPI's?! Dave Clough
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||