第十一讲 Oracle 对象管理

第十一讲 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格式 FFF OOOOOO BBBBBB 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值的范围将数据插入到相应的分区段上当在分区表上执行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个物理上的散列分区段。

DEPT EMP DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON DEPTNO DNAME SAL 20 SMITH 800 10 CLARK 2450 10 KING 5000 20 SCOTT 2000 30 DEVE 1800 EMP DEPT 11.1.4 簇 • 一般情况下，建表时Oracle会为每个表分配相应的表段。

产生的问题 • 假定用户经常需要执行类似于“SELECT dname,ename,sal FROM dept,emp WHERE dept.deptno=emp.deptno AND dept.deptno=10”的连接查询语句来检索部门及其雇员的相关信息，那么至少需要2次的I/O操作

Deptno（簇键） 10 ACCOUNTING NEW YORK CLARK 2450 KING 5000 20 RESEARCH DALLAS SMITH 800 SCOTT 2000 簇DEPT_EMP 解决方法 • 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:\test\users2.dbf');

释放空间 ALTER TABLE [schema.]table DEALLOCATE UNUSED [KEEP integer[K|M]] 注：KEEP指定在高水位标记（HIGH WATER MARK，使用段存储的最高历史记录）以上应该保留的字节数。如果使用上述命令时没有KEEP子句，Oracle将回收高水位标记以上所有未使用空间。

Extent ID 0 1 2 3 4 High Water Mark After inserts After deletes High water mark Extent ID 0 1 2 3 4 Used block Unused block Free space after delete

释放未使用的空间 释放之前 ALTER TABLE scott.employees DEALLOCATE UNUSED; High water mark 0 1 2 3 4 释放之后 Used block Unused block Free space after delete

修改存储参数 在表创建之后，可以使用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_TABLES OWNER TABLE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE CACHE BLOCKS EMPTY_BLOCKS CHAIN_CNT DBA_SEGMENTS OWNER SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

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 位图索引 M_Status-IDX r o w 1 2 3 4 1 0 0 0 0 1 0 0 0 0 1 0 Single Married Divorced Name Joe Jane John James M_Status Single Married Divorced Married State PA CA CA PA Gender M F M M State-IDX 0 1 1 0 1 0 0 1 CA PA Gender-IDX 1 0 1 1 0 1 0 0 M F Select count(*) from customers where M_Status = married AND State = CA AND Gender = M

B-Tree树与位图索引的比较 B 树适合高基数的列更新关键字列的费用相对较低使用 OR 谓词的查询效率低对 OLTP 有用位图适合低基数的列更新关键字列的费用非常昂贵使用 OR 谓词的查询效率高对数据仓库有用

建立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; • 索引已创建。

第十一讲 Oracle 对象管理