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// TEST_PARTITION PART2 2000>=1000 和 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> |
linux scheduled shutdown command
What is the difference between golang and python
How to match numbers in regular expressions
What does data intelligence mean?
What are the Chinese programming languages?
What are the video server configuration parameters?
The difference between export and export default
Binary representation of negative numbers
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
