create table ALL_SALES
(
YEAR INTEGER not null,
MONTH INTEGER not null,
PRD_TYPE_ID INTEGER not null,
EMP_ID INTEGER not null,
AMOUNT NUMBER(8,2)
);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 1, 1, 21, 10034.84);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 2, 1, 21, 15144.65);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 3, 1, 21, 20137.83);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 4, 1, 21, 25057.45);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 5, 1, 21, 17214.56);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 6, 1, 21, 15564.64);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 7, 1, 21, 12654.84);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 8, 1, 21, 17434.82);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 9, 1, 21, 19854.57);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 10, 1, 21, 21754.19);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 11, 1, 21, 13029.73);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 12, 1, 21, 10034.84);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 1, 2, 21, 1034.84);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 2, 2, 21, 1544.65);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 3, 2, 21, 2037.83);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 4, 2, 21, 2557.45);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 5, 2, 21, 1714.56);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 6, 2, 21, 1564.64);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 7, 2, 21, 1264.84);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 8, 2, 21, 1734.82);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 9, 2, 21, 1854.57);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 10, 2, 21, 2754.19);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 11, 2, 21, 1329.73);
insert into ALL_SALES (YEAR, MONTH, PRD_TYPE_ID, EMP_ID, AMOUNT)
values (2003, 12, 2, 21, 1034.84);
当ALL_SALES表的数据量有千万条时,建分区表里用以下命令,执行很慢,有没有快点的办法?
CREATE TABLE sales_range
(year,
month,
prd_type_id ,
emp_id,
amount)
PARTITION BY RANGE(month)
(
PARTITION sales_1 VALUES LESS THAN(2),
PARTITION sales_2 VALUES LESS THAN(3),
PARTITION sales_3 VALUES LESS THAN(4),
PARTITION sales_4 VALUES LESS THAN(5),
PARTITION sales_5 VALUES LESS THAN(6),
PARTITION sales_6 VALUES LESS THAN(7),
PARTITION sales_7 VALUES LESS THAN(8),
PARTITION sales_8 VALUES LESS THAN(9),
PARTITION sales_9 VALUES LESS THAN(10),
PARTITION sales_10 VALUES LESS THAN(11),
PARTITION sales_11 VALUES LESS THAN(12),
PARTITION sales_12 VALUES LESS THAN(MAXVALUE)
) AS SELECT * FROM all_sales;