create cluster scott.cluster1(code_key number); create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1); create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2); create index
create cluster scott.cluster1(code_key number);
create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1);
create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2);
create index index1 on cluster scott.cluster1; --为簇创建索引
//1.首先创建簇表
SQL> create cluster cluster1(share_col number(10));
Cluster created
SQL> create table tab1(sno number(10),sname varchar2(20)) cluster cluster1(sno);
Table created
SQL> create table tab2(sno number(10),saddr varchar2(20)) cluster cluster1(sno);
Table created
SQL> create index cluster_index on cluster cluster1;
Index created
SQL> select uc.CLUSTER_NAME,uc.TABLESPACE_NAME,uc.CLUSTER_TYPE from user_clusters uc where uc.CLUSTER_NAME='CLUSTER1';
CLUSTER_NAME TABLESPACE_NAME CLUSTER_TYPE
------------------------------ ------------------------------ ------------
CLUSTER1 USERS INDEX
SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.CLUSTER_NAME from user_tables ut where ut.TABLE_NAME='TEST1';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------
SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.CLUSTER_NAME from user_tables ut where ut.CLUSTER_NAME='CLUSTER1';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------
SQL>
先删除表，再删除簇表
四、临时表：
存放临时数据，可以使用临时表；临时表被每个session
单独使用，即：不同session看到的临时表中的数据可能不一
样。
如果在退出session时删除临时表中的数据，可以使用on
commit preserve rows；如果在用户commit或rollback时删
除临时表中的数据，可以使用on commit delete rows；
从v$sort_usage中查看正在使用临时表空间的session信
息和SQL语句的ID号，从v$sort_segment中查看临时表空间中
的段的使用情况。
临时表在临时表空间中保存。
create global temporary table temp_tab1() on commmit preserve rows/delete rows;
五、分区表：
q 允许用户将一个表分成多个分区
q 用户可以执行查询，只访问表中的特定分区
q 将不同的分区存储在不同的磁盘，提高访问性能和安全性
q 可以独立地备份和恢复每个分区
分区方式有一下几种：
范围分区： 以表中的一个列或一组列的值的范围分区
SQL> create table test_partition(id number(20),account number(20))
2 partition by range(id)(
3 partition part1 values less than(1000),
4 partition part2 values less than(2000),
5 partition part3 values less than(3000));
Table created
SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST_PARTITION PART1 1000//
TEST_PARTITION PART2 2000>=1000 和
SQL> insert into test_partition part1 values(1,100);
1 row inserted
SQL> insert into test_partition values(1000,200);
1 row inserted
SQL> insert into test_partition part1 values(2000,300);//标注为会被忽略
1 row inserted
SQL> insert into test_partition values(5000,400);
insert into test_partition values(5000,400)
ORA-14400: 插入的分区关键字未映射到任何分区
SQL> select * from test_partition partition(part1);
ID ACCOUNT
--------------------- ---------------------
SQL>
SQL> select * from test_partition partition(part2);
ID ACCOUNT
--------------------- ---------------------
SQL> alter table test_partition add partition part4 values less than( maxvalue );//无上限
Table altered
SQL> insert into test_partition values(6000,600);
1 row inserted
SQL> select * from test_partition partition(part4);
ID ACCOUNT
--------------------- ---------------------
SQL>
2. 散列分区
允许用户对不具有逻辑范围的数据进行分区
通过在分区键上执行HASH函数决定存储的分区
将数据平均地分布到不同的分区
SQL> create table test_partition_hash(id number(20),name varchar(20))
2 partition by hash (id)(//散列分区是通过hash算法得到分区来进行的
3 partition part1,partition part2,partition part3);
Table created
SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_HASH';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST_PARTITION_HASH PART1 //所以不存在high_value
TEST_PARTITION_HASH PART2
TEST_PARTITION_HASH PART3
SQL> insert into test_partition_hash values(1,'张三');
1 row inserted
SQL> insert into test_partition_hash values(2,'李四');
1 row inserted
SQL> insert into test_partition_hash values(3,'王五');
1 row inserted
SQL>
SQL> select * from test_partition_hash partition(part1);
ID NAME
--------------------- --------------------
SQL> select * from test_partition_hash partition(part2);
ID NAME
--------------------- --------------------
1 张三
3 王五
SQL> select * from test_partition_hash partition(part3);
ID NAME
--------------------- --------------------
2 李四
SQL>
3. 列表分区
允许用户将不相关的数据组织在一起
注意：列表分区是针对于可以列举的类型进行分区的
SQL> create table test_partition_list(id number(20),name varchar2(20),address varchar2(20))
2 partition by list(address)(
3 partition 上北 values('九江'),
4 partition 下南 values('赣州','鹰潭'),
5 partition 左西 values('抚州','新余'),
6 partition 右东 values('景德镇'));
Table created
SQL> insert into test_partition_list values(1,'吴xx','九江');
1 row inserted
SQL> insert into test_partition_list values(2,'陈xx','赣州');
1 row inserted
SQL> insert into test_partition_list values(3,'邹xx','抚州');
1 row inserted
SQL> insert into test_partition_list values(4,'刘xx','景德镇');
1 row inserted
SQL> select * from test_partition_list partition(上北);
ID NAME ADDRESS
--------------------- -------------------- --------------------
1 吴xx 九江
SQL> select * from test_partition_list partition(下南);
ID NAME ADDRESS
--------------------- -------------------- --------------------
2 陈xx 赣州
SQL>
4. 复合分区
范围分区与散列分区或列表分区的组合//只有这两种组合而且顺序不能颠倒
SQL> create table test_partition_compass(
2 id number(20),name varchar2(20))
3 partition by range(id)//主分区
4 subpartition by hash(name)//子分区
5 subpartitions 4(//每个主分区包括4个子分区
6 partition part1 values less than(100),//第一个分区
7 partition part2 values less than(200),
8 partition part3 values less than(maxvalue));
Table created
通过EM查看表的分区信息如下：
A browser with Javascript enabled is required for this page to operate properly.
Partitions
Partitioning Description
Partitioning Method
Range-Hash
Partitioning Columns
ID
Number of Partitions
3
Subpartitioning Columns
NAME
Number of Subpartitions
12
Partition Definitions
Previous
1-3 of 3
Next
Partition Name
High Value - ID (NUMBER)
Subpartition Default Tablespace
Subpartitions
PART1
100
USERS
4
PART2
200
USERS
4
PART3
MAXVALUE
USERS
4
Subpartition Definitions
Previous
1-12 of 12
Next
Partition Name
Subpartition Name
Tablespace
PART1
SYS_SUBP21
USERS
SYS_SUBP22
USERS
SYS_SUBP23
USERS
SYS_SUBP24
USERS
PART2
SYS_SUBP25
USERS
SYS_SUBP26
USERS
.......
........
........
Subpartition Definitions
Previous
1-12 of 12
Next
Partition Name
Subpartition Name
Tablespace
PART1
SYS_SUBP21
USERS
SYS_SUBP22
USERS
SYS_SUBP23
USERS
SYS_SUBP24
USERS
PART2
SYS_SUBP25
USERS
SYS_SUBP26
USERS
.......
........
........
5. 11g 新增的表分区的类型 – 引用分区
引用分区：基于由外键引用的父表的分区的方
法，它依赖已有的父表子表的关系，子表通过外键
关联到父表，进而继承了父表的分区方式而不需自
己创建，子表还继承了父表的维护操作。
1，主表是范围分区，子表是引用分区
2，主表是列表分区，子表是引用分区
3，主表是散列分区，子表是引用分区
//创建范围分区
SQL>
SQL> create table test_partition_student(
2 id number(10),name varchar2(20),grade varchar2(20),constraints pk_student primary key(id))
3 partition by range(id) (
4 partition part1 values less than(100),
5 partition part2 values less than(200),
6 partition part3 values less than(maxvalue));
Table created
SQL> create table test_partition_score(
2 id number(10) primary key ,sid number(10),goal number(10),constraints fk_student_score foreign key(sid)
3 references test_partition_student(id))
4 partition by reference(fk_student_score);
create table test_partition_score(
id number(10) primary key ,sid number(10),goal number(10),constraints fk_student_score foreign key(sid)
references test_partition_student(id))
partition by reference(fk_student_score)
ORA-14652: 不支持引用分区外键,关联的外键必须是非空
SQL>
SQL> create table test_partition_score(
2 id number(10) primary key , sid number(10) not null ,goal number(10),constraints fk_student_score foreign key(sid)
3 references test_partition_student(id))
4 partition by reference(fk_student_score);
Table created
SQL>
SQL> select upt.table_name,upt.partition_name,upt.high_value from user_tab_partitions upt where upt.table_name in(upper('test_partition_score'),upper('test_partition_student'));
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST_PARTITION_STUDENT PART3 MAXVALUE
TEST_PARTITION_SCORE PART1
TEST_PARTITION_SCORE PART2
TEST_PARTITION_SCORE PART3
6 rows selected
分区名称相同
SQL> insert into TEST_PARTITION_STUDENT values(1,'张三','二年级');
1 row inserted
SQL> insert into TEST_PARTITION_STUDENT values(111,'李四','三年级');
1 row inserted
SQL> insert into TEST_PARTITION_SCORE values(1,1,100);
1 row inserted
SQL> insert into TEST_PARTITION_SCORE values(2,111,99);
1 row inserted
SQL> select * from TEST_PARTITION_STUDENT partition(part1);
ID NAME GRADE
----------- -------------------- --------------------
1 张三 二年级
SQL> select * from TEST_PARTITION_SCORE partition(part1);
ID SID GOAL
----------- ----------- -----------
SQL>
6. 11g 新增的表分区的类型 – 间隔 分区
间隔分区：可以完全自动地根据间隔阈值创建范
围分区，它是范围分区的扩展 。
在数据仓库中有广泛的应用。
SQL> select * from user_part_tables;//存放的是分区表的情况
SQL> select * from user_tab_partitions;/存放的是表分区的情况
SQL> create table test_partition_interval(
2 id number(10),name varchar2(20),num number(20),_date date)
3 partition by range(_date)
4 interval(NUMTOYMINTERVAL(1,'MONTH'))(
5 partition part1 values less than(to_date(20140101,'yyyymmdd')));
create table test_partition_interval(
id number(10),name varchar2(20),num number(20), _date date)
partition by range(_date)
interval(NUMTOYMINTERVAL(1,'MONTH'))(
partition part1 values less than(to_date(20140101,'yyyymmdd')))
ORA-00911: 无效字符//不能使用_开头的属性名称
SQL>
SQL> create table test_partition_interval(
2 id number(10),name varchar2(20),num number(20), s_date date)
3 partition by range(s_date)
4 interval(NUMTOYMINTERVAL(1,'MONTH'))(//按照一个月来间隔增长的
5 partition part1 values less than(to_date(20140101,'yyyymmdd')));//初始的月份
Table created
SQL> select sysdate from dual;
SYSDATE
-----------
13-1月-15 1:
SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-1月-2014');
1 row inserted
SQL>
SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-2月-2014');
1 row inserted
SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_INTERVAL';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST_PARTITION_INTERVAL PART1 TO_DATE(' 2014-01-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-10月-2014');
1 row inserted
SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_INTERVAL';
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TEST_PARTITION_INTERVAL PART1 TO_DATE(' 2014-01-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TEST_PARTITION_INTERVAL SYS_P43 TO_DATE(' 2014-11-01 0', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL>
7. 11g 新增的表分区的类型 -- 基于虚拟列的 分区
基于虚拟列的分区：把分区建立在某个虚拟列
上，即 建立在函数或表达式的计算结果上 ，来完成
某种任务。
SQL>
SQL> create table test_partition_virtual(
2 id number(10),name varchar2(20),num number(20),price number(8,2),total_price as price*num virtual)
3 partition by range(total_price)(
4 partition part1 values less than(1000),
5 partition part2 values less than(2000),
6 partition part3 values less than(maxvalue));
ORA-02000: 缺失 ( 关键字
SQL>
SQL>
SQL> create table test_partition_virtual(
2 id number(10),name varchar2(20),num number(20),price number(8,2),total_price as (price*num) virtual)
3 partition by range(total_price)(
4 partition part1 values less than(1000),
5 partition part2 values less than(2000),
6 partition part3 values less than(maxvalue));
Table created
SQL> insert into test_partition_virtual(id,name,num,price) values(1,'上衣',10,100);
1 row inserted
SQL> insert into test_partition_virtual(id,name,num,price) values(1,'上衣',20,100);
1 row inserted
SQL> select * from test_partition_virtual partition(part1);
ID NAME NUM PRICE TOTAL_PRICE
----------- -------------------- --------------------- ---------- -----------
SQL> select * from test_partition_virtual partition(part2);
ID NAME NUM PRICE TOTAL_PRICE
----------- -------------------- --------------------- ---------- -----------
SQL>
8. 11g 新增的表分区的类型 — 系统 分区
系统分区：不指定分区列，由 ORACLE 来完成分
区的控制和管理，它没有了范围分区或列表分区的
界限。
分区维护操作
q 分区维护操作修改已分区表的分区。
q 分区维护的类型：
q 计划事件 － 定期删除最旧的分区
q 非计划事件 － 解决应用程序或系统问题
q 分区维护操作有：
q 添加分区
q 删除分区
q 截断分区
q 合并分区
q 拆分分区
SQL> alter table test_partition add partition values less than(6000);
alter table test_partition add partition values less than(6000)
ORA-14074: 分区界限必须调整为高于最后一个分区界限
分区因为是添加在最后以后分区上的
//删除分区
SQL> alter table test_partition drop partition part4;
Table altered
//增加分区
SQL> alter table test_partition add partition part4 values less than(7000);
Table altered
//拆分分区
SQL> alter table test_partition merge partitions part1,part2 into partition part2;
Table altered
//合并分区
SQL> alter table test_partition split partition part2 at(1000) into (partition part1 ,partition part2);
SQL> alter table test_partition split partition part2 at(1000) into (partition part1 ,partition part2);
Table altered
//截断分区
SQL> alter table test_partition truncate partition part3;
Table truncated
SQL>