《模式对象管理及数据完整性0》由会员分享，可在线阅读，更多相关《模式对象管理及数据完整性0（57页珍藏版）》请在金锄头文库上搜索。
1、11-1模式对象管理及数据完模式对象管理及数据完整性整性11-2管理表（视图）管理表（视图）管理索引管理索引管理和维护数据完整性管理和维护数据完整性管理表管理表11-4目标目标标识各种存储数据的放法标识各种存储数据的放法Oracle数据类型数据类型区分扩展区分扩展ROWID 和限制性和限制性ROWID行结构行结构创建正普通（创建正普通（regular）表和临时表表和临时表重组，截断，和删除表重组，截断，和删除表删除中表的一列删除中表的一列11-5存储用户数据存储用户数据Regular tableClusterPartitionedtableIndex-organizedtable11-6Ora
2、cle 内置（内置（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 object numberR
3、elative 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 VARCHAR2(2
4、0),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增加约束增加约束 alter tab
5、le 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）表的重组表的重组当一个非分区表被重组时，该表的结构被保存，但相关的当一个非分区表被重组时，该表的结构被保存，但相关的对象不一定，如索引。对象不一定，如索引。它被用于将一个表移到不同的表空间或重组它被用于将一个表移到不同的表空间或重组extents。ALTER TABLE
7、 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.employeesSET UNUSED COLUMN comment
9、s 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(用户的数据在哪些表空间用户的数据在哪些表空间
10、)DBA_INDEXES(某索引基于哪个表某索引基于哪个表)管理索引管理索引11-21目标目标列出索引的不同类型和它们的应用列出索引的不同类型和它们的应用创建各种类型索引创建各种类型索引重组索引重组索引管理索引管理索引监督索引的应用监督索引的应用获得索引的信息获得索引的信息11-22索引的分类索引的分类逻辑（分类）逻辑（分类）:单列或多列单列或多列唯一或非唯一唯一或非唯一基于函数的基于函数的物理（分类）物理（分类） :分区或非分区分区或非分区B-树树: 正常或反转关键字正常或反转关键字位图位图11-23B-树树 索引索引Index entry headerKey column lengthKe
11、y 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 indx;1
14、1-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 INDEX orders_i
16、d_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: 提供了有关索引使用的信息提供了有关索引使用的信息管理和维护数据完整性管理和维护数据完整性11-39目标目标实现数据完整
18、性约束实现数据完整性约束管理数据约束管理数据约束获得约束的信息获得约束的信息11-40数据完整性数据完整性ApplicationcodeTableDataIntegrityconstraintDatabasetrigger11-41约束的类型约束的类型约束约束NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK描述描述说明某一列不能包含空值说明某一列不能包含空值指明一列或几列的组合为唯一指明一列或几列的组合为唯一指明一列或几列的组合为该表的主键指明一列或几列的组合为该表的主键指明一列或几列的组合为外键以维护引用完整性指明一列或几列的组合为外键以维护引用完整性约束约
19、束说明该表中的每一行都必须满足的一个条件说明该表中的每一行都必须满足的一个条件11-42行行 列列 参照参照11-43约束的状态约束的状态ENABLENOVALIDATEExisting dataNew dataDISABLENOVALIDATEDISABLEVALIDATE=ENABLEVALIDATE11-44enable/disable对未来的数据有约束对未来的数据有约束/无约束。无约束。validate/novalidate对已有的数据有约束对已有的数据有约束/无约束。无约束。enable约束时没有说明约束时没有说明novalidate则意味着则意味着validatedisable约束
20、时没有说明约束时没有说明validate则意味着则意味着novalidate11-45约束的检验约束的检验DML statementCheck nondeferred constraintsCOMMITCheck deferred constraints11-46非延迟约束在每个非延迟约束在每个DML语句结束时进行约束检查。违反约束语句结束时进行约束检查。违反约束导致语句回退。默认。导致语句回退。默认。可延迟约束使用可延迟约束使用deferrable显式指定。仅在提交事务处理显式指定。仅在提交事务处理时才检查约束。如果提交时检测到违反约束，则回退整个事时才检查约束。如果提交时检测到违反约束，则
21、回退整个事务处理。参照完整性时非常有用。务处理。参照完整性时非常有用。11-47定义约束为立即的定义约束为立即的（Immediate）或延迟的（或延迟的（ Deferred）使用使用SET CONSTRAINTS 语句来使约束语句来使约束DEFERRED 或或IMMEDIATE。ALTER SESSION语句也有子句可将约束置为语句也有子句可将约束置为 DEFERRED 或或IMMEDIATE。constraint如果被定义成如果被定义成deferrable那么这个那么这个constraints可以在可以在deferred和和imediate两种状态相两种状态相互转换互转换 11-48主键和唯
22、一键的实施主键和唯一键的实施Is an index available for use?YesNoNoYesYesNoCreate nonunique indexCreate unique indexDo not use indexUse existing indexKeyenabled?Constraintdeferrable?ConstraintDeferrable?Is the indexnonunique?YesNo/YesNo11-49外键约束外键约束Appropriate SolutionDesired ActionDrop parent tableCascade constrai
23、ntsTruncate parent tableDisable or drop foreign keyPerform DML on child tableEnsure that the tablespace containing the parent key is onlineUse the CASCADE CONSTRAINTS clauseDrop tablespace containingparent table11-50在创建一个表时定义约束在创建一个表时定义约束CREATE TABLE hr.employee(id NUMBER(7) CONSTRAINT employee_id_p
24、k 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-51创建表时定义列级完整性约束创建表时定义列级完整性约束创建表时定义表级完整性约束创建表时定义表级完整性约束为表增加约束为表增加约束三者之间语句的差别三者之间语句的差别11-52定义约束的原则定义约束的原则主键和唯一键主键和唯
25、一键:将它们的索引放在（与表）不同的表空间中。将它们的索引放在（与表）不同的表空间中。如果经常有大规模数据的装入使用非如果经常有大规模数据的装入使用非唯一唯一索引。索引。自引用的外键自引用的外键:在初始装入数据之后再定义或开启外键。在初始装入数据之后再定义或开启外键。延迟延迟约束的检查。约束的检查。11-53开启开启约束约束在表上没有锁在表上没有锁主键和唯一键必须主键和唯一键必须 使用非唯一索引使用非唯一索引不验证表中的数据不验证表中的数据ENABLE NOVALIDATEALTER TABLE hr.departmentsENABLE NOVALIDATE CONSTRAINT dept_p
26、k;11-54开启开启约束约束对表加锁对表加锁既可以使用既可以使用唯一也可以使用唯一也可以使用非唯一索引非唯一索引需要验证表中的数据需要验证表中的数据ENABLE VALIDATEALTER TABLE hr.employeesENABLE VALIDATE CONSTRAINT emp_dept_fk;11-55重新命名约束重新命名约束使用以下的方法将约束重新命名使用以下的方法将约束重新命名:ALTER TABLE employeesRENAME CONSTRAINT emp_dept_fkTO employees_dept_fk;11-56使用使用 EXCEPTIONS 表表通过运行通过运行utlexpt1.sql脚本来建立脚本来建立EXCEPTIONS表。表。执行执行 ALTER TABLE 语句并带有语句并带有EXCEPTIONS 选项。选项。在在EXCEPTIONS上使用子查询来锁定无效的数据行。上使用子查询来锁定无效的数据行。改正错误。改正错误。重新执行重新执行 ALTER TABLE语句语句开启开启约束。约束。11-57获得约束的信息获得约束的信息通过查询以下视图可以获取有关约束的信息通过查询以下视图可以获取有关约束的信息: DBA_CONSTRAINTSDBA_CONS_COLUMNS
