管理表（七）--分区表及簇表的建立

 

1、分区表：（>2G)对大表进行优化   （Range Partitioning，List PartitioningHash Partitioning，Composite Partitioning）

——按range 建立分区表

1  create table sales_range

2  (salesman_id number(5),

3   salesman_name varchar2(30),

4   sales_amount number(10),

5   sales_date   date)

6  partition by range(sales_date)——指定分区的指标

7  (partition p1 values less than(TO_DATE('04/01/2011','MM/DD/YYYY')) tablespace lx01,

8  partition p2 values less than(TO_DATE('07/01/2011','MM/DD/YYYY')) tablespace lx02,

9  partition p3 values less than(TO_DATE('10/01/2011','MM/DD/YYYY')) tablespace lx03,

10  partition p4 values less than(TO_DATE('01/01/2012','MM/DD/YYYY')) tablespace lx04)

11* enable row movement

SQL> /

Table created.

SQL> insert into  sales_range  values ( 1001,'tom',1000,'2011-02-01');

1 row created.

SQL> insert into  sales_range  values ( 1002,'jerry',1000,'2011-05-01');

1 row created.

SQL> insert into  sales_range  values ( 1003,'rose',1000,'2011-08-01');

1 row created.

SQL> insert into  sales_range  values ( 1004,'john',1000,'2011-01-01');

1 row created.

SQL> insert into  sales_range  values ( 1005,'john',1000,'2011-11-01');

1 row created.

SQL> select * from sales_range partition(p1);

SALESMAN_ID SALESMAN_NAME   SALES_AMOUNT SALES_DATE

----------- --------------- ------------ -------------------

1001 tom                     1000 2011-02-01 00:00:00

1004 john                    1000 2011-01-01 00:00:00

SQL> select * from sales_range partition(p2);

SALESMAN_ID SALESMAN_NAME   SALES_AMOUNT SALES_DATE

----------- --------------- ------------ -------------------

1002 jerry                   1000 2011-05-01 00:00:00

SQL> select * from sales_range partition(p3);

SALESMAN_ID SALESMAN_NAME   SALES_AMOUNT SALES_DATE

----------- --------------- ------------ -------------------

1003 rose                    1000 2011-08-01 00:00:00

SQL> select * from sales_range partition(p4);

SALESMAN_ID SALESMAN_NAME   SALES_AMOUNT SALES_DATE

----------- --------------- ------------ -------------------

1005 john                    1000 2011-11-01 00:00:00

SQL> select * from sales_range;

SALESMAN_ID SALESMAN_NAME   SALES_AMOUNT SALES_DATE

----------- --------------- ------------ -------------------

1001 tom                     1000 2011-02-01 00:00:00

1004 john                    1000 2011-01-01 00:00:00

1002 jerry                   1000 2011-05-01 00:00:00

1003 rose                    1000 2011-08-01 00:00:00

1005 john                    1000 2011-11-01 00:00:0

2、簇：cluster （多表链接查询）【先建立簇，然后建立簇表，最后建立索引】

1)建立簇

SQL> create cluster dept_emp_clu(deptno number(3))

2  pctfree 20 pctused 60

3  size 500 tablespace lx01;

Cluster created.

2）建立簇表

SQL> create table department(

2  id number(3) primary key,

3  dname varchar(14),loc varchar2(13))

4  cluster dept_emp_clu(id);

Table created.

SQL> create table employee(

2  eno number(4) primary key,

3  ename varchar2(10),

4  job varchar2(9),

5  mgr number(4),

6  hiredate date,

7  sal number(7,2),

8  comm number(7,2),

9  dept_id number(3) references department)

10  cluster dept_emp_clu(dept_id);

Table created.

3）建立索引

SQL> create index dept_emp_idx on cluster dept_emp_clu ——在簇上建立索引，并将索引和簇放在不同的表空间

2  tablespace lx02;

Index created.