《Oracle对象管理》PPT课件.ppt

第十一讲 Oracle对象管理,本章学习目标 表、索引和约束是Oracle数据库管理的重要对象。本章将介绍它们的概念、分类、创建及管理方法。,11.1 表 11.2 索引 11.3 完整性约束,11.1 表,11.1.1 普通表 11.1.2 索引组织表（IOT） 11.1.3 分区 11.1.4 簇集 11.1.5 表的存储管理 11.1.6 表信息查询,不同类型的表,普通表,分区表,11.1.1 普通表,存储 创建表时，Oracle会自动从指定的表空间中为新建的表创建一个数据段，而该表的所有数据都会存放到相应的表段中。 ROWID ROWID用于惟一标识表行。它间接地给出了表行的物理存放位置，是定位表行最快速的方式。,ROWID格式,OOOOOO,BBBBBB,FFF,RRR,数据对象号,相对文件号,行号,块号,数据对象编号需要32 位，相关文件编号需要10 位，块编号需要22，位行编号需要16 位，加起来总共是80 位或10 个字节,数据文件的最大尺寸？ 32bit-object number-， 每个数据库最多有4G个对象 10bit-file number-， 每个数据库最多有1024个文件 22bit-block number-， 每个文件最多有4M个BLOCK 16bit-row number-， 每个BLOCK最多有64K个ROWS,建表并指定存储参数,CREATE TABLE schema.table (column datatype,column datatype) TABLESPACE tablespace PCTFREE integer PCTUSED integer INITRANS integer MAXTRANS integer STORAGE storage-clause LOGGING| NOLOGGING CACHE | NOCACHE,PCTFREE和PCTUSED PCTFREE用于指定块中必须保留的最小空闲空间比例； PCTUSED用于指定当数据块达到PCTFREE参数的限制之后，数据块能够被再次使用前，已占用的存储空间必须低于的比例 INITRANS和MAXTRANS 在创建表时，Oracle将在表中每个数据块的头部空间中分配可以存储INITRANS个事务条目的空间。MAXTRANS表明一个块所允许的最大并发事务数目,在STORAGE子句中可以设置下面6个存储参数： INITIAL，NEXT，PCTINCREASE， MINEXTENTS，MAXEXTENTS， BUFFER_POOL：指定表的数据块的缓存池。小且经常被查询的表存放在KEEP中；大却很少被访问的表存储在RECYCLE里；负责存储在DEFAULT里 LOGGING和NOLOGGING： 表的创建操作（包括通过查询创建表时的插入记录操作）是否记录到重做日志中。,建表例子,CREATE TABLE employees( empno NUMBER(4), last_name VARCHAR2(30) deptno NUMBER(2) PCTFREE 20 PCTUSED 50 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE data01;,复制表例子,CREATE TABLE test STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) NOLOGGING PARALLEL TABLESPACE USERS AS SELECT * FROM USER_OBJECTS;,11.1.2 索引组织表（IOT）,一般情况下，表与索引数据分别存放在表段和索引段中。但索引组织表（IOT）比较特殊，它将表的数据和索引数据存储在一起，即以B树索引的方式来组织表中的数据。,非键列,键列,行头,普通表及其索引,索引组织表,创建索引组织表,在CREATE TABLE语句中必须显式地指定organization index关键字。 在索引组织表中必须建立一个primary key主码约束。 create table employees( empno number(5) primary key, ename varchar2(15) not null, sal number(7,2), job varchar2(10) ) organization index tablespace users;,11.1.3 分区,分区是指将一张大表的数据进行物理划分，并最终将其数据放到几个相对较小的表分区段中。 当执行SQL语句访问分区表时，系统可以直接访问某个表分区段，而不需要访问整张表的所有数据，从而降低磁盘I/O，提高系统性能。,范围分区,范围分区是按照分区字段中值的范围来对表进行分区。范围分区通常用于分区字段是日期类型的表。,create table sales( customer_id number(3), sales_amount number(10,2), sales_date date) partition by range (sales_date)( partition pl values less than (TO_DATE (01012001, MMDDYYYY) tablespace tb2001, partition p2 values less than (TO_DATE (01012002, MMDDYYYY) tablespace tb2002, partition p3 values less than (TO_DATE (01012003, MMDDYYYY) tablespace tb2003, partition p4 values less than (TO_DATE (01012004, MMDDYYYY) tablespace tb2004 );,当在分区表上执行INSERT操作时，系统会自动按照sales_date值的范围将数据插入到相应的分区段上 当执行SELECT、UPDATE、DELETE操作时，如果在WHERE子句中引用了分区列，那么Oracle会自动在相应分区上执行操作，从而降低I/O操作的次数，进而提高性能。,列表分区,如果分区字段的值并不能划分范围（非数字或日期数据类型），同时分区字段的取值范围只是一个包含少数值的集合，那么可以对表进行列表分区。 在进行列表分区时，需要为每个分区指定一个取值列表，分区字段值处于同一个取值列表中的记录被存储在同一个分区中。 列表分区适用于那些分区字段是一些无序的或者无关的取值集合的表。,create table sales_by_region( deptno number, dname varchar2 (20), quarterly_sales number (10,2), city varchar2(10) partition by list (city)( partition pl values (北京, 上海) , partition p2 values (重庆, 广州) , partition p3 values (南京, 武汉) );,散列分区,在进行范围分区或列表分区的时候，有时由于用户无法对各个分区中可能具有的记录数目进行预测，可能会产生不平衡分区情况。这时应创建散列分区 散列分区是指按照Oracle所提供的散列(HASH)函数来计算列值数据，并最终按照函数结果来分区数据。,create table product( product_id number(6), description varchar2(30) partition by hash (product_id) (partition pl tablespace locall, partition p2 tablespace local2 );,组合分区,组合分区实际上组合了范围分区和散列分区，它首先按照列值范围从逻辑上进行范围分区，然后在每个范围分区的基础上再按照散列函数进行散列分区。,create table sales_order( order_id number, order_date date, product_id number, quantity number) partition by range (order_date) subpartition by hash (product_id) subpartitions 2 store in(users,userl) (partition p1 values less than( 01-APR-2001), partition p2 values less than (01-JUL-2001), partition p3 values less than (01-OCT-2001), partition p4 values less than (01-JAN-2002);,其中，STORE IN用于指定散列分区所在表空间。 在执行了上述命令之后，首先按照ORDER_DATE列值进行范围分区，此时建立四个逻辑上的范围分区。然后按照PRODUCT_ID列值进行散列分区，并将逻辑上的四个范围分区最终转化成8个物理上的散列分区段。,11.1.4 簇,一般情况下，建表时Oracle会为每个表分配相应的表段。,产生的问题,假定用户经常需要执行类似于“SELECT dname,ename,sal FROM dept,emp WHERE dept.deptno=emp.deptno AND dept.deptno=10”的连接查询语句来检索部门及其雇员的相关信息，那么至少需要2次的I/O操作,解决方法,Oracle利用簇将多个表的相关字段聚簇在相同的数据块中。,簇的建立（第一步）,建簇 create cluster dept_emp(deptno number (3) pctfree 20 pctused 60 size 500 tablespace users storage ( initial 200K next 200K minextents 3 pctincrease 0 maxextents 50 ); 注：size指一个簇键和它相关行所需的平均字节数,簇的建立（第二步）,建簇表 create table dept( deptno number(3) primary key, dname varchar2(14), loc varchar2(13) )cluster dept_emp(deptno); 注：不能指定STORAGE子句和块空间使用参数,簇的建立（第三步）,在插入数据之前必须首先建立簇索引 create index dept_emp_idx on cluster dept_emp tablespace indx storage( initial 20K next 20K pctincrease 0 );,11.1.5 表的存储管理,分配空间 默认情况下，Oracle会根据存储参数设置自动为表分配区并计算大小。若需要指定大小的区，则可使用ALTER TABLE ALLOCATE EXTENT语句以手工方式为表分配存储空间。 alter table department allocate extent (size 500K datafile e:testusers2.dbf);,释放空间 ALTER TABLE schema.table DEALLOCATE UNUSED KEEP integerK|M 注：KEEP指定在高水位标记（HIGH WATER MARK，使用段存储的最高历史记录）以上应该保留的字节数。如果使用上述命令时没有KEEP子句，Oracle将回收高水位标记以上所有未使用空间。,Free space after delete,Unused block,High water mark,Extent ID 0 1 2 3 4,Used block,High Water Mark,After inserts,After deletes,Free space after delete,Unused block,High water mark,释放之前,Used block,释放未使用的空间,ALTER TABLE scott.employees DEALLOCATE UNUSED;,释放之后,0 1 2 3 4,修改存储参数 在表创建之后，可以使用ALTER TABLE语句来改变表的块参数设置和部分存储参数设置（INITIAL和MINEXTENTS不能改）。 ALTER TABLE schema.table storage-clause PCTFREE integer PCTUSED integer INITRANS integer MAXTRANS integer,表的重组 如果发现一个表的数据段具有不合理的区分配方式，但是又不能通过别的方法来调整（改变存储参数不会影响到已经分配的区），可以考虑将该表移到一个新的数据段中。用户可以为新的数据段重新设置存储参数，以便符合表的存储需求。,例1：将EMPLOYEES表移动到同一个表空间的新数据段中： alter table employees move storage(initial 20K next 40K minextents 2 maxextents 20 pctincrease 0);,例2：将EMPLOYEES表移动到表空间USERS02的新数据段中： alter table employees move tablespace users02 storage(initial 20K next 40K minextents 2 maxextents 20 pctincrease 0);,11.1.6 表信息查询,DBA_OBJECTS OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID CREATED,DBA_SEGMENTS OWNER SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK,DBA_TABLES OWNER TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE CACHE BLOCKS EMPTY_BLOCKS CHAIN_CNT,11.2 索引,11.2.1 B-Tree索引 11.2.2 位图索引 11.2.3 反键索引 11.2.4 索引管理 11.2.5 索引信息查询,11.2.1 B-Tree索引,索引项标题,关键字列长,关键字列值,ROWID,根,分支,叶,索引项,建立通常的B-Tree 索引,CREATE UNIQUE INDEX schema. index ON schema. table (column ASC | DESC , column ASC | DESC .) TABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING| NOLOGGING NOSORT ,建立B-Tree索引的例子,CREATE INDEX test_idx ON test(a) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;,建立索引的原则,平衡查询和DML 需求 放在一个单独的表空间内（非 SYSTEM, RBS, TABLE） 使用统一的区大小(经验值： 五块的倍数或表空间MININUM EXTENT 大小的倍数） 考虑对大索引使用NOLOGGING 如果新的关键字值有可能位于当前范围内, 则设置高PCTFREE,11.2.2 位图索引,r o w 1 2 3 4,Name Joe Jane John James,M_Status Single Married Divorced Married,State PA CA CA PA,Gender M F M M,M_Status-IDX,Single Married Divorced,State-IDX,CA PA,Gender-IDX,M F,1 0 0 0 0 1 0 0 0 0 1 0,0 1 1 0 1 0 0 1,1 0 1 1 0 1 0 0,Select count(*) from customers where M_Status = married AND State = CA AND Gender = M,B 树 适合高基数的列 更新关键字列的费用相对较低 使用 OR 谓词的查询效率低 对 OLTP 有用,位图 适合低基数的列 更新关键字列的费用非常昂贵 使用 OR 谓词的查询效率高 对数据仓库有用,B-Tree树与位图索引的比较,建立Bitmap索引,CREATE BITMAP INDEX schema. index ON schema. table (column ASC | DESC , column ASC | DESC .) TABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING| NOLOGGING NOSORT 注意，Bitmap 不能是 unique 的,建立Bitmap索引的例子,SQL CREATE BITMAP INDEX test_bm_idx 2 ON test(c) 3 PCTFREE 30 4 STORAGE(INITIAL 200K NEXT 200K 5 PCTINCREASE 0 MAXEXTENTS 50) 6 TABLESPACE indx; 索引已创建。,11.2.3 反键索引,KEY ROWID EMPNO (BLOCK# ROW# FILE#) - - 1257 0000000F.0002.0001 2877 0000000F.0006.0001 4567 0000000F.0004.0001 6657 0000000F.0003.0001 8967 0000000F.0005.0001 9637 0000000F.0001.0001 9947 0000000F.0000.0001 . . . .,Index on EMP (EMPNO),EMP table,EMPNO ENAME JOB . - - - 7499 ALLEN SALESMAN 7369 SMITH CLERK 7521 WARD SALESMAN . 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER . . . . . . . .,适合唯一索引 反键索引有助于把本来相连的列值分散到索引的不同叶块，这样减少了I/O 的读取个数，从而提高了性能。,建立反键索引,CREATE UNIQUE INDEX schema. index ON schema. table (column ASC | DESC , column ASC | DESC .) TABLESPACE tablespace PCTFREE integer INITRANS integer MAXTRANS integer storage-clause LOGGING| NOLOGGING REVERSE （注意，这里不能使用 NOSORT）,建立反键索引的例子,SQL CREATE UNIQUE INDEX test_rsidx 2 ON test(b) REVERSE 3 PCTFREE 30 4 STORAGE(INITIAL 200K NEXT 200K 5 PCTINCREASE 0 MAXEXTENTS 50) 6 TABLESPACE indx; 索引已创建。 SQL,11.2.4 索引管理,修改索引存储参数设置 可以使用ALTER INDEX命令修改其存储参数。存储参数INITIAL不能修改，而修改其他存储参数只对新分配的区起作用。 alter index test_idx storage(next 200k maxextents 50);,SQL ALTER INDEX test_bm_idx 2 ALLOCATE EXTENT (SIZE 200K 3 DATAFILE E:ORACLEORA92TESTINDX01.DBF); 索引已更改。,SQL ALTER INDEX test_bm_idx 2 DEALLOCATE UNUSED; 索引已更改。 SQL 注意：释放HWM之上的,分配释放存储空间,11.2.5 索引信息查询,DBA_INDEXES OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENESS TABLESPACE_NAME LOGGING STATUS,DBA_IND_COLUMNS INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH,11.3 完整性约束,Application code,Integrity constraint (首选),Database trigger,Table,Data,11.3.1 约束的类型 11.3.2 约束的定义 11.3.3 约束的修改 11.3.4 约束的删除 11.3.5 约束的查询,11.3.1 约束的类型,约束 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK,说明 列不能包含空值 列或列的组合 的值唯一 列或列的组合 为表的主键 该完整性约束保证：对于表的每一行，外键的值必须与父表的主键值匹配， 表中每一行必须满足的特殊条件,延迟约束（Deferred Constraints）,DML 语句,检查非延迟约束,COMMIT,检查延迟约束,默认情况下，Oracle在一条SQL语句执行完毕后进行约束检查，但是也可以将约束检查的时间延迟到一个事务结束时进行。以保证事务的执行过程不会因为出现违反约束的情况而中断。 不可延迟约束：在定义约束时，默认使用NOT DEFERRABLE关键字 可延迟约束：如果在定义约束时显式地指定了DEFERRABLE关键字，则创建的约束是可延迟的。,11.3.2 约束的定义,建立表时定义约束,SQL CREATE TABLE emp( 2 id NUMBER(7) 3 CONSTRAINT employee_id_pk PRIMARY KEY 4 DEFERRABLE 5 USING INDEX 6 STORA