How to create partitioned table based on one Integer column (each value = 1 partition) in Oracle 12c?

Question

There is a non-partitioned table in Oracle 12c, call it 't1' with 10M records that has 3 columns (a,b,c).

Column 'a' has 100 distinct integer values.

How to create a second table 't2' that has all the values from 't1' but is partitioned based on column 'a' so that each partition correspond to one distinct value in column 'a'? (there should be 100 partitions created).

Thanks!

Answer 1

Create a automatic list partition while creating table t2 and later insert records from table t1 which will create a new partition for each distinct value in the mentioned column 'a'.

Note : While creating automatic list partition you need to specify atleast one partition.

eg :- CREATE TABLE t2 ( a number,b varchar2(200),c varchar2(200) ) PARTITION BY LIST (a) AUTOMATIC ( PARTITION P_10 VALUES (10) );

insert into t2 select * from t1;

This insert will create partitions automatically for each distinct value.