-
Hello. I have quite a large table: it contains approx 8 million rows for each of the time/date key.
I want to make it into a partitioned table using the time/date key values.
Question is: is it possible to partition this existing table e.g. using ALTER TABLE or do I have to create a table from scratch including a partition option?
-
A non-partitioned table can not be "converted" to a partitioned table. You will have to re-create your table with the appropriate partitioning clause.
-
Thought as much... more work for me then.
Cheers for the help though!
-
you can do (with EMP table as example)
Code:
create table partemp(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
partition by range(empno)
(partition partemp6 values less than (8000))
/
alter table partemp exchange partition partemp6 with table emp
/
alter table partemp split partition partemp6
at (7000)
into (partition partemp1,
partition partemp6)
/
alter table partemp split partition partemp6
at (7200)
into (partition partemp2,
partition partemp6)
/
alter table partemp split partition partemp6
at (7400)
into (partition partemp3,
partition partemp6)
/
alter table partemp split partition partemp6
at (7600)
into (partition partemp4,
partition partemp6)
/
alter table partemp split partition partemp6
at (7800)
into (partition partemp5,
partition partemp6)
/
this should be pretty fast