Oracle 11G可以使用在线重定义进行非分区表转换为分区表（Online Redefinition），在Oracle 12C的新特性中，可在线将非分区表转换为分区表，同样，LightDB实现了此特性：创建测试表emp2，并且在emp2上创建索引
lightdb@test=# create table emp2 as select * from emp; CREATE TABLE lightdb@test=# create index i_emp2_name on emp2(ename); CREATE INDEX lightdb@test=# create index i_emp2_empno on emp2(empno); CREATE INDEX
测试语句执行语句如下：
lightdb@test=# alter table emp2 modify
lightdb@test-# partition by range (HIREDATE)
lightdb@test-# (
lightdb@test(# partition part_1980 values less than (to_date('1980','yyyy')),
lightdb@test(# partition part_1981 values less than (to_date('1981','yyyy')),
lightdb@test(# partition part_1982 values less than (to_date('1982','yyyy')),
lightdb@test(# partition part_1983 values less than (to_date('1983','yyyy')),
lightdb@test(# partition part_1984 values less than (to_date('1984','yyyy')),
lightdb@test(# partition part_1985 values less than (to_date('1985','yyyy')),
lightdb@test(# partition part_1986 values less than (to_date('1986','yyyy')),
lightdb@test(# partition part_1987 values less than (to_date('1987','yyyy')),
lightdb@test(# partition part_1988 values less than (to_date('1988','yyyy'))
lightdb@test(# ) online
lightdb@test-# update indexes
lightdb@test-# ( i_emp2_name GLOBAL,
lightdb@test(# i_emp2_empno local
lightdb@test(# );
ALTER TABLE
然后我们查看转换后的表DDL语句
lightdb@test=# \d+ emp2
Partitioned table "public.emp2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+---------+--------------+-------------
empno | numeric(4,0) | | | | main | |
ename | varchar2(20) | | | | plain | |
job | varchar2(9) | | | | plain | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
dname | varchar2(100) | | | | plain | |
Partition key: RANGE (hiredate)
Indexes:
"i_emp2_empno" btree (empno)
"i_emp2_name" btree (ename)
Partitions: part_1980 FOR VALUES FROM (MINVALUE) TO ('1980-01-01'),
part_1981 FOR VALUES FROM ('1980-01-01') TO ('1981-01-01'),
part_1982 FOR VALUES FROM ('1981-01-01') TO ('1982-01-01'),
part_1983 FOR VALUES FROM ('1982-01-01') TO ('1983-01-01'),
part_1984 FOR VALUES FROM ('1983-01-01') TO ('1984-01-01'),
part_1985 FOR VALUES FROM ('1984-01-01') TO ('1985-01-01'),
part_1986 FOR VALUES FROM ('1985-01-01') TO ('1986-01-01'),
part_1987 FOR VALUES FROM ('1986-01-01') TO ('1987-01-01'),
part_1988 FOR VALUES FROM ('1987-01-01') TO ('1988-01-01')
