extending MLPPI tables

Database

extending MLPPI tables

I am having trouble extending partitions for the table below, the table is populated and I was able to extend range for level1. however i get error on extending level2, I was able to extend level2 range on an empty table. Can someone help me?

ALTER TABLE COD.SOME_TABLE_S_C12126                   

MODIFY PRIMARY INDEX (mstr_cust_ky)

ADD RANGE#L1 BETWEEN  2014011 AND  2014125 EACH 1;

ALTER TABLE COD.SOME_TABLE_S_C12126                     

MODIFY PRIMARY INDEX (mstr_cust_ky)

ADD RANGE#L2 BETWEEN  201401 AND  201412 EACH 1;

CREATE MULTISET TABLE COD.SOME_TABLE_S_C12126 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      fsc_wk_tm_ky INTEGER NOT NULL,

      mstr_cust_ky DECIMAL(30,0) NOT NULL,

      pdt_scndry_cat_id INTEGER NOT NULL COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ),

      prmry_rep_plsft_assoc_num BIGINT NOT NULL,

      ownr_mstr_rep_plsft_assoc_num BIGINT NOT NULL,

      fcly_mgr_rep_plsft_assoc_num BIGINT NOT NULL,

      oam_plsft_assoc_num BIGINT NOT NULL,

      bdm_plsft_assoc_num BIGINT NOT NULL,

      src_sys_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS '08',

      lang_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS 'ENG   ',

      fsc_prd_tm_ky INTEGER,

      src_orgn_dstr_cd VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('DET','LA','ATL','DAL','NYC','DC','BOS','RCH','PHL','CE-SBA','NAT','HOU','CE-SEA','CHI'),

      src_orgn_div_cd VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('SBA','SEA'),

      mstr_cust_num BIGINT,

      mstr_cust_nmb VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      mstr_cust_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      pdt_prmry_cat_id INTEGER COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ),

      pdt_prmry_cat_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Tech','Print/Promo','Furniture','Facilities','Paper','Office Supplies','MPS','Ink & Toner'),

      pdt_scndry_cat_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Transactional Furn','Promo','Project Furn','Printing Tech','Print','Peripherals','Paper','Office Supplies','MPS','Misc Tech','Jan/San','Ink & Toner','Copy','Computing Tech','Breakroom'),

      pdt_sa_rvn_cat_id INTEGER COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ),

      pdt_sa_rvn_cat_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Tech','Furniture','Promo','Print','Paper','Office Supplies','MPS','Jan/San','Ink & Toner','Copy','Breakroom'),

      prmry_rep_full_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Sales Rep. Not Found','IAM Insufficient Spend ATL','SBA Sales Rep Unassigned','House SNALAIAM','IAM Project_36','House_BD Unassigned','IAM NATION PENDING ASSIGNMENT','IAM Account Closed','Leads HouseAMSantry','Leads HouseAMHall','Deborah Leukus','Leads HouseAMAttias','David Edward Staggs','Megan C. McDaniel','Leads HouseAMPeterson','Sheba Ivie','Leads HouseAMGeurin','Ella Martell','David Blankenship','Michael Ingeri','Teri Marion Campillo','James A Gutierrez II','Eduardo Costas','Jennifer Lewis','Joshua Osborne','Adrian Aranda','Caine Aronowitz','Nicholas Dalacio','Andrea Lazzaro','Teri L Garrow','Deborah Barnes','John Landreth','Yael Hunt','Julie A Copits','Gabriel Hernandez','Charles Blair','Mark E Caswell','Eric Alonso','Kuwan L. Payne','Kevin Foote','Christine Adams','Rosemarie Jones','Nora Flores','Margaret Minke Kooistra','Susan McCrady','Yolanda Woods','Truman Flanders','Renee Dunatchik','Brian Potopowicz','Joaquin Solis','Poingtevy Elizabeth Dolan','Jonathan L Canoy','Kate Barlow','Jason Scarbrough','Fran Dworkin','Mark Thomason','Carol Baugh','Ashtan Rogers','Pedro Jesus Menacho','Karen D Hubbard','Katie Parker','Jared J Culhane','Robin E. Goldberg','Piyush Jain','Thomas Brown II','George Romeo','Kimberly I. Matta-Jackson','Susan A Krachie','Elizabeth Ann Resh','Wayne Matthew Eisenberg','Robb Schneider','Kevin H Duncan','Christ Paul Gekas','Beatriz Guerrero','Judith May Casper','John I McMullen','Lisa Zagrobelny','Joseph Wiest','Gerrit Stukkie','Sally D Beck','Todd M Godbey'),

      ownr_mstr_rep_full_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Sales Rep. Not Found','IAM Insufficient Spend ATL','IAM Project_36','House SNALAIAM','SBA Sales Rep Unassigned','House_BD Unassigned','IAM NATION PENDING ASSIGNMENT','IAM Account Closed','Leads HouseAMSantry','Leads HouseAMHall','Leads HouseAMAttias','David Edward Staggs','Megan C. McDaniel','Deborah Leukus','Leads HouseAMPeterson','Sheba Ivie','Leads HouseAMGeurin','Ella Martell','David Blankenship','Michael Ingeri','Teri Marion Campillo','James A Gutierrez II','Jennifer Lewis','Eduardo Costas','Joshua Osborne','Nicholas Dalacio','Caine Aronowitz','Andrea Lazzaro','Adrian Aranda','Teri L Garrow','Deborah Barnes','Yael Hunt','John Landreth','Gabriel Hernandez','Charles Blair','Eric Alonso','Julie A Copits','Mark E Caswell','Kuwan L. Payne','Truman Flanders','Susan McCrady','Christine Adams','Margaret Minke Kooistra','Nora Flores','Kevin Foote','Fran Dworkin','Yolanda Woods','Renee Dunatchik','Poingtevy Elizabeth Dolan','Rosemarie Jones','Brian Potopowicz','Kate Barlow','Joaquin Solis','Jonathan L Canoy','Jason Scarbrough','Mark Thomason','Carol Baugh','Ashtan Rogers','Pedro Jesus Menacho','Karen D Hubbard','Thomas Brown II','Katie Parker','Piyush Jain','Jared J Culhane','Robin E. Goldberg','Kimberly I. Matta-Jackson','George Romeo','Susan A Krachie','Robb Schneider','Elizabeth Ann Resh','Kevin H Duncan','Beatriz Guerrero','Wayne Matthew Eisenberg','Joseph Wiest','John I McMullen','Lisa Zagrobelny','Judith May Casper','Christ Paul Gekas','Lisa E. Spence','Todd M Godbey','Shandis Steele'),

      fcly_mgr_rep_full_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('SBA Sales Rep Unassigned','Sales Rep. Not Found','Brian R Bartee','Paul J Lumia','SNA Sales Rep Unassigned','House FGSAM_Cooper','Michael J Womack','William Hendrix Whaley','Al Balcaen','Thomas L Anderson','Leo Winkel','John Spencer Cyrus','Paul N Cassimatis','Austin H Eidson','Charles Scott Baldwin','Laura W Whaley','Gregg L Solak','David M McGlinn','Michelle Wolcott','William S Haren','Steven Frederic Sawottke','Denece Ketzler','Robert Joseph Svec','Alan Joseph Klarr','Heather Zaloudik','House FSS-Grant','Christopher Friedrich','John Paul St Pierre','House FSS-Simms','Timothy A. Trautz','Mary C Signorelli','Timothy R Jabaay','Randall L. Johnson','Robert Joseph Kyle','Dan Howard Clausen','Timothy Halloran','Sherron Hughes','John M Kelly','Jeremy P. Warren','Scott Yaskell','Paul Garofano','M Loraine Boyer','Tyler Norris','John W Chupak','Alan G Hanke','Brandy Jean Niedoborski','Chandra Renee Debolt Petrie','Stephanie Heinrichs','Jeff Crow','Jeff Friedgen','Allan C. Nicholson','Gregory Brothers','Alan Curtis Feyen','Kenneth Robert Versino','Stephen Michael McCabe','House FSS-Grahs','Keith Fitzpatrick','James Rignall','David C Paulk','Herbert A Brown','Terry Lynn Hays','Thomas W Curry','John Occhipinti','Benjamin J DeLay','Christopher Steven Sousa','Bernard Joseph Wenner III','Scott Makrauer','Leslie E Vermeulen','House FSS-Finn','Joseph Doles','Alan Jay Kaufman','Franklin B Hoard','Jake W Evans','Michelle Bauer','Matthew R Michael','Darryl W Kennon','Edward Cursey Roberson','Laura A Ducey','Adam Coffman','House FSS-Trahey','Joel A. Ward'),

      oam_full_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Sales Rep. Not Found','IAM Insufficient Spend ATL','SBA Sales Rep Unassigned','House SNALAIAM','IAM Project_36','House_BD Unassigned','IAM NATION PENDING ASSIGNMENT','SNA Sales Rep Unassigned','IAM Account Closed','Leads HouseAMSantry','Leads HouseAMHall','Deborah Leukus','Leads HouseAMAttias','David Edward Staggs','Megan C. McDaniel','Sheba Ivie','Leads HouseAMPeterson','Ella Martell','Leads HouseAMGeurin','Michael Ingeri','Teri Marion Campillo','James A Gutierrez II','Eduardo Costas','Jennifer Lewis','Joshua Osborne','Adrian Aranda','Caine Aronowitz','Nicholas Dalacio','Andrea Lazzaro','Teri L Garrow','Deborah Barnes','John Landreth','Yael Hunt','Julie A Copits','Gabriel Hernandez','Charles Blair','Mark E Caswell','Eric Alonso','Kuwan L. Payne','Kevin Foote','Christine Adams','Rosemarie Jones','Nora Flores','Susan McCrady','Margaret Minke Kooistra','Yolanda Woods','Truman Flanders','Renee Dunatchik','Brian Potopowicz','Joaquin Solis','Poingtevy Elizabeth Dolan','Jonathan L Canoy','Kate Barlow','Jason Scarbrough','Fran Dworkin','Ashtan Rogers','Mark Thomason','Carol Baugh','Pedro Jesus Menacho','Katie Parker','Karen D Hubbard','Jared J Culhane','Robin E. Goldberg','Piyush Jain','Thomas Brown II','George Romeo','Kimberly I. Matta-Jackson','Wayne Matthew Eisenberg','Susan A Krachie','Elizabeth Ann Resh','Robb Schneider','Kevin H Duncan','Christ Paul Gekas','Beatriz Guerrero','Judith May Casper','John I McMullen','Lisa Zagrobelny','Joseph Wiest','Gerrit Stukkie','Sally D Beck','Linda Dennis'),

      bdm_full_nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('Sales Rep. Not Found','SBA Sales Rep Unassigned','Unassigned Legacy Reps','SNA Sales Rep Unassigned','House Doherty','House DeMund','House Mulholland','House Vandevelder','House Roeser (Lindsay)','House McCutcheon','House Fritsch','House Amakasu','House Mageros','House Wood','House Komenda','House Minervino','House Rushford','House Carrelli','House Roberson (Chris)','House Watson','House Nardiello','David A HouseB2BMatthews','House SouthBD Scott Dixon','House Yarbrough','House Paianini','House Rogers','House Sebast','House VOS BD','House Pak','House Austin','House B2B Silverman','House Pemberton SouthBD','House MargolisLAMETRO','House Meany Chicago South','House Kawka','Paula House Sorg','House Erdel','House Cottrell','House Adenariwo','House Reed','House Fortin','House Livingston','House Gordon Heather','House Srivastava','Ben House Snyder','House Stimson','House Steelman','House Compango','House Bramante','House Restivo','House Pandey','Tamara House Larkin','House Sheldon','House Borges','House Young','House Hayett','House Hammel','House Ween','House Dearth','House Esposito','House Wamsley','House Shanahan','House English','House Kiel','House Richelson','House Nemmers (Nate)','House Flores','House Browning','House Plavchak','Prime House DC-Baltimore','House Sorbo','House Rutan','House Busbin','House Wise','House Doyle','House Rohs','House Derry','House Crisci','House Jackson Ladd','House Dix'),

      crt_fsc_yr_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      crt_fsc_qtr_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      crt_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      crt_fsc_wk_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      prior_fsc_yr_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      prior_fsc_qtr_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      prior_01_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_02_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_03_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_04_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_05_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_06_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_07_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_08_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_09_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_10_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_11_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      prior_12_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS 'N',

      comp_prior_01_fsc_yr_ptd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      comp_prior_02_fsc_yr_ptd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      comp_prior_fsc_qtr_qtd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      comp_prior_fsc_prd_ptd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      comp_prior_01_fsc_ytd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      comp_prior_02_fsc_ytd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      comp_prior_01_fsc_yr_qtd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      comp_prior_02_fsc_yr_qtd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      roll_12_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      roll_13_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      roll_03_fsc_prd_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',

      boss_pdt_ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),

      ship_qty DECIMAL(17,3) COMPRESS (0.000 ,6.000 ,5.000 ,4.000 ,3.000 ,2.000 ,1.000 ),

      extd_prc_amt DECIMAL(20,6) COMPRESS 0.000000 ,

      extd_avg_cst_amt DECIMAL(20,6) COMPRESS 0.000000 ,

      extd_cust_rbt_amt DECIMAL(20,6) COMPRESS 0.000000 ,

      extd_cust_extrn_slscmn_rbt_amt DECIMAL(20,6) COMPRESS 0.000000 ,

      extd_pdt_vn_rbt_amt DECIMAL(20,6) COMPRESS 0.000000 ,

      extd_bid_cst_diff_amt DECIMAL(20,6) COMPRESS 0.000000 ,

      ord_cnt INTEGER NOT NULL COMPRESS (0 ,1 ,2 ),

      ord_ln_cnt INTEGER NOT NULL COMPRESS (0 ,1 ,2 ),

      row_insrt_tms TIMESTAMP(6) WITH TIME ZONE NOT NULL,

      row_updt_tms TIMESTAMP(6) WITH TIME ZONE NOT NULL,

      row_stat_cd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'NEW   ' COMPRESS 'NEW   ',

CONSTRAINT FWCUSTSREPPDTCATSC1_PK PRIMARY KEY ( fsc_wk_tm_ky ,

mstr_cust_ky ,pdt_scndry_cat_id ,prmry_rep_plsft_assoc_num ,ownr_mstr_rep_plsft_assoc_num ,

fcly_mgr_rep_plsft_assoc_num ,oam_plsft_assoc_num ,bdm_plsft_assoc_num ))

PRIMARY INDEX FWCUSTSREPPDTCATSC1_NUPI01 ( mstr_cust_ky )

PARTITION BY ( RANGE_N(fsc_wk_tm_ky  BETWEEN 2011011  AND 2011124  EACH 1 ,

2012011  AND 2012125  EACH 1 ,

2013011  AND 2013124  EACH 1 ,

2014011  AND 2014125  EACH 1 ,

 NO RANGE, UNKNOWN),RANGE_N(fsc_prd_tm_ky  BETWEEN 201101  AND 201112  EACH 1 ,

201201  AND 201212  EACH 1 ,

201301  AND 201312  EACH 1 ,

201401  AND 201412  EACH 1 ,

 NO RANGE, UNKNOWN) );

8 REPLIES
Enthusiast

Re: extending MLPPI tables

AFAIK, it is not possible since it is populated table. However, you can think of or try to do thus:

Note: to be 100% sure, you can take backup of entire table, because I dont know the version of TD you work on.

CREATE TABLE old_table1 ( /*create backup only for the one you want*/

key1 INTEGER NOT NULL,

....)

UNIQUE PRIMARY INDEX (key1);

ALTER TABLE table1 /*your table */

MODIFY

DROP RANGE#L2 BETWEEN DATE 'xxxxx'

AND DATE 'yyyyy'

ADD RANGE#L2 BETWEEN DATE 201401 

AND DATE 201412 

EACH 1....

WITH INSERT INTO old_table1;

Cheers,

Raja

Enthusiast

Re: extending MLPPI tables

HI,

You can use alter table statement to alter partitions on an empty table in any way, but there are certian limitations when the table is populated.

When the table is populated, You can add or drop ranges only at end. Can you please sepeicfy the error you are getting?

Further, as per you given alter table statement:

ALTER TABLE COD.SOME_TABLE_S_C12126                     

MODIFY PRIMARY INDEX (mstr_cust_ky)

ADD RANGE#L2 BETWEEN  201401 AND  201412 EACH 1;

This range has already been specified in create table statement. 

Please paste the error you are getting so that we can resolve it.

Khurram
Junior Contributor

Re: extending MLPPI tables

Before TD14 there's a specific rule for changing the definition of sub-partitions on a populated table:

You can only add a partition when you also drop a partition, i.e. the number of partitions must not change. 

In TD!4 there's a new ADD option to keep some additional partitions available.

I usually recommend to define as far into the future as possible/neccessary, so you never have to ADD/DROP again :-)

Enthusiast

Re: extending MLPPI tables

Will defining empty partitions for future use have any impact on performance?

Enthusiast

Re: extending MLPPI tables

Harpreet,

Future empty partitions will not impact performance in TD 14. all you can do is to keep the stats updated. So that optimizer knows about the empty partitions :)

Khurram
Enthusiast

Re: extending MLPPI tables

Hi , 

I have a table which is partitioned on BUS_DT with a Range_N partition with data (200 million) . I want to include another  RANGE_N partition on CO_ID .

Can I do it through Alter Statement. I donot want to drop the table as dropping and loading wil take a lot of time. 

Please provide me the query/alter stmt.

Thansk,

Junior Contributor

Re: extending MLPPI tables

You can't use ALTER to add a new partitioning column, you must create a new table.

Teradata Employee

Re: extending MLPPI tables

But it is not neccesary to drop and reload the table. Create the new table definition with the new partitioning, then use INSERT SELECT to move the data from the current table to the new one. INSERT SELECT will be much better than reloading.