CREATE TABLE sales
(
AAAAA NUMBER,
BBBBB char
...
)
PARTITION BY RANGE (substr(AAAAA,4,2))
(
PARTITION a1 VALUES LESS THAN (10)
PARTITION a2 VALUES LESS THAN (20)
...
PARTITION a9 VALUES LESS THAN (90)
--values less than (maxvalue)
);
我想实现按AAAAA项目的后两位来分区,可PARTITION BY RANGE (substr(AAAAA,4,2))
中好像不认substr()函数请问该如何实现?
(
AAAAA NUMBER,
BBBBB char
...
)
PARTITION BY RANGE (substr(AAAAA,4,2))
(
PARTITION a1 VALUES LESS THAN (10)
PARTITION a2 VALUES LESS THAN (20)
...
PARTITION a9 VALUES LESS THAN (90)
--values less than (maxvalue)
);
我想实现按AAAAA项目的后两位来分区,可PARTITION BY RANGE (substr(AAAAA,4,2))
中好像不认substr()函数请问该如何实现?
PARTITION a1 VALUES LESS THAN (10)
PARTITION a2 VALUES LESS THAN (20)
PARTITION a3 VALUES LESS THAN (30)
...
PARTITION a9 VALUES LESS THAN (90)
PARTITION a10 VALUES LESS THAN (100)
PARTITION a1 VALUES LESS THAN (110)
PARTITION a2 VALUES LESS THAN (120)
PARTITION a3 VALUES LESS THAN (130)
...
呵呵循环他1000遍
2,用现有数据库,增加一个标识列,多点儿冗余问题也不大
CREATE TABLE sales
(
AAAAA NUMBER,
BBBBB char ,
CC varchar2(2), --substr(AAAAA,4,2)
...
)
PARTITION BY RANGE (CC)
(
PARTITION a1 VALUES LESS THAN (10)
PARTITION a2 VALUES LESS THAN (20)
...
PARTITION a9 VALUES LESS THAN (90)
--values less than (maxvalue)
);