管理Oracle约束与分区表.doc

1．约束
作用：
约束用于确保数据库数据的完整性,在oracle数据库中,可以使用约束,触发器和应用代码(过程,函数)3种方法实现数据完整性,这3种方法中,因为约束易于维护,并且具有最好的性能,所以实现数据完整性首选约束.
分类：
约束的种类有：not null,unique,primary key,foreign key,check
Not null确保字段值不能为空
Unique:确保字段值唯一性
Primary key,最常用的约束（主键约束），主键约束的列值不仅不能重复，也不能为NULL，注意一张表最多只能有一个主键约束，当定义主键约束后oracle自动建立一个以主键为关键字段的索引。
Foreign key:定义了主从表之间的关系，foreign要定义在从表上，但主表必须具有主键约束或唯一约束，当定义froeign key后外部键列的数据必须在主表的主键列中存在，或者为NULL
Check::用于强制表行数据必须满足的条件，如工资表，工人工资必须在2000-5000之间
约束状态
enable validate:是默认,新旧数据同时满足约束规则
enable novalidate:旧数据可以不满足,检新插入的数据要满足约束
disable validate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用
disable novalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,
约束常用语句
1. create table t(i number,v mubmer not null)  
2. create table t(i number,v mubmer unique)  
3. create table t(i number constraint pk_i primary key,v number)  
4. create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v));  
5. alter table t add constraint pk_i primary key (i)  
6. alter table t modify i not null;  
7. alter table t add constraint t_i unique(i)[(create index ind_name on t(i))];  
8. alter table t add constraint t_i check(i in (1,2,3,4,5));  
9. alter table t disable novalidate constraint i  
10. alter table t enable novalidate constraint check_i  
11. alter table t drop constraint i;  
12. alter table t drop primary key i; 
#常用的数据字典
1. dba_constraints  
2. dba_cons_columns  
3. user_cons_columns  
4. user_constraints  
简单应用
检验当为一个表建立主键索时后，这个字段是否满足约束非空，唯一性，而且自动建立一个索引，并查看当把约束状态关闭再次插入相同的记录,是否还能把把约束设为enable ividate状态。
1. SQL> create table t(i number constraint pk_i primary key,v number);  
2. SQL> insert into t values(1,2);  
3. SQL> insert into t values(3,4);  
4. SQL> commit;  
5. SQL> select * from t;  
6.          I          V  
7. ---------- ---------------------------  
8.          1          2  
9.          3          4  
现在表中有两条记录，然后给它插主键为空或相同的值
1. SQL> insert into t values('',10);  
2. ERROR at line 1:  
3. ORA-01400: cannot insert NULL into ("Y"."T"."I")  
4. SQL> insert into t values(1,10);  
5. ERROR at line 1:  
6. ORA-00001: unique constraint (Y.PK_I) violated  
可以看到全部报错，此时主键不能为空或重复
查看是否建立索引
1. SQL> select index_name from user_indexes;  
2. INDEX_NAME  
3. ------------------------------  
4. PK_I  
把约束关闭再次做同样的操用
1. SQL> alter table t disable novalidate constraint pk_i;  
2. Table altered.  
3. SQL> insert into t values('',10);  
4. 1 row created.  
5. SQL> insert into t values(1,10);  
6. 1 row created.  
7. SQL> commit;  
8. Commit complete.  
9. SQL> select * from t;  
10.          I          V  
11. ---------- ----------  
12.          1          2  
13.          3          4  
14.                    10  
15.          1         10  
16. SQL>  select index_name from user_indexes;  
17. no rows selected  
可见当把约束关闭后就可以何意给表插数据了，而具索引也自动删除了。
现在激活约束
1. SQL> alter table t enable validate constraint pk_i;  
2. alter table t enable validate constraint pk_i  
3. ERROR at line 1:  
4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  
因为表中主键有相同的值所以不能恢复到enable validate状态了
再次测试回复到enable novalidate
1. SQL> alter table t enable novalidate constraint pk_i;  
2. alter table t enable validate constraint pk_i  
3. ERROR at line 1:  
4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  
也失败了,
因为表中主键有了空值和相同的值,所以恢复不到enable validate状态,但enable novalidate不检查旧数据所以应该还能恢复到enable novalidate.
要想恢复到enable novalidate必须建立主键索引(关闭约束时自动删除的那个索引)如下:
1. SQL> create index pk_i on t(i);  
2. Index created.  
然后恢复到enable disvalidate,以后再插数据不能为空,主键也不能重复了.
1. SQL> alter table t enable novalidate constraint pk_i;  
2. Table altered.  
3. SQL> insert into t values(1,14);  
4. insert into t values(1,14)  
5. ERROR at line 1:  
6. ORA-00001: unique constraint (SYS.PK_I) violated  
2.修正约束数据
当给一个表作主键约束时,因为已存数据不满足约束规则,会提示错误信息,些时必须对数据进行修正
要修正数据先找出不满足约束的数据
如下表,有不满足约束的数据
1. SQL> select * from t;  
2.          I          V  
3. ---------- ------------------------  
4.          1          2  
5.          3          4  
6.          15         12  
7.          15         10  
如果一个表数据量多可通过如下方法查找
1. SQL> alter table t drop constraint pk_i;    
2. Table altered.  
3. SQL>conn y / 123  
4. SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql  
5. Table created.  
6. SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;  
7. select * from t where rowid in (select row_id from exceptions)  
8.          I          V  
9. ---------- ------------------------  
10.          15         12  
11.          15         10  
找到了重复的记录
修正
1. SQL>update t set i=10 where v=12;  
2. SQL> select * from t;  
3.          I          V  
4. ---------- ----------  
5.          1          2  
6.          3          4  
7.          10         12  
8.          15         10  
再建主键约束
1. alter table t add constraint pk_i primary key (i)  
2. Table altered.  
成功了!!!
二:分区表管理
作用:将在张大表的数据分布到多个表分区段,不同分区彼此独立,从而提高了表的可用性和性能
种类:范围分区,散列分区(使用HASH算法,最常使用),列表分区,范围/散列组合分区,范围/列表组合分区
范围分区表
创建范围分区表
1. create table t(v number,b number)  
2. partition by range(v) (  
3. partition p1 values less than ('11') tablespace test1,  
4. partition p2 values less than ('21') tablespace test2);  
增加与删除分区
#增加分区
1. alter table t add partition p3 values less than ('31') tablespace test3;  
2. alter table t drop partition p3  
一个时间分区的例子
1. alter session set nls_data_lanage=AMERICAN;  
2. alter session set nls_data_format='DD-MON-YYYY' 
3. create table t(v_date date,b number)  
4. partition by range(v_date)(  
5. partition p1 values less than ('01-APR-2009') tablespace test1,  
6. partition p2 values less than ('01-JUN-2009') tablespace test2);  
2.散列分区表(最常用)
创建
1. create table t1(  
2. v number,b number)  
3. partition by hash(v)  
4. (partition p1 tablespace test1,  
5. partition p2 tablespace test2);  
增加分区
1. alter table t add partition p3 tablespace test3;  
删除分区
1. alter table t drop coalesce partition;  
3.列表分区
建列表分区
1. create table t(  
2. v varchar2(10),  
3. b number  
4. )partition by list(v)  
5. (partition p1 values('a','b') tablespace test1,  
6. partition p2 values('c','d') tablespace test2);  
#插入数据
1. SQL> insert into t values('a',10);  
2. SQL> insert into t values('d',20); 
#注意,插入数据时第一个字段只能为a,b,c,d
1. SQL> insert into t values('f',30);  
2. ERROR at line 1:  
3. ORA-14400: inserted partition key does not map to any partition  
#查询
1. select * from t;  
2. select * from t partition(p1);  
3. select * from t partition(p2);  
4. select * from t where v=XXX  
增加分区
1. alter table t add partition p3 values('31','32') tablespace test3;  
删除分区
1. alter table t drop partition p3  
4.范围/散列组合分区
建立散列组合分区
1. create table t(  
2. v number,b number)  
3. partition by range(v)  
4. subpartition by hash(b) subpartitions 2  
5. store in (test1,test2)(  
6. partition p1 values less than ('11'),  
7. partition p2 values less than ('21'));  
查询
1. select * from t;  
2. select * from t partition(p1);  
3. select * from t where ....  
增加主分区和子分区
1. alter table t add partition p3 values less than ('31') tablespace test3;  
2. alter table t modify partition p3 add subpartition;  
删除分区
1. alter table t coalesce partition;  
2. alter table t modify partition p1 coalesce subpartition;  
5.范围/列表组合分区
创建
1. create table t(  
2. v number,b number)  
3. partition by range(v)  
4. subpartition by list(b)  
5. (partition p1 values less than ('11') tablespace test1(  
6. subpartition p1_1 values('1','3'),  
7. subpartition p1_2 values('5','6')  
8. ),  
9. partition p2 values less than ('21') tablespace test2(  
10. subpartition p2_1 values('13','14'),  
11. subpartition p2_2 values('15','16')  
12. ));  
查询
1. select * from t  
2. select * from t partition(p1)  
3. select * from t subpartition(p1_1)  
4. select * from t where .....  
5. select segment_name,partition_name,tablespace_name  
6. from user_segments where segment_name='T';  
增加分区和子分区
1. alter table t add partition p3 values less than ('31') tablespace test3(  
2. subpartition p3_1 values('25','26'),  
3. subpartition p3_2 values('22','23'));  
4. alter table t modify partition r3  
5. add subpartition r3_3 tablespace test3 values('28','29');  
删除分区
1. alter table t modify partition p1 coalesce subpartition;  
其它设置
1. 交换分区数据  
2. alter table t exchange partition p1 with table tt;  
3. 载断分区  
4. alter table t truncate partition p1;  
5. 修改分区名  
6. alter table t rename partition p2_1 to p2;  
7. 合并分区  
8. alter table t merge partitions p1,p2 into partition p01  
9. 重组分区  
10. alter table t move partition p1 tablespace test04  
11. 为列表分区和子分区加值  
12. alter table t modify partition p1 add values('111');  
13. alter table t modify subpartition p3_1 add values('111');  
14. 从列表分区和子分区中删除值  
15. alter table t modify partition p1 drop values('111')  
16. alter table t modify subpartition p3_1 drop values('111')  
分区表常用的数据字典
1. 分区表信息:  dba_part_tables  
2. 显示分区:    dba_tab_partitions  
3. 显示子分区:  dba_tab_subpartitions  
4. 显示分区列:  dba_part_key_columns  
5. 显示子分区列:dba_subpart_dey_columns  
6. 显示分区索引:dba_part_indexes  
7. 显示索引分区:dba_ind_partitions  
来源：网络 编辑：联动北方技术论坛