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
转载于:https://blog.51cto.com/19880614/1168942