列表分区如下:
List Partitioning Example
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii') TABLESPACE part1,
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida') TABLESPACE part2,
PARTITION sales_central VALUES('Texas', 'Illinois') TABLESPACE part3
PARTITION sales_other VALUES(DEFAULT) TABLESPACE part4
);范围分区如下:
CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE part1,
PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE part2,
PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE part3);
List Partitioning Example
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii') TABLESPACE part1,
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida') TABLESPACE part2,
PARTITION sales_central VALUES('Texas', 'Illinois') TABLESPACE part3
PARTITION sales_other VALUES(DEFAULT) TABLESPACE part4
);范围分区如下:
CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11) TABLESPACE part1,
PARTITION employees_part2 VALUES LESS THAN (21) TABLESPACE part2,
PARTITION employees_part3 VALUES LESS THAN (31) TABLESPACE part3);
create table sales
(
id numeric(10,0),
zone_id numeric(10,0),
.....
)
partition by list(zone_id)
(
partition zone_id_1 values in (id1,id2,id3,...),
partition zone_id_2 values in (id4,id5,.......),
,,,,,,,,,,,
)
nologging;