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.

CUUG

更多oracle视频教程请点击：http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6