ALTER_PPI

Database

ALTER_PPI

While going through Partition primary index I saw to add and drop the range partitions.

My question is

Is not it possible to modify the PPI created by case partition?????

If yes kindly give the syntax

10 REPLIES
Enthusiast

Re: ALTER_PPI

No. I don't think we have option to modify case_n partition.

Enthusiast

Re: ALTER_PPI

the syntax to alter the PPI is as follows:

create multiset table dbname.tablename, no fallback, no before journal, no after journal, checksum = default

(column1 datatype

,column2 datatype

...

,columnX datatype

)

primary index (column1)

partition by ...

Enthusiast

Re: ALTER_PPI

once you recreate the table, you need to insert/select and collect the appropriate statistics

Enthusiast

Re: ALTER_PPI

I see Create table syntax above.  I think you mispasted the content. Can you please correct if you have alter ppi of case_n partition.

I tried similar as range n alter statement  but not working.

alter table <db>.<table> modify primary index

Add RANGE BETWEEN DATE '2014-01-02' AND DATE '2014-12-31' EACH INTERVAL '7' DAY

Teradata Employee

Re: ALTER_PPI

There is no Alter PPI, you have to create a seperate table with new PPI and insert data into that.

HTH!

Re: ALTER_PPI

The syntax for PPI is

CREATE SET TABLE demo_contract ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      O_ORDERKEY INTEGER NOT NULL,

      O_CUSTKEY INTEGER NOT NULL,

      O_ORDERSTATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      O_TOTALPRICE DECIMAL(15,2),

      O_ORDERDATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      O_ORDERPRIORITY VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT

 NULL,

      O_CLERK VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      O_SHIPPRIORITY INTEGER NOT NULL,

      O_COMMENT VARCHAR(79) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)

PRIMARY INDEX ( O_ORDERKEY )

partition by case_n(O_TOTALPRICE<5000,O_TOTALPRICE<100000,O_TOTALPRICE<200000,O_TOTALPRICE<300000,

O_TOTALPRICE<403000,no case,Unknown);

------------------------------------------------------

CREATE SET TABLE demo_contract ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      O_ORDERKEY INTEGER NOT NULL,

      O_CUSTKEY INTEGER NOT NULL,

      O_ORDERSTATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      O_TOTALPRICE DECIMAL(15,2),

      O_ORDERDATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      O_ORDERPRIORITY VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT

 NULL,

      O_CLERK VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      O_SHIPPRIORITY INTEGER NOT NULL,

      O_COMMENT VARCHAR(79) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)

PRIMARY INDEX ( O_ORDERKEY )

partition by range_n(O_ORDERDATE between date '1992-01-02' and date '1998-08-01' each interval

 '1' year,no range, unknown);

----------------------------------------------

and for alter syntax is

---------------------------------------

alter table demo_contract modify primary index

drop range between date '1992-01-01' and date '1998-08-02';

--------------------------------------

alter table demo_contract modify primary index

drop range where partition in(1,2);

Enthusiast

Re: ALTER_PPI

yes. So it is clear that for Range partition only alter table syntax would work. for case parition it is only the workaround of Create new and insert/select.

--Thank You.

Sri

Enthusiast

Re: ALTER_PPI

Can I add a new range partition on a new column in an already created and populated table. 

Enthusiast

Re: ALTER_PPI

No, you are trying to modify the PPI of a populated table. Rows are already distributed in this case and you can not redistribute.