模式对象管理及数据完整性

1、11-1模式对象管理及数据完模式对象管理及数据完整性整性11-2管理表（视图）管理表（视图）管理索引管理索引管理和维护数据完整性管理和维护数据完整性管理表管理表11-4目标目标 标识各种存储数据的放法标识各种存储数据的放法 Oracle数据类型数据类型 区分扩展区分扩展ROWID 和限制性和限制性ROWID 行结构行结构 创建正普通（创建正普通（regular）表和临时表表和临时表 重组，截断，和删除表重组，截断，和删除表 删除中表的一列删除中表的一列11-5存储用户数据存储用户数据Regular tableClusterPartitionedtableIndex-organizedtable

2、11-6Oracle 内置（内置（Built-in）数据类型数据类型CHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)DATETIMESTAMPRAW(N)BLOB, CLOB,NCLOB, BFILELONG, LONG RAWROWID, UROWIDVARRAYTABLEREFData typeBuilt-inUser-definedScalarRelationshipCollection11-7ROWID 格式格式 扩展扩展ROWID 格式格式 限制性限制性ROWID 格式格式OOOOOOBBBBBBFFFRRRData objec

3、t numberRelative file numberRow numberBlock numberBBBBBBBBFFFFRRRRBlock numberRow numberFile number.11-8行的结构行的结构Database blockRow headerColumn lengthColumn value11-9创建表创建表CREATE TABLE hr.employees(employee_id NUMBER(6), first_name VARCHAR2(20),last_name VARCHAR2(25),email VARCHAR2(25),phone_number V

4、ARCHAR2(20),hire_date DATE DEFAULT SYSDATE,job_id VARCHAR2(10),salary NUMBER(8,2),commission_pct NUMBER (2,2),manager_id NUMBER(6),department_id NUMBER(4)TABLESPACE USERS;11-10增加列增加列 alter table add (name type )修改列修改列 alter table modify (name type )删除列删除列 alter table drop column column-name增加约束增加约束

5、alter table add constraint删除约束删除约束 alter table drop constraint11-11创建表原则创建表原则 将不同的表放在不用的表空间中。将不同的表放在不用的表空间中。 使用本地管理表空间以避免碎片。使用本地管理表空间以避免碎片。 在表中使用若干标准在表中使用若干标准 extent 尺寸尺寸 以减少表空间的碎片。以减少表空间的碎片。11-12创建临时建表创建临时建表 the 使用使用GLOBAL TEMPORARY 子句来创建子句来创建: 这些表只在事务进行或会话期间存在。这些表只在事务进行或会话期间存在。 在这些数据上没有在这些数据上没有DML

6、锁锁 您可以在临时建表上创建索引，视图和触发器。您可以在临时建表上创建索引，视图和触发器。 ON COMMIT DELETE(PRESERVE) ROWSCREATE GLOBAL TEMPORARY TABLE hr.employees_tempAS SELECT * FROM hr.employees;11-13非分区（非分区（Nonpartitioned）表的重组表的重组 当一个非分区表被重组时，该表的结构被保存，但相关当一个非分区表被重组时，该表的结构被保存，但相关的对象不一定，如索引。的对象不一定，如索引。 它被用于将一个表移到不同的表空间或重组它被用于将一个表移到不同的表空间或重组

7、extents。ALTER TABLE hr.employeesMOVE TABLESPACE data1;11-14截断表截断表 截断表将删除该表中所有的行并释放所用的空间。截断表将删除该表中所有的行并释放所用的空间。 对应的索引也被截断。对应的索引也被截断。TRUNCATE TABLE hr.employees;11-15删除表删除表DROP TABLE hr.departmentsCASCADE CONSTRAINTS;11-16删除一列删除一列从表中删除一列从表中删除一列: 从每一行中删除列的长度和数据，并释放数据块中的空从每一行中删除列的长度和数据，并释放数据块中的空间。间。 删除大

8、型表中的一列需要相当长的时间。删除大型表中的一列需要相当长的时间。ALTER TABLE hr.employeesDROP COLUMN commentsCASCADE CONSTRAINTS CHECKPOINT 1000;11-17重新命名一列重新命名一列重新命名表中的一列重新命名表中的一列:ALTER TABLE hr.employeesRENAME COLUMN hire_dateTO start_date;11-18使用选项使用选项 将某一列标为无用将某一列标为无用: 删除无用的列删除无用的列: 继续删除无用列的操作继续删除无用列的操作:ALTER TABLE hr.employee

9、sSET UNUSED COLUMN comments CASCADE CONSTRAINTS;ALTER TABLE hr.employeesDROP UNUSED COLUMNS CHECKPOINT 1000;ALTER TABLE hr.employeesDROP COLUMNS CONTINUE CHECKPOINT 1000;11-19获得表的信息获得表的信息通过查询以下的视图可以获取有关表的信息通过查询以下的视图可以获取有关表的信息: DBA_TABLES(表类型表类型) DBA_OBJECTS(对象类型对象类型) DBA_SEGMENTS(段所在表空间段所在表空间) DBA_I

10、NDEXES(索引基于表索引基于表)管理索引管理索引11-21目标目标 列出索引的不同类型和它们的应用列出索引的不同类型和它们的应用 创建各种类型索引创建各种类型索引 重组索引重组索引 管理索引管理索引 监督索引的应用监督索引的应用 获得索引的信息获得索引的信息11-22索引的分类索引的分类 逻辑（分类）逻辑（分类）: 单列或多列单列或多列 唯一或非唯一唯一或非唯一 基于函数的基于函数的 物理（分类）物理（分类） : 分区或非分区分区或非分区 B-树树: 正常或反转关键字正常或反转关键字 位图位图11-23B-树树 索引索引Index entry headerKey column length

11、Key column valueROWIDRootBranchLeafIndex entry11-24位图索引位图索引keystartROWIDendROWIDbitmapTableIndexBlock 10Block 11Block 12File 311-25B-树树 索引和位图索引的比较索引和位图索引的比较B-树（树（B-tree）适合于适合于 high-cardinality 列列对关建字列的修改相对不对关建字列的修改相对不算昂贵算昂贵使用谓词使用谓词OR查询效率不高查询效率不高用于用于 OLTP位图（位图（Bitmap）适合于适合于 low-cardinality 列列对关建字列的修改

12、相对不对关建字列的修改相对不算昂贵算昂贵使用谓词使用谓词OR查询效率高查询效率高用于用于 data warehousing（DSS）11-26创建创建 B-树树 索引索引CREATE INDEX hr.employees_last_name_idxON hr.employees(last_name)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx;11-27创建索引原则创建索引原则 平衡查询和平衡查询和DML的需要。的需要。 将其放入单独的表空间。将其放入单独的表空间。 使用统

13、一的使用统一的 extent 尺寸尺寸: 数据块尺寸的数据块尺寸的5的倍数或表空间的倍数或表空间的的 MINIMUM EXTENT的的尺寸尺寸。 对大索引可考虑使用对大索引可考虑使用NOLOGGING。 索引的索引的INITRANS 参数通常应改比相对应表的高。参数通常应改比相对应表的高。11-28创建位图索引创建位图索引CREATE BITMAP INDEX orders_region_id_idxON orders(region_id)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE

14、 indx;11-29改变索引的存储参数改变索引的存储参数ALTER INDEX employees_last_name_idxSTORAGE(NEXT 400KMAXEXTENTS 100);11-30分配与回收索引的空间分配与回收索引的空间ALTER INDEX orders_region_id_idxALLOCATE EXTENT (SIZE 200KDATAFILE /DISK6/indx01.dbf);ALTER INDEX orders_id_idxDEALLOCATE UNUSED;11-31重建索引重建索引使用使用ALTER INDEX 命令可进行如下的操作命令可进行如下的操作

15、: 将一个索引移到另一个表空间将一个索引移到另一个表空间 去掉已被删除的项以改进空间的利用率去掉已被删除的项以改进空间的利用率ALTER INDEX orders_region_id_idx REBUILDTABLESPACE indx02;11-32以联机方式重建索引以联机方式重建索引 索引在重建时只有极短暂的时间被加锁。索引在重建时只有极短暂的时间被加锁。 仍有一些限制。仍有一些限制。ALTER INDEX orders_id_idx REBUILD ONLINE;11-33合并索引中的碎片合并索引中的碎片Before coalescingAfter coalescingALTER IND

16、EX orders_id_idx COALESCE;11-34检查索引的有效性检查索引的有效性ANALYZE INDEX orders_region_id_idxVALIDATE STRUCTURE;INDEX_STATS11-35删除索引删除索引 在大规模装入数据之前删除和重建索引。在大规模装入数据之前删除和重建索引。 删除索引不常用的索引，并在需要时重建。删除索引不常用的索引，并在需要时重建。 删除并重建无效的索引。删除并重建无效的索引。DROP INDEX hr.departments_name_idx;11-36标识没用过的索引标识没用过的索引 启动监督索引的使用启动监督索引的使用:

17、停止监督一个索引的使用停止监督一个索引的使用:ALTER INDEX hr.dept_id_idx MONITORING USAGEALTER INDEX hr.dept_id_idx NOMONITORING USAGE11-37获得索引的信息获得索引的信息通过查询以下视图可以获取有关索引的信息通过查询以下视图可以获取有关索引的信息: DBA_INDEXES: 提供了有关索引的信息提供了有关索引的信息 DBA_IND_COLUMNS: 提供了有关索引列的信息提供了有关索引列的信息 V$OBJECT_USAGE: 提供了有关索引使用的信息提供了有关索引使用的信息管理和维护数据完整性管理和维护数

18、据完整性11-39目标目标 实现数据完整性约束实现数据完整性约束 管理数据约束管理数据约束 获得约束的信息获得约束的信息11-40数据完整性数据完整性ApplicationcodeTableDataIntegrityconstraintDatabasetrigger11-41约束的类型约束的类型约束约束NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK描述描述说明某一列不能包含空值说明某一列不能包含空值指明一列或几列的组合为唯一指明一列或几列的组合为唯一指明一列或几列的组合为该表的主键指明一列或几列的组合为该表的主键指明一列或几列的组合为外键以维护引用完整性指明

19、一列或几列的组合为外键以维护引用完整性约束约束说明该表中的每一行都必须满足的一个条件说明该表中的每一行都必须满足的一个条件11-42行行 列列 参照参照11-43约束的状态约束的状态ENABLENOVALIDATEExisting dataNew dataDISABLENOVALIDATEDISABLEVALIDATE=ENABLEVALIDATE11-44约束的检验约束的检验DML statementCheck nondeferred constraintsCOMMITCheck deferred constraints11-45定义约束为立即的定义约束为立即的（Immediate）或延迟的

20、（或延迟的（ Deferred） 使用使用SET CONSTRAINTS 语句来使约束语句来使约束DEFERRED 或或IMMEDIATE。 ALTER SESSION语句也有子句可将约束置为语句也有子句可将约束置为 DEFERRED 或或IMMEDIATE。11-46主键和唯一键的实施主键和唯一键的实施Is an index available for use?YesNoNoYesYesNoCreate nonunique indexCreate unique indexDo not use indexUse existing indexKeyenabled?Constraintdeferr

21、able?ConstraintDeferrable?Is the indexnonunique?YesNo/YesNo11-47外键约束外键约束Appropriate SolutionDesired ActionDrop parent tableCascade constraintsTruncate parent tableDisable or drop foreign keyPerform DML on child tableEnsure that the tablespace containing the parent key is onlineUse the CASCADE CONSTR

22、AINTS clauseDrop tablespace containingparent table11-48在创建一个表时定义约束在创建一个表时定义约束CREATE TABLE hr.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEYDEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL,dept_id NUMBER(7)TABLESPACE users;11-49定义约束的原则定义约束的原则 主键和唯一键主键和唯一键: 将它们的索引放在（与表）不同的表空间中。将它们的索引放在（与表）不同的表空间中。 如果经常有大规模数据的装入使用非如果经常有大规模数据的装入使用非唯一唯一索引。索引。 自引用的外键自引用的外键: 在初始装入数据之后再定义或开启外键。在初始装