Oracle 笔记（六）、表的管理（约束、索引、锁定、表分区）

柔情只为你懂 2022-05-19 13:52 152阅读 0赞

**一、表完整性与约束**  
  
实体完整性：主键约束  
域完整性  
参照完整性：外键约束  
  
SELECT constraint\_name, constraint\_type FROM user\_constriants WHERE table\_name = 'EMP';  
  
  
  
**二、管理索引**  
索引是建立在表字段上的一种存储结构，利用索引可以加快表的查询速度。当索引键列用于 SQL 语句的 WHERE 子句时，该索引将直接指向包含这些值得行位置，合理使用索引是减少磁盘 I/O 的主要方法，它只影响执行的速度。  
  
创建索引时，Oracle 将获取要创建索引的列，并对其进行排序。然后将 ROWID 连同每一行的索引值存储起来。使用时，Oracle 先通过已排序的索引值执行快速搜索，然后使用相关联的 ROWID 值来定位具有所要查找的记录行。  
  
一旦创建索引，Oracle 会自动维护和使用它们。更新行记录时，会自动更新索引，所以为表创建过多的索引会降低表的性能。  
  
获得索引信息 user\_indexes 视图。  
  
重建现有的索引性能要优于删除重建索引。 ALTER INDEX item\_index REBUILD;  
  
  
**2.0、传统的 B树索引：**  
CREATE INDEX ix\_mm ON mm(m1);  
  
  
**2.1、唯一索引：**唯一约束就是唯一索引，但唯一索引未必是唯一约束。  
CREATE UNIQUE INDEX myidx ON mm(m2);  
  
  
**2.2、组合索引：**在表中多个列上创建的索引。如果 SELECT 语句中 WHERE 子句引用了组合索引中的所有列或大多数列，则组合索引可以提高数据检索速度。通常创建索引时最频繁访问的列应该放在列表的最前面。  
CREATE INDEX comp\_index ON itemfile(p\_category, itemrate);  
  
  
**2.3、反向键索引：**特殊的索引，在索引含有序数的列时非常有用，反向键索引通过简单的反向被索引中的数据来解决问题。  
CREATE INDEX rev\_index ON itemfile(itemcode) REVERSE;  
  
ps.可以使用 NOREVERSE 将反向键索引重建为标准索引，但不能将标准索引重建为反向键索引。  
ALTER INDEX rev\_index REBUILD NOREVERSE;  
  
  
**2.4、位图索引：**表里数据量大，但表的唯一值少。位图索引使用每个键值的位图，而不是用 ROWID 列表。  
CREATE BITMAP INDEX bit\_emp ON emp(sex);  
  
  
2.5、索引组织表  
  
2.6、基于函数的索引  
  
2.7、索引中的分区  
  
  
  
**三、锁定**  
锁定是数据库用来控制共享资源并发访问的机制。当更新数据的时候，Oracle 会自动将其锁定，其他用户只能查看该数据，直至回滚或提交事务后，锁才会释放，其他用户才能编辑。  
  
**3.1、行级锁**  
行级锁只对用户正在访问的行进行锁定。在使用 INSERT、UPDATE、DELETE 和 SELECT ... FOR UPDATE 等语句时，Oracle 会自动应用行锁定。

> SELECT \* FROM order\_master WHERE vencode = 'V002'  
> FOR UPDATE WAIT 5;  
> UPDATE .....  
> COMMIT;

**3.2、表级锁**  
表级所将保护数据，在事务处理过程中，表级锁会限制对整个表的增、删、改操作。

> LOCK TABLE <table\_name> IN <look\_mode> MODE \[NOWAIT\];  
> ● 行共享（ROW SHARE, RS）  
> ● 行排他（ROW EXCLUSIVE, RX）  
> ● 共享（SHARE, S）  
> ● 共享行排他（SHARE ROW EXCLUSIVE, SRX）  
> ● 排他（EXCLUSIVE, X）

**3.3、死锁**  
两个事务相互等待对方完成任务，就会出现死锁。Oracle 将自动检测死锁，并通过终止两个事务处理之一来解决问题。  
  
  
  
**四、表分区**  
Oracle 提供表分区技术，以来改善大型应用系统的性能。表分区允许用户把一个表中所有的行分为几个部分，并将这些部分存储在不同的位置。在大型表中利用表分区，能改善表查询性能、方便管理表，以及备份\\恢复、提高数据安全性。  
  
表分区对用户使透明的，用户在更新和查询时把分区表当作普通表使用。CREATE TABLE 语句中增加 PARTTION 子句以创建表分区。  
  
ps.要分区的表不能具有 LONG 和 LONG RAW 数据类型的列。  
  
  
**4.1、范围分区**  
根据表某一个列或一组列的值范围，决定该数据存储在哪个分区上。  
例如：对 sales 表的 sales\_cost 列值进行分区

> CREATE TABLE sales  
> (  
> ...  
> )  
> PARTTION BY RANGE (sales\_cost)  
> (  
> PARTTION P1 VALUES LESS THAN (1000),  
> PARTTION P2 VALUES LESS THAN (2000),  
> PARTTION P3 VALUES LESS THAN (3000),  
> PARTTION P4 VALUES LESS THAN (MAXVALUE) /\* 大于3000的其他值都存储于分区 P4 \*/  
> );

**4.2、散列分区**  
散列分区通过在分区键值上执行一个散列函数来决定数据的物理位置。散列分区把记录平均地分布到不同的分区，减少了磁盘 I/O 争用的可能性。  
例如：创建 employer 表按照 department 列分区成 4个散列分区

> CREATE TABLE employer  
> (  
> ...  
> )  
> PAERTTION BY HASH (department) PARTTIONS 4;

**4.3、复合分区**  
复合分区是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后再散列分区内创建散列子分区。  
例如：创建 sales3 表，分区为复合分区，先根据 sales\_date 列创建范围分区，然后再分区内根据 product\_id 创建子分区。

> CREATE TABLE sales  
> (  
> ...  
> )  
> PARTTION BY RANGE (sales\_date)  
> SUBPARTTION BY HASH (product\_id) SUBPARTTION 5  
> (  
> PARTTION P1 VALUES LESS THEN (DATE '2001-04-01'),  
> PARTTION P2 VALUES LESS THEN (DATE '2001-07-01'),  
> PARTTION P3 VALUES LESS THEN (DATE '2001-09-01'),  
> PARTTION P4 VALUES LESS THEN (MAXVALUE)  
> );

**4.4、列表分区**  
列表分区允许用户明确地控制行到分区的映射。列表分区允许按自然方式对无序和不相关的数据集进行分组和组织。  
例如：根据职员住址 emp\_address 列对 employee 表进行分区，并针对不同地区不同存放。

> CREATE TABLE employee  
> (  
> ...  
> )  
> PARTTION BY LIST (emp\_address )  
> (  
> PARTTION north VALUES ('芝加哥'),  
> PARTTION west VALUES ('旧金山', '洛杉矶'),  
> PARTTION south VALUES ('亚特兰大', '达拉斯', '休斯顿'),  
> PARTTION east VALUES ('纽约' ,'波士顿'),  
> PARTTION aa VALUES (DEFAULT) /\* 其他的地址存储 aa 分区，不指定则不接受其他地址输入 \*/  
> );

插入数据时 Oracle 会根据分区列的值将记录存储到对应的分区。  
查询分区数据： SELECT \* FROM sales3 PARTTION (P3);  
  
  
**分区的维护操作：**  
分区的维护操作时修改分区表的分区，以获得更佳的 I/O 负载平衡。  
  
**1、添加分区：**在最后一个分区之后添加新的分区。  
ALTER TABLE sales ADD PARTTION P4 VALUES LESS THEN (4000);  
  
**2、删除分区：**删除分区时，分区中数据也随之删除。  
ALTER TABLE sales DROP PARTTION P4;  
  
**3、截断分区：**将删除表分区的所有记录。  
ALTER TABLE sales TRUNCATE PARTTION P3;  
  
**4、合并分区：**可将分范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承两个分区的上界。  
ALTER TABLE sales MERGE PARTTIONS P1, P2 INTO PARTTION P2;  
  
**5、拆分分区：**允许用户将一个分区拆分成两个分区。  
/\* 以 '2005-01-01' 为分界拆分一个分区数据 \*/  
ALTER TABLE sales SPLIT PARTTION P3 AT (DATE '2005-01-01')  
INTO (PARTTION P31, PARTTION P32);