How to set up partitioned tables and cluster tables in Oracle

1, Partition table: (>2G) to optimize the large table (Range partitioning,list partitioninghash partitioning,composite Partitioning)

--Create a partitioned table by 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)--Specify the metric for the partition

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,

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: Cluster (multi-table link query) "First set up a cluster, and then set up a cluster table, and finally set up an index"

1) to establish a cluster

sql> Create cluster DEPT_EMP_CLU (DEPTNO number (3))

2 Pctfree pctused 60

3 size tablespace lx01;

Cluster created.

2) set up a cluster table

Sql> CREATE TABLE Department (

2 ID Number (3) primary key,

3 dname varchar (), 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)

Cluster DEPT_EMP_CLU (DEPT_ID);

Table created.

3) Establish the index

Sql> CREATE index DEPT_EMP_IDX on cluster dept_emp_clu--indexes on the cluster and places indexes and clusters in different table spaces

2 tablespace lx02;

Index created.

Cuug

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/