Oracle企业资产管理.ppt

第七章管理Oracle对象 管理表概览表是最基本的数据库对象 它用于存储用户数据 关系数据库的所有操作最终都是围绕用户表进行的 在Oracle数据库中 按照存储方式的不同 表可以分为普通表 分区表 索引组织表以及簇表等四种表 1 表结构表是Oracle数据库数据存储的基本单元 在表中是通过行和列来组织数据的 一张表一般都具有多个列 或者称为字段 每个字段都具有特定的属性 包括字段名 字段数据类型 字段长度 约束 默认值等 这些属性在表创建时即被确定 而表的每一行存放一条信息 2 基本的数据类型当建立表时 不仅要指定表名 列名 而且要根据情况为列选择合适的数据类型以及长度 下面介绍一些常用的Oracle数据类型 CHAR N 该数据类型用于定义固定长度的字符串 其最大长度为2000字节 假定定义COLA列为CHAR 100 并且该列的数据为 ACCESS 这时虽 ACCESS 只有六个字符 但COLA列仍将占用100字节的空间 VARCHAR2 N 该数据类型用于定义可变长度的字符串 其最大长度为4000字节 假定定义COLA列为VARCHAR2 100 并且该列的数据为 ACCESS 那么COLA列将占用6字节的空间 所以使用VARCHAR2类型可以节省空间 但CHAR类型存取速度更快 NUMBER P S 该数据类型用于定义数字类型的数据 其中P是精度 表示数字的总位数 而S是刻度范围 表示小数点后的位数 精度和刻度范围都是可选的 但若指定了刻度范围 那么必须指定精度 假定定义SAL列为NUMBER 4 3 若该列的数据为123 4567 则存储错误 因为超出了精度范围 若该列的数据为1 234567 则实际存储的数据为1 235 因为当被指派值超出了刻度范围时 存储值按照刻度范围指定的数字位的位数进行舍入 另外 定义整数还可以直接使用INT数据类型 DATE该数据类型用于定义日期时间数据 其长度为7个字节 RAW N 该数据类型用于定义二进制数据 其最大长度为2000字节 通常用来存储小型二进制数据 存储大对象的数据类型Oracle为存储大对象 LOB 提供了六种数据类型 其中 CLOB和LONG用于存储大型的 固定宽度字符数据 BLOB和LONGRAW用于存储非结构化数据 如二进制图象 NCLOB用于存储大型的 固定宽度字符集数据 BFILE用于存储操作系统文件中的非结构化数据 3 数据的存储方式当创建表时 Oracle会自动从指定的表空间中为新建的表创建一个数据段 而该表的所有数据都会存放到相应的表段中 表的行数据存储在数据块中 如果一个块的大小足够容纳一条记录 Oracle就将一条记录完整的存储在一个数据块中 一般情况 记录按列定义顺序来存放 但若使用LONG或LONGRAW类型 那么它们的数据总是放在记录的尾部 如果表中某字段允许为NULL 若NULL值字段位于非NULL值字段的中间 则需使用1字节的空间来存储NULL值字段的长度 若NULL值字段位于一条记录的末尾 将不需要任何存储空间来存储NULL值 因此 在定义表时候 应当将可能包含NULL值的字段放在字段列表的末尾 这样可以节省存储空间 4 ROWIDROWID用于惟一标识表行 当执行INSERT操作时 服务器进程会将数据插人到表段的相应数据块中 并且Oracle会生成惟一的ROWID对应于该行数据 ROWID间接地给出了表行的物理存放位置 它是定位表行最快速的方式 ROWID占用10个字节的存储空间 而显示结果为18个字符 具体格式如下 数据对象号相对文件号数据块号行号 数据对象号 表的惟一对象标识号 当建立表时 Oracle会为该表分配惟一的数据对象号 相对文件号 表空间内数据文件的惟一标识号 块号 行所在数据块的位置 行号 行在块内的位置 因为创建了一个表就创建了一个段 而一个段只能驻留在一个表空间中 所以使用数据对象编号 Oracle服务器可以确定表所在的表空间 又因为表空间的数据都存放在数据文件中 使用相对文件编号Oracle可以确定存放表数据的数据文件 数据文件用数据块来存储数据 使用数据块编号可知表的某一行数据存放在哪个数据块中 而一个数据块可能存放多行数据 利用行号就可以定位表中的任一行了 ROWID是表的伪列 用户在查看表的结构时不会看到ROWID字段 但和其它字段一样 可以在执行SELECT操作时直接引用 但是因为用户无法直接读懂ROWID内容 所以在引用ROWID时通常使用DBMS ROWID将其转变为可读取的内容 selectdeptno dbms rowid rowid relative fno rowid dbms rowid rowid block number rowid dbms rowid rowid row number rowid 行位置fromdept 7 1 2建表建表是使用createtable命令完成的 执行该命令要求用户必须具有createtable系统权限 如果要在其他用户模式中建表 则要求用户必须具有createanytable系统权限 当建立表时 Oracle会为该表分配相应的表段 因为表段所需空间是从表空间上分配的 所以要求表的所有者必须要在表空间上具有相应的空间配额或具有unlimitedtablespace系统权限 1 建立普通表普通表是存储用户数据最常用的方式 当建立普通表时 Oracle会自动为该表建立相应的段 并且段的名称与表名完全相同 而且段的数据只能存放在一个表空间中 创建普通表的语法如下 CREATETABLE schema table columndatatype columndatatype TABLESPACEtablespace PCTFREEinteger PCTUSEDinteger INITRANSinteger MAXTRANSinteger STORAGEstorage clause LOGGING NOLOGGING CACHE NOCACHE 现对命令中各参数说明如下 TABLESPACE 标识要在其中创建表的表空间 如果创建表时没有显式的指定表所处的表空间 则表被创建在当前用户的默认表空间中 PCTFREE和PCTUSED PCTFREE参数用于指定块中必须保留的最小空闲空间比例 PCTUSED参数用于指定当数据块达到PCTFREE参数的限制之后 这时数据块被标记为不可用 数据块能够被再次使用前 已占用的存储空间必须低于的比例 INITRANS和MAXTRANS INITRANS与MAXTRANS参数用于指定针对同一个块所允许的并发事务数目 每当一个DML事务访问表中的一个数据块时 该事务会在数据块的块头部 BlockHeader 中保存一个条目 用于标记该事务正在使用这个数据块 当该事务结束时 它所对应的条目被删除 在创建表时 Oracle将在表中每个数据块的头部空间中分配可以存储INITRANS个事务条目的空间 这部分存储空间是永久性的 只能用来存储事务条目 当一个DML事务访问这个数据块时 Oracle首先将该事务的条目存储在块头部空间中 当块头部空间已经存储了INITRANS个事务条目后 再没有多余的空闲空间来存储其他的事务条目了 这时若还有别的事务要访问这个数据块 Oracle将在数据块的空闲空间中为事务条目分配存储空间 如果在块中还有空闲空间的话 这部分空闲空间是动态分配的 回收以后可以用于存储其他数据 能够在空闲空间中存储的事务条目数量等于MAXTRANS参数值减去INITRANS参数的值 STORAGE 用于指定段的存储参数 若不指定存储参数 那么Oracle会使用表空间的默认存储参数 在STORAGE子句中可以设置下面6个存储参数 INITIAL 为表的数据段分配的第一个区的大小 NEXT 为表的数据段分配的第二个区的大小 PCTINCREASE 指定从第二个区开始 为表的数据段分配的区的大小增加比例 即每个区的大小等于前一个区的大小乘以 1 PCTINCREASE 100 如果表处于本地管理方式的表空间中 则该参数被忽略 MINEXTENTS 允许为表的数据段分配的最小区数目 MAXEXTENTS 允许为表的数据段分配的最大区数目 如果表处于本地管理方式的表空间中 则该参数被忽略 BUFFER POOL 指定表的数据块的缓存池 LOGGING和NOLOGGING 使用了LOGGING 则表的创建操作 包括通过查询创建表时的插入记录操作 都将记录到重做日志中 若用NOLOGGING 则表的创建操作不会被记录到重做日志中 默认情况将使用LOGGING子句 NOLOGGING子句适合于通过查询创建表的情况 CACHE和NOCACHE 在创建表时默认使用NOCACHE子句 但对于比较小又经常查询的表 可以使用CACHE子句 下面是一个在data表空间上建立employee表的例子 createtableemployee idnumber 7 last namevarchar2 25 dept idnumber 7 pctfree20pctused50storage initial200knext200kpctincrease0maxextents50 tablespacedata 2 复制表使用CREATETABLE命令不仅可以建立表结构 而且还可以将已存在表的结构和数据复制到另一张新表中 另外为了尽快复制表的数据 复制表时可以指定PARALLEL选项和NOLOGGlNG选项 示例如下 createtablenew empparallel2nologgingasselect fromscott emp 其中 paralleldegree用于指定执行并行操作 parallel2表示由两个并行服务器进程执行数据加载操作 另外使用Nologging 在重做日志中没有记录下创建表的操作 3 建立临时表通过createtable语句创建的表是永久性的表 即其中的记录可以一直保存下来 与之对应 在Oracle中还可以创建临时表 TemporaryTable 与普通表不同 临时表中的数据在使用完毕后自动删除 使用完毕 有两种情况 事务结束和会话结束 在创建临时表时若使用了oncommitdeleterows子句 则说明临时表是事务级别的 这时Oracle将在每次提交事务时对临时表进行删减操作 即删除表中的所有数据 若使用了oncommitpreserverows子句 则说明临时表是会话级别的 这时Oracle将直到会话终止时才对临时表进行删减操作 下面语句就建立了名称为employee temp的临时表 该临时表将在每次事务提交时进行删减操作 createglobaltemporarytableemployee temponcommitdeleterowsasselect fromscott emp 4 建立索引组织表一般情况下 表与索引数据分别存放在表段和索引段中 但索引组织表 IOT 比较特殊 它将表的数据和索引数据存储在一起 即以B树索引的方式来组织表中的数据 非键列 键列 行头 普通表及其索引 索引组织表 要创建索引组织表 必须在CREATETABLE语句中显式地指定organizationindex关键字 另外 在索引组织表中必须建立一个primarykey主码约束 下面语句就创建了一个索引组织表employees createtableemployees empnonumber 5 primarykey enamevarchar2 15 notnull salnumber 7 2 jobvarchar2 10 organizationindextablespaceusers 在索引组织表中 如果要获得对常用字段更快的访问速度 可以应用 溢出 存储功能 将表中的不常访问的非主码不再存储在B树的叶节点中 而是存储在一个具有堆组织方式的溢出存储区中 对于大型的索引组织表 使用溢出存储能够大大减少索引组织表所占用的存储空间 同时又可提高对常用字段的查询效率 如果要启用溢出存储功能 必须在创建索引组织表时指定OVERFLOW子句 此外还需使用INCLUDING子句或PCTTHRESHOLD子句来设置溢出存储的方式 如下所示 createtablesales info idnumber 6 primarykey customer namevarchar 30 sales amountnumber 10 2 sales datedate remarkvarchar2 200 organizationindexpctthreshold20includingremarkoverflowtablespaceusers 其中 PCTTHRESHOLD指定在数据块中为主键列和部分非主键列所预留空间的百分比 如上例所示 假定数据块剩余空间已经低于20 那么Oracle会将INCLUDING子句后所有列的数据存放到溢出段 而INCLUDINGremark则指如果数据块剩余空间低于PCTTHRESHOLD 那么Oracle会将该子句列remark后的所有列数据存放到溢出段 OVERFLOWTABLESPACE指定了溢出段所在的表空间 5 建立分区表分区是指将一张大表的数据进行物理划分 并最终将其数据放到几个相对较小的表分区段中 当执行SQL语句访问分区表时 系统可以直接访问某个表分区段 而不需要访问整张表的所有数据 从而降低磁盘I O 提高系统性能 Oracle提供了范围分区 散列分区 列表分区以及组合分区四种分区方法 范围分区范围分区是按照分区字段中值的范围来对表进行分区 是最常用的分区类型 范围分区通常用于分区字段是日期类型的表 假定一张销售表年数据总量达到10GB 每个季度平均2 5GB 如果使用普通表存储数据 那么10G数据会存放到一个表段SALES中 那么在统计一季度销售数据时需要扫描10GB数据 而如果使用表分区段 那么可以将一 二 三 四季度数据分别存放到不同表分区段中 此时统计一季度销售数据只需要扫描2 5GB数据 显然 使用表分区段可以大大降低I O次数 并提高I O性能 下面以建立SALES表为例 说明使用范围分区建立分区表的方法 示例如下 createtablesales customer idnumber 3 sales amountnumber 10 2 sales datedate partitionbyrange sales date partitionplvalueslessthan 01 APR 2001 partitionp2valueslessthan 01 JUL 2001 partitionp3valueslessthan 01 OCT 2001 partitionp4valueslessthan 01 JAN 2002 当在分区表上执行INSERT操作时 系统会自动按照sales date值的范围将数据插入到相应的分区段上 例如 insertintosalesvalues 1 28500 25 JAN 2001 insertintomalesvalues 2 30500 25 MAY 2001 当执行了上述INSERT语句之后 会将第一条数据插入到分区P1上 而第二条数据被插入到分区P2上 当执行SELECT UPDATE DELETE操作时 如果在WHERE子句中引用了分区列 那么Oracle会自动在相应分区上执行操作 从而降低I O操作的次数 进而提高性能 列表分区如果分区字段的值并不能划分范围 非数字或日期数据类型 同时分区字段的取值范围只是一个包含少数值的集合 那么可以对表进行列表分区 在进行列表分区时 需要为每个分区指定一个取值列表 分区字段值处于同一个取值列表中的记录被存储在同一个分区中 列表分区适用于那些分区字段是一些无序的或者无关的取值集合的表 下面以建立sales by region表为例 说明使用列表分区建立分区表的方法 示例如下 createtablesales by region deptnonumber dnamevarchar2 20 quarterly salesnumber 10 2 cityvarchar2 10 partitionbylist city partitionplvalues 北京 上海 partitionp2values 重庆 广州 partitionp3values 南京 武汉 其中 PARTITIONBYLIST column 指定分区方法为列表分区 column指定分区列名 当执行了上述命令之后 Oracle会为表sales by region建立三个分区段 当在分区表上执行INSERT操作时 系统会自动按照city列的值将数据插入到相应的分区段上 例如 insertintosales by regionvalues 10 SALES 20800 上海 insertintosales by regionvalues 10 SALES 24800 重庆 insertintosales by regionvalues 10 SALES 28800 武汉 当执行了上述INSERT语句之后 会将第一条数据插入到分区P1上 第二条数据则会被插入到分区P2上 而第三条数据则会被插入到分区P3上 当执行SELECT UPDATE DELETE操作时 如果WHERE子句引用了分区列 那么Oracle会自动在相应分区上执行操作 从而降低I O操作的次数 进而提高性能 散列分区在进行范围分区或列表分区的时候 有时由于用户无法对各个分区中可能具有的记录数目进行预测 可能会产生某个分区中记录很多 而某个分区中记录很少的不平衡分区情况 这时应创建散列分区 散列分区是指按照Oracle所提供的散列 HASH 函数来计算列值数据 并最终按照函数结果来分区数据 下面以建立分区表PRODUCT为例 说明使用散列分区建立分区表的方法 示例如下 createtableproduct product idnumber 6 descriptionvarchar2 30 partitionbyhash product id partitionpltablespacelocall partitionp2tablespacelocal2 其中 PARTITIONBYHASH column 指定分区方法为散列分区 column指定分区列名 当在散列分区表上插入数据时 系统会自动在分区列PRODUCT ID上使用散列函数进行运算 并根据运算结果将数据均匀地放置到不同分区 组合分区顾名思义 组合分区实际上组合了范围分区和散列分区 它首先按照列值范围从逻辑上进行范围分区 然后在每个范围分区的基础上再按照散列函数进行散列分区 当不同范围的数据分布比较均匀时 Oracle建议直接使用范围分区 对于某些表来说 尽管数据是按照一定范围分布的 但因为不同范围的数据分布不均匀 所以此时可以使用组合分区来有效地分布表的数据 createtablesales order order idnumber order datedate product idnumber quantitynumber partitionbyrange order date subpartitionbyhash product id subpartitions2storein users userl partitionp1valueslessthan 01 APR 2001 partitionp2valueslessthan 01 JUL 2001 partitionp3valueslessthan 01 OCT 2001 partitionp4valueslessthan 01 JAN 2002 其中 STOREIN用于指定散列分区所在表空间 在执行了上述命令之后 首先按照ORDER DATE列值进行范围分区 此时建立四个逻辑上的范围分区 然后按照PRODUCT ID列值进行散列分区 并将逻辑上的四个范围分区最终转化成8个物理上的散列分区段 当给该组合分区表插入数据时 首先按照order date列值区逻辑确定其所在区 然后用散列函数计算product id的散列值 最终确定数据应该放到哪个分区中 6 建立簇表一般情况下 建立表时Oracle会为该表分配相应的表段 例如 当建立表DEPT和EMP时 Oracle会分别为这两张表分配表段DEPT EMP 并且它们的数据会分别存放到这两个表段中 表DEPT的所有数据存放在表段DEPT中 而EMP表的所有数据则存放在表段EMP中 假定用户经常需要执行类似于 SELECTdname ename salFROMdept empWHEREdept deptno emp deptnoANDdept deptno 10 的连接查询语句来检索部门及其雇员的相关信息 那么至少需要2次的I O操作 因为表DEPT和EMP的数据分别存放在两个表段中 这时 为了降低硬盘I O操作的开销 可以把这两张表的数据组织到簇中 簇是一种用于存储表中数据的可选方法 在一个簇中 Oracle将多个表的相关字段聚簇在相同的数据块中 比如 EMP表和DEPT表都具有DEPTNO字段 DEPTNO字段就是这两个表的相关字段 簇键 如果将EMP表和DEPT表聚簇在一起 那么Oracle会按照部门的DEPTNO在物理上存储两个表的所有记录 创建簇使用CREATECLUSTER语句 执行该命令要求用户要有CREATECLUSTER系统权限 如果要在其他用户模式中建立簇 则要有CREATEANYCLUSTER系统权限 当建立簇时 Oracle会为该簇分配相应的簇段 因为簇段所需空间是从表空间上分配的 所以要求簇所有者必须要在表空间上具有相应的空间配额或具有UNLIMITEDTABLESPACE系统权限 示例如下 createclusterdept emp deptnonumber 3 pctfree20pctused60size500tablespaceusersstorage initial200Knext200Kminextents3pctincrease0maxextents50 创建簇后 还要建簇表 建立簇表要求用户必须具有CREATETABLE系统权限 如果要在其他用户模式中建立簇表 则必须具有CREATEANYTABLE系统权限 另外 因为簇表数据是放在簇段中的 所以用户不需要任何表空间配额或UNLIMITEDTABLESPACE系统权限 为了将表组织到簇中 在建表时必须指定CLUSTER子句 建立簇表的示例如下 createtabledept deptnonumber 3 primarykey dnamevarchar2 14 locvarchar2 13 clusterdept emp deptno 当执行了上述命令后 将表DEPT增加到簇DEPT EMP中了 需要注意的是 当建立簇表时不能指定STORAGE子句和块空间使用参数 当建立了簇和簇表之后 在插入数据之前必须首先建立簇索引 否则会显示错误信息 createindexdept emp idxonclusterdept emptablespaceindxstorage initial20Knext20Kpctincrease0 7 1 3修改表1 增加和删除字段如果目前的字段不能够完整地标识表的所有属性 那么通过增加字段可以间接地增加表的属性 使用ALTERTABLE命令可以给表增加字段 示例如下 altertabledepartmentaddphonevarchar2 10 altertabledepartmentaddmanagervarchar2 10 使用ALTERTABLE DROP语句能够删除删除表中不再需要使用的字段 但是注意不能删除表中所有的字段 也不能删除SYS模式中任何表中的字段 如果仅需要删除一个字段 必须在字段名之前指定COLUMN关键字 比如 下列语句将删除EMPLOYEES表中的AGE字段 altertableemployeesdropcolumnage 如果要在一条语句中删除多个字段 则需要将删除的字段名放在括号中 相互间用逗号隔开 并且不能用COLUMN关键字 比如 altertableemployeesdrop age sal 当删除字段时 如果该表包含了大量数据 那么删除列的时间就会很长 如果时间因素必须考虑 而字段也不再需要 那么你可以先将字段标记为UNUSED列 然后在适当时机删除该列的所有数据 标记列为UNUSED的方法如下 altertableempsetunusedcolumncomm 当将列标记为UNUSED之后 会从数据字典中删除该列的信息 并且在查看表结构时也不会看到该列的信息 但是该列的数据仍然存在 如果要删除UNUSED列的数据 则必须执行如下语句 altertableempdropunusedcolumnscheckpoint1000 其中CHECKPOINTl000用于指定每删除1000行发出一次检查点 以节省回滚段的空间使用 2 修改表的参数设置在表创建之后 可以使用ALTERTABLE语句来改变表的块参数设置和部分存储参数设置 语法如下 ALTERTABLE schema table storage clause PCTFREEinteger PCTUSEDinteger INITRANSinteger MAXTRANSinteger 如 利用下面的语句可以为EMPLOYEES表重新设置PCTFREE和PCTUSED参数 altertableemployeespctfree30pctused60 在表创建之后 不能再对INITIAL存储参数进行修改 但是可以修改其他的存储参数 altertableemployeesstorage next512Kpctincrease0maxextentsunlimited 3 重建表如果发现一个表的数据段具有不合理的区分配方式 但是又不能通过别的方法来调整 改变存储参数不会影响到已经分配的区 可以考虑将该表移到一个新的数据段中 用户可以为新的数据段重新设置存储参数 以便符合表的存储需求 比如 利用下面的语句可以将EMPLOYEES表移动到同一个表空间的新数据段中 altertableemployeesmovestorage initial20Knext40Kminextents2maxextents20pctincrease0 新的数据段可以在原来的表空间中 也可以在其他的表空间 比如 利用下面的语句可以将EMPLOYEES表移动到表空间USERS02的新数据段中 altertableemployeesmovetablespaceusers02storage initial20Knext40Kminextents2maxextents20pctincrease0 4 手工分配和释放空间默认情况下 Oracle会根据存储参数设置自动为表分配区并计算大小 若需要指定大小的区 则可使用ALTERTABLEALLOCATEEXTENT语句以手工方式为表分配存储空间 通过手工分配区 你可指定区的大小 也可控制将区分布到哪个数据文件上 另外当执行SQL loader装载数据时 如果表段的空间不足 则会导致Oracle为表段动态分配空间 这样会降低数据装载速度 为了避免区的动态分配 应该在执行数据装载操作前手工为表增加足够大的区 示例如下 altertabledepartmentallocateextent size500Kdatafile e test users2 dbf 使用ALTERTABLE命令不仅可以为表段分配空间 也可以释放表上多余的空间 如果表段实际占用空间多于所需空间时 你可以释放其所占用的多余空间 语法如下 ALTERTABLE schema tableDEALLOCATEUNUSED KEEPinteger K M KEEP指定在高水位标记 已经使用的存储空间和未使用的存储空间之间的分界线 以上应该保留的字节数 如果使用上述命令时没有KEEP子句 Oracle将回收高水位标记以上所有未使用空间 如果高水位标记所在的区小于MINEXTENTS的值 则Oracle释放MINEXTENTS以上的区 既默认情况下释放剩余空间后表段的区个数不会低于MINEXTENTS 而如果要释放MINEXTENTS下面的剩余空间 需要带有KEEP0选项 示例如下 altertabledepartmentdeallocateunused 5 分析表在Oracle中 利用ANALYZE语句可以对表 索引和簇进行分析 通过分析可以获得关于指定对象的状态和统计信息 并且能够对指定对象的存储格式进行验证 验证表的存储结构在ANALYZE语句中使用VALIDATESTRUCTURE子句 可以在分析过程中对表的存储结构的完整性进行验证 通过存储结构的验证 用户可以知道表中是否存在损坏的数据块 如果有损坏的数据块 则需删除该表并重建它 在验证表的存储结构时 Oracle会把表中包含损坏数据块的记录的ROWID插入到一个名为INVALID ROWS的表中 对EMPLOYEES表进行结构验证分析示例如下 analyzetableemployeesvalidatestructure 然后可查询INVALID ROWS表看是否有损坏的数据块 收集表的统计信息在ANALYZE语句中使用COMPUTESTATISTICS子句或ESTIMATESTATISTICS子句可以收集关于表的物理存储结构和特性的统计信息 如表中记录的总数和记录链接的总数 已使用的数据块总数 未使用的数据块总数 所有记录的平均长度等 比如 下面语句对EMPLOYEES表进行精确统计信息 analyzetableemployeescomputestatistics 而利用下列语句将通过对200条记录的分析 获得对EMPLOYEES表的近似统计信息 analyzetableemployeesestimatestatics 统计完后 可查询USER TABLE ALL TABLE和DBA TABLE数据字典视图来获得分析后的统计信息 查找表中的链接记录和迁移记录在ANALYZE语句中使用LISTCHAINED ROWS子句 可以找出表中的链接记录和迁移记录 Oracle将把表中所有链接记录和迁移记录的ROWID保存到一个名为CHAINED ROWS的表中 比如 下面语句对EMPLOYEES表进行链接记录分析 analyzetableemployeeslistchained rows 6 重命名表如果要修改表的名称 可以使用RENAME语句对表进行重命名 用户只能对属于自己模式中的表进行重命名 renameemployeestoemp 7 1 4删减表1 使用DELETE语句deletefromemployees 但是 用DELETE删除记录后 Oracle不会回收为表分配的存储空间 也无法手工回收 甚至高水位标记也都不会改变 DELETE通常只来删除表中指定的记录 如果删除表中的全部记录 一般用TRUNCATE或DROP语句 2 使用DROP语句DROP语句不仅删除了表中所有的记录 还删除了表结构 删除表一般是由表的所有者来完成的 如果要以其他用户身份删除表 则要求该用户必须具有DROPANYTABLE系统权限 示例如下 droptableemployeescascadeconstraints 其中 CASCADECONSTRAINTS选项用于指定级联删除 当表与其他表具有主外键关系时 删除主表时必须带有该选项 3 使用TRUNCATE语句当表结构必须保留 但表数据不再需要时 可以使用TRUNCATETABLE命令截断表 当执行该命令时 会删除表的所有数据 并释放表所占用的空间 但会保留表的结构 具体命令如下 truncatetableemployees 7 1 5显示表的信息1 显示用户所包含的表通过查询数据字典DBA TABLES USER TABLES可以取得表的信息 其中DBA TABLES可用于显示所有用户表的信息 而USER TABLES则可以显示当前用户表的信息 示例如下 selecttable namefromdba tableswhereowner DEVEP 2 显示表的存储参数设置当建立表时 可以指定表段的存储参数 块空间使用参数 并行度 日志属性以及CACHE属性等 通过查询数据字典USER TABLES 可以取得相应属性信息 示例如下 selectpct free pct used degree cachefromuser tableswheretable name EMPLOYEES 3 显示表段所在表空间及尺寸当建立表时 Oracle会自动为表分配相应的表段 表段的名称与表的名称完全一致 并且该表的所有数据都会存放在相应表段中 那么如何取得段信息呢 通过查询数据字典DBA SEGMENTS或USER SEGMENTS可以取得段信息 其中DBA SEGMENTS用于显示数据库所有段的信息 而USER SEGMENTS则用于显示当前用户段的信息 示例如下 selecttablespace name bytesfromuser segmentswheresegment name DEPARTMENT 4 显示表数据占用的实际空间以及剩余空间当建立表时 Oracle会为表分配相应的表段 当在表上执行INSERT操作时 Oracle会将数据放到表段的相应数据块上 那么如何取得表数据占用的实际空间以及剩余空间呢 通过查询数据字典USER TABLES可以取得这些信息 但查询之前必须首先收集统计 示例如下 analyzetableempcomputestatics selectblocks empty blocksfromuser tableswheretable name EMP 其中BLOCKS对应于数据已占用的实际块个数 而EMPTY BLOCKS则对应于剩余块个数 5 显示区信息当建立表时 Oracle会为表建立相应的表段 段逻辑上又是由一个或多个区组成的 而区又是由相邻的数据块所组成的 那么如何确定段包含哪些区 以及区的位置及尺寸呢 通过查询数据字典DBA EXTENTS 可以显示区的详细信息 示例如下 selectextent id file id block id blocksfromdba extentswhereowner DEVEP andsegment name DEPT 其中 extent id为区编号 file id为区所在文件号 block id为区的初始数据块号 blocks为区所包含的数据块个数 6 显示行所在的实际位置执行INSERT操作时 服务器进程会将数据插入到表段的相应数据块中 并且Oracle会生成惟一的ROWID对应于该行数据 但用户无法直接读懂ROWID 通过使用DBMS ROWID包可以显示行所在数据文件 数据块位置以及行位置 如下所示 selectdeptno dname dbms rowid rowid relative fno ROWID file dbms rowid rowid block number ROWID block dbms rowid rowid row number ROWID row fromdept 7 2管理索引索引的作用索引是与表和簇相关的一种数据库对象 它的作用类似于书中的目录 在没有目录的情况下 要在书中查找指定的内容必须通读全书 而有了目录之后 只需要通过目录就可以快速地找到包含所需内容的页 合理地使用索引可以降低磁盘I O操作次数 从而提高表的访问性能 ROWID 在empno列上无索引 在empno列上存在索引 select fromempwhereempno 7788 假定表EMP数据占用了1000个数据块 如果在EMPNO列上不存在索引 那么当执行 select fromempwhereempno 7788 时需要扫描表的所有数据块 也就是说需要1000次I O操作 如果在EMPNO列上存在索引 并假定索引层次为2 那么当执行 select fromempwhereempno 7788 时 在检索了3个索引块后就可以定位到行所在的ROWID 然后根据ROWID可以直接定位到该行数据 也就是说只需要4次I O操作 显然 使用索引可以大大提高查询速度 2 索引存储方式索引与表一样 不仅需要在数据字典中保存索引的定义 还需要在表空间中为它分配实际的存储空间 当创建索引时 Oracle会自动在用户的默认表空间中或指定的表空间中创建一个索引段 为索引数据提供存储空间 与创建表类似 在创建索引时也可以为它设置存储参数 在创建索引时 Oracle首先对将要建立索引的字段进行排序 然后将排序后的字段值和对应记录的ROWID存储在索引段中 ROWID是数据库的伪列 用于存储一个行标识符 例如 假设为EMPLOYEES表中的ENAME字段创建了索引 createindexemp enameonemployees ename 则Oracle将先在EMPLOYEES表中按照ENAME字段进行排序 默认为升序排序 然后按照排序后的顺序将ENAME字段值和对应的ROWID逐个保存在索引中 在索引创建之后 如果执行如下的一条查询语句 即在WHERE子句中引用ENAME字段 selectename salfromemployeeswhereename JONES 那么Oracle将首先对索引中ENAME字段进行一次快速搜索 找到符合条件的ENAME字段值所对应的ROWID 然后再利用ROWID到EMPLOYEES表中提取相应的记录 7 2 2建立索引建立索引使用CREATEINDEX命令 执行该命令的用户必须具有CREATEINDEX系统权限 如果要在其他用户模式中创建索引 则必须具有CREATEANYINDEX系统权限 常用的索引类型有B 树索引 位图索引 反向索引 函数索引等 1 建立B 树索引B 树索引是最常用的索引 在使用CREATEINDEX语句创建索引时 默认方式下将建立B 树索引 B 树索引是以根块 分支块 叶块来组织和存放索引数据的 在B 树的叶节点中存储索引字段的值与ROWID 根块 KINGKING 分支块块 KINGMILLERTURNER BLAKEBLAKEJAMES 叶块 B 树索引适用于那些具有高基数的字段 即大部分值都不相同的字段 创建B 树索引的语法如下 CREATE UNIQUE INDEX schema indexON schema table column ASC DESC column ASC DESC TABLESPACEtablespace PCTFREEinteger INITRANSinteger MAXTRANSinteger storage clause LOGGING NOLOGGING NOSORT TABLESPACE 用于指定索引段所在表空间 PCTFREE 用于指定为将来INSERT操作所预留的百分比 若指定PCTFREE为0 则可能会导致索引数据占满某些索引叶块 INITRANS 指定每个块中预先分配的事务项的数目 MAXTRANS 限制分配给每块的事务项的数目 storage clause 用于指定索引段存储参数 LOGGING NOLOGGING 是否在重做日志中记录创建索引的操作和在索引上进行的后续操作 NOSORT 指定行按升序存储在数据库中 这样 Oracle在创建索引时就不必对行进行排序 注意 创建索引的时候 不能使用pctused参数 因为删除索引时 只是逻辑删除 其物理空间没有释放 如果在WHERE子句中经常要引用某列 那么你可以基于该列建立B 树索引 例如 如果在应用中经常要执行类似于 SELECT FROMempWHEREename SCOTT 查询语句 那么你可以基于ENAME列建立B 树索引 建立B 树索引的示例如下 createindexind enameonemp ename pctfree30storage initial100Knext100Kpctincrease0minextents2maxextents50 tablespaceindx 当建立了B 树索引之后 如果在WHERE子句中包含有索引列 如 SELECT FROMempWHEREename SCOTT 那么Oracle会自动使用索引定位行数据 从而降低I O操作次数 最终提高数据访问速度 2 建立位图索引位图索引不同于B 树索引 它不是以 索引字段值 ROWID 的方式来存储索引信息的 而是为每个唯一的索引字段值建立一个位图 在这个位图中使用一个位元 Bit 取值为0或1 来对应一条记录的ROWID 如果该位元为1 说明与该位元对应的ROWID是一条包含该位图的索引字段值的记录 位元到ROWID的对应关系通过位图索引中的映射函数来完成 创建位图索引的语法如下 CREATEBITMAPINDEX schema indexON schema table column ASC DESC column ASC DESC TABLESPACEtablespace PCTFREEinteger INITRANSinteger MAXTRANSinteger storage clause LOGGING NOLOGGING NOSORT 假定表CUSTOMER包含有1000万条记录 该表使用REGION列表示客户所在方位 并且将来经常需要引用REGION列进行数据统计 CUSTOMER表的部分内容如下所示 CUSTOMER IDMARITAL STATUSREGIONGENDERSALARY101SingleEastMale1000102MarriedCentralFemale1200103MarriedWestFemale8500104DivorcedWestMale1200105SingleCentralFemale7500106MarriedCentralFemale1050 从表中可以看到 字段MARITAL STATUS REGION和GENDER都适合建立位图索引 而CUSTOMERID和SALARY两个字段则适合建立B树索引 现以REGION字段为例 说明怎样建立位图索引 因为REGION字段只有三个不同的值 East Central West 所以将建立三个位图段 REGION East REGION Central REGION West 100010001001010010 假设现在要查询所有已婚的 居住在中部或西部地区的顾客数目 可以编写如下的查询语句 selectcount fromcustomerwheremarital status Married andregionin Central West 如果在MARITAL STATUS和REGION字段上都建立了位图索引 查询的速度可以变得很快 因为只需要对这两个字段的索引位图进行逻辑运算 然后计算结果位图中1的个数即可得到满足条件的顾客数目 下图显示了逻辑运算的