时间：2021-07-01 10:21:17 帮助过：40人阅读
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 ------------------------------ ------------------------------ ------------------------------ TAB2 USERS CLUSTER1 TAB1 USERS CLUSTER1
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//<1000 TEST_PARTITION PART2 2000>=1000 和<2000 TEST_PARTITION PART3 3000
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 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 --------------------- --------------------- 1 100
SQL> SQL> select * from test_partition partition(part2); ID ACCOUNT --------------------- --------------------- 1000 200
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 --------------------- --------------------- 6000 600
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.
|
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 PART1 100 TEST_PARTITION_STUDENT PART2 200 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 ----------- ----------- ----------- 1 1 100
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 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 00:00:00', '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 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_PARTITION_INTERVAL SYS_P43 TO_DATE(' 2014-11-01 00:00:00', '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 ----------- -------------------- --------------------- ---------- ----------- 1 上衣 10 100.00 1000
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> |
