All Forums Database
samapika.t 4 posts Joined 12/13
17 Feb 2014
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

sgarlapa 88 posts Joined 03/13
17 Feb 2014

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

VandeBergB 182 posts Joined 09/06
17 Feb 2014

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 ...

Some drink from the fountain of knowledge, others just gargle.

VandeBergB 182 posts Joined 09/06
17 Feb 2014

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

Some drink from the fountain of knowledge, others just gargle.

sgarlapa 88 posts Joined 03/13
17 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
17 Feb 2014

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

-- If you are stuck at something .... consider it an opportunity to think anew.

samapika.t 4 posts Joined 12/13
17 Feb 2014

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);
 
 

sgarlapa 88 posts Joined 03/13
18 Feb 2014

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

drmkd17 54 posts Joined 10/12
13 Jul 2016

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

RonaldBrayan 7 posts Joined 11/15
14 Jul 2016

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

M.Saeed Khurram 544 posts Joined 09/12
14 Jul 2016

Hi Katei,
Just create a new table with new partition layout, and do insert select into new table. It will not take much time.
Khurram

Khurram

You must sign in to leave a comment.