表和索引的管理PPT课件.ppt

Oracle数据库 第七章表和索引以及聚簇的管理 1 7 1 1设计表结构主要考虑 1 在建立表结构前要认真进行分析与设计主要考虑下面方面 规范表的各个字段类型 确定各个字段的取值范围 确定是否可以空以节省空间 相关的各表是否适合建立簇 确定使用数据库块的空间 2 7 1 1设计表结构主要考虑 2 根据应用类型确定块空间的使用主要设置pctfree和pctused两个参数 修改较频繁 且行内容有增大的可能 则 pctfree20 pctused50 修改行一般不使行增大 经常进行insert和delete 则pctfree5 pctused70 数据库表很大 如数据仓库等 并且这些表多数为只读 则 pctfree5 pctused40 3 7 1 1设计表结构主要考虑 3 指定块事务数INITRANS和MAXTRANS在个别特殊的应用系统需要认真考虑这两个参数的具体值 INITRANS在一个对象的每个数据块中 为指定数目的事务项 TRANSACTIONENTRIES 预分配的空间 缺省为1 MAXTRANS限制能够并发进入一个数据块的事务数目 如果当前同时有MAXTRANS个事务在使用一个数据库块 则请求该块信息的下一个事务必须等到正在使用该块的某一事务提交或回滚后才能使用该块 4 7 1 1设计表结构主要考虑 4 确定每个表的存储表空间在创建表时要用Tablespace来指定表所希望存放的表空间 不要使用缺省表空间 不要使用缺省表空间 即建议将表指定到对应的数据表空间 不要在system表空间上创建应用系统的表或练习用的表 将相关的各表创建在同一个表空间里 采用表和索引分开存放在不同表空间的方法 5 7 1 1设计表结构主要考虑 5 考虑创建UNRECOVERABLE表对于特殊的需要 可以考虑将表创建成为不需恢复 UNRECOVERABLE 的表 如复制一个已存在的表就可以采用这种方法以减少系统的开销 如 例 参考emp表创建一个新的emp new表 SQL createtableemp newasselect fromempUNRECOVERABLE 或CREATETABLEnew empASselect fromscott empNOLOGGING 注 虽然上面提到UNRECOVERABLE 但是Oracle推荐你使用NOLOGGING或LOGGING 6 7 1 1设计表结构主要考虑 6 估计和设置表的存储参数在创建表 索引及簇时 建议你对存储参数进行认真的考虑 估计 表的年存储数据量的初始值 initial 确定合适的扩展参数值 next 一般以六个月为一个基本单位 表对应的索引的数据量 表的每次增 删 改所可能产生的回滚段数据量 7 理解存储参数和PCTFREE与PCTUSED 8 理解存储参数和PCTFREE与PCTUSED 当插入新行时 并达到该数据块的pctfree时 将该块置为脱离 自由空间列表 即使从块删去一些行时 ORACLE也不立即将该块置为 自由空间列表 而是直到该块达到pctused时才将该块置为 自由空间列表 free space list 9 理解存储参数和PCTFREE与PCTUSED 管理员和应用系统的总设计师应该对当前应用系统的数据库结构进行查询和调整 1 查询某个用户的表或簇的PCTFREE和PCTUSED值 SQL selecttable name pct free pct usedfromuser tables 2 修改表的PCTFREE和PCTUSED不合理值 SQL altertableemp2pctfree10pctused85 10 7 2 1建立表结构 应该是由总设计师根据用户的具体应用需要来定 表的设计是否合理关系到应用系统将来的成败与性能问题 任何担当总设计师角色的人都不要轻视这项工作 11 建立表结构命令 CREATETABLE命令简要语法如下 CREATETABLE USER table name COLUMN1DATATYPE DEFAULTEXPN COLUMN CONSTRAINT TABLE CONSTRAINT COLUMN1DATATYPE DEFAULTEXPN COLUMN CONSTRAINT TABLE CONSTRAINT CLUSTERCLUSTER COLUMN1 COLUMN2 PCTFREEN PCTUSEDN INITRANSN MAXTRANSN STORAGEN TABLESPACETABLESPACE ENABLE DISABLE ASQUERY 12 建立表结构 例1 在SCOTT模式下建立表emp 并指定表空间和存储参数 Createtablescott emp Empnonumber 5 primarykey Enamevarchar2 15 notnull Jobvarchar2 10 Mgrnumber 5 Hiredatedatedefaultsysdate Salnumber 7 2 CHECK sal 100 Commnumber 3 default0 0 Deptnumberconstraintdept fkeyReferencesscott dept 13 建立表结构 TablespaceusersPCTFREE10PCTUSED70STORAGE INITIAL50KNEXT50kMAXEXTENTS10 14 建立表结构 特别说明 一般情况下 如果表含有long字段 这样势必需大量的空间 系统会在每次插入新记录时 经常分配空间给表 不久就会出现 ORA 01547 Failedtoallocateextentofsizexxxxxintablespace xxxx 此种情况如果表空间还剩较多的连续空间的话 则可能是该表分配的空间次数已达最大值 为了对该表能插入新数据 需对该表的存储参数作修改 比如 SQL altertablexxxstorage MAXEXTENTS999 15 7 2 1 3建立临时表结构 Oracle现在可以使用CREATEGLOBALTEMPORARYTABLE命令来实现建立临时表结构 表的数据只在用户会话期间存在 当会话完成后就自动清除 SQL createglobaltemporarytablemyempasselect fromemp 当连接到其它用户再连接回来后数据就不存在了 16 7 2 2修改表结构 从8i以后 oracle修改列的命令可以实现 增加一个列 字段 宽度 减少一个列 字段 宽度 该列必须无数据 增加一个列 字段 修改列的定义 或一个限制 如数据类型 NOTNULL 仅当某列的值为空时才能修改其类型 去掉限制 修改存储分配 记录表已作过BACKUP 删除已存在的列 仅Oracle8i及以后版本 重新定位和组织表 仅Oracle8i及以后版本 将表标识为不可用 仅Oracle8i及以后版本 17 7 2 2修改表结构 ALTERTABLE user table ADD colum element table constraint column element table constraint MODIFY column element column element DROPCONSTRAINTconstraint PCTFREEinteger PCTUSEDinteger INITRANSinteger MAXTRANSinteger STORAGEstorage BACKUP 18 7 2 2修改表结构 例1 对已经存在的表增加一新的列 SQL altertabledeptadd headcountnumber 3 例2 对表的列修改其大小 SQL altertabledeptmodify Dnamechar 20 不能将已经存在数据的表的列的值调整为一个比原来小的长度 这样会因起下面的错误 ORA 01441 无法减小列长度 因为一些值过大 19 修改表结构 例3 复制一个表 CREATETABLEHOLD TANKASSELECTTANK NO CHIEF CARETAKER NAMEFROMTANK 例4 参照某个已存在的表建立一个表结构 不需要数据 createtableemp2asselect fromempwhererownum 1 20 修改表结构 虽然Oracle允许用户对表的结构进行修改 但建议你在工作中不要采用方式 表结构被多次修改会影响应用系统的性能 21 7 2 3删除表结构 DROPTABLE命令可以实现删除表数据和结构DROPTABLE user table name CASCADECONSTRAINTS CASCADECONSTRAINTS表示所有指向本表的主键 外部键被删掉 当删除一个表时 下面的对象也随之被删掉 表的索引 指向本表的外部键 本表的触发器 本表中的分区 本表的快照 本表的角色和用户权限 加在本表的所有限制 22 7 2 4使用CHECK作限制约束 在设计数据库表结构时 建议分析用户的数据的取值范围 从而将那些取值范围一定的字段用CHECK进行描述 以保证以后数据的正确性 Createtableworker empnonumber 4 primarykey namevarchar2 10 agenumber 2 CHECK agebetween18and65 lodgingchar 15 ReferencesLODGING lodging 23 7 3主键 创建主键 ORACLE系统提供一个关键字primarykey来建立一个主键 所谓主键 就是在一个表内该列具有唯一的值 一旦你为一个表的一列或几个列建立了主键 则ORACLE就自动为该表建立一个唯一索引 CREATETABLEdept deptnonumber 2 dnamevarchar2 20 locvarchar2 20 CONSTRAINTpk deptPRIMARYKEY deptno 24 7 3主键 创建主键 用altertable创建主键例1ALTERTABLEPARK REVENUEADD park rev pkPRIMARYKEY ACCOUNT NO 25 7 3主键 唯一索引和主键区别 唯一索引 唯一索引使用CREATEUNIQUEINDEX命令完成 能标识数据库表中一行的关键字 在数据字典中建立了唯一索引名字 主键 主键使用primarykey来指定 能标识数据库表中一行的关键字 在数据字典中也建立了唯一索引名字 差别 被定义为唯一索引的列可以空 而被定义为主键的列不能空 26 Createtabledept deptnonumber 2 dnamevarchar2 40 constraintunq dnameunique locvarchar2 50 27 7 3 2改变主键 限制 不许修改作为主键的列 不许修改作为主键的名字可以 可以定义一主键 或使主键无效例1 AltertabledeptDisablescott pk dept 28 7 3 2改变主键 如果有一外部键依赖于该主键 则系统给出下列错误 ORA 02297 Cannotdisableconstraint scott pk dept Depentenciesexist 在这种情况下 必须先删掉依赖于该主键的外部键并使该外部键无效 然后查才能使主键无效 29 7 3 3删除主键 ALTERTABLE schema tablenameDROPCONSTRAINTconstraint name CASCADE 删除顺序 1 使该外部键无效 删掉依赖于该主键的外部键 2 使该主键无效 删掉该主键 30 7 4外部键 建立外部键是保证完整性约束的一种唯一方法 也是关系数据库的精髓所在 许多曾使用过桌面数据库 如Dbase Foxpro 的软件人员不太习惯或不使用关系数据库的主键与外部键的方法来设计自己的数据库结构 这是很不好的方法 应该在使用关系数据库中将习惯改过来 否则设计的应用结构和效率就不可能达到用户的要求 31 7 4 1建立外部键 方法1Createtableemp empnonumber 4 enamevarchar2 10 jobvarchar2 10 mgrnumber 4 hiredatedate salnumber 7 2 commnumber 7 2 deptnoconstraintfk deptnoReferencesdept deptno 32 7 4 1建立外部键 方法2Createtableemp empnonumber 4 enamevarchar2 10 jobvarchar2 10 mgrnumber 4 hiredatedate salnumber 7 2 commnumber 7 2 deptno constraintfk deptnoForeignkey deptno Referencesdept deptno 33 建立外部键 方法3Altertablephone callsADDconstraintfk areaco phonenoForeignkey areaco phoneno Referencesaustomers areano phoneno Exceptionsintowrong numbers 34 7 4 2修改外部键 ORACLE不允许改变已被定义的外部键的列 也不允许改变外部键的名字 只能用ALTERTABLE定义一个新的外部键或者使一个已存在的外部键无效 ALTERTABLE schema table nameDISABLECONSTRAINT NAME 35 7 4 3删除外部键 关系数据库的核心主要体现在主键和外部键上 在进行数据库结构设计时 建议要采用主键和外部键来定义那些有关系的表 这样可以保证应用系统数据的完整性和一致性 ALTERTABLE schema table nameDROPCONSTRAINTconstraint name 36 7 5管理表 表移动到新的表空间 例1通过移动来实现存储参数的修改 AltertableempMOVESTORAGE INITIAL1mnext512kminextents1maxextents999pctincrease0 通过查询视图user tables来查看移动的情况SQL selecttablespace name table name initial extentfromuser tables 37 7 5 4标记不使用的列 可以使用ALTERTABLE SETUNUSED语句实现将表中的列设置为不用的状态以达到删除列的目的 其结果是 在显示结果时看不到该列 不删除该列的数据 但可以将该列删掉 SQL altertableemp2setunused comm 38 7 5 5删除不使用的列 该语句时可以在后面加上checkpoint检查点关键字 它可以产生一个检查点 使用检查点可以在删除数据列的操作过程中减少恢复日志的容量积累 从而避免回滚段的空间消耗 如 SQL altertableempdropunusedcolumnscheckpoint 注意 删除表中未使用列时不需要指定列名 它是根据altertableempsetunused comm 语句来删除未使用的列 39 7 5 6删除不需要的表 管理表的工作也包括删除掉那些不再使用的表以释放出空间和提高处理速度 要想删除无用的表 先要识别哪些表是开发人员临时建立的 哪些是应用系统设计人员创建的等 我们可以从创建表的时间上来识别 如 SQL selectowner object type object name createdfromdba objectswhereobject type TABLE orderbyowner object type createdDROPTABLE 后加CASCADECONSTRAINT选项 使得全部删除不需要的表 40 7 6索引的定义与管理 索引是若干数据行的关键字的列表 查询数据时 通过索引中的关键字可以快速定位到要访问的记录所在的数据块 从而大大减少读取数据块的I O次数 因此可以显著提高性能 在数据库设计阶段的与数据库结构一道考虑 应用系统的性能直接与索引的合理直接有关 41 缺点 对表进行insert update delete处理时 由于要表的存放位置记录到索引项中而会降低一些速度 42 7 6 1建立索引 CREATE unique INDEX user indexON user table column ASC DESC column ASC DESC CLUSTER scheam cluster INITRANSn MAXTRANSn PCTFREEn STORAGEstorage TABLESPACEtablespace NOSORT Advanced 43 创建一般的索引 CREATEINDEXemp enameonemp ename TABLESPACEusersSTORAGE INITIAL128knext64kpctincrease0 pctfree5 44 创建与约束有关的索引 CREATETABLEemp empnoNUMBER 5 PRIMARYKEY ageINTEGER ENABLEPRIMARYKEYUSINGINDEXTABLESPACEusersPCTFREE0 45 创建基于函数的索引 创建基于函数的索引 需要有GLOBALQUERYREWRITE和CREATEANYINDEX权限 示例 CREATEINDEXidxONemp UPPER ename SELECT FROMEMPWHEREUPPER ename LIKE JOH 46 例2为emp的工资和奖金之和建立索引 1 查看emp的表结构 2 连接到DBA帐户并授权 SQL grantGLOBALQUERYREWRITEtoscott SQL grantCREATEANYINDEXtoscott 47 3 连接到scott帐户 创建基于函数的索引 SQL connectscott tiger ora816SQL createindexsal commonemp sal comm 12 sal comm tablespaceusers 4 在查询中使用函数索引 SQL selectename sal commfromempwhere sal comm 12 5000 48 7 7簇的定义与管理 簇 Cluster 就是将一组有机联系的表在物理上存放在一起并且相同的关键列的值只存储一份 用于提高处理效率的一项技术 簇 Cluster 是一组表 如果应用程序中的SQL语句经常联结两个或多个表 可以把这些表以簇方式进行创建以改善性能 如果创建了簇并在创建表时指定到已经创建好的簇中 ORACLE就把簇中的表存储在相同的数据块中 并且各个表中的相同的列值只存储一个 49 50 簇的定义与管理 创建簇的场合 如果通过引用完整性把两个或多个表联系起来并经常使用联结 则为这些表创建一个索引簇 如果一个表的多行经常与一个非唯一的列一起查询 则为该列创建一个单表簇 该列作为簇关键字 51 簇的定义与管理 对频繁更新或删除的表使用簇对性能有不利的影响 因此对簇的使用作如下限制 簇中的每个表必须有一列与簇中指定的列的大小和类型匹配 簇码中可用列的最大数目是16 即一个簇最多有16列作为簇码 列的最大长度为239字节 LONG和LONGRAW不能作为簇列码 52 簇的定义与管理 CREATECLUSTERcluster columndatatype colmndatatype PCTUSED40 intger PCTFREE10 intger SIZEintger INITRANS1 intger MAXTRANS255 intger TABLESPACEtablespace STORAGEstorage 53 簇的定义与管理 创建簇及其表的步骤 1 用CREATECLUSTER创建聚集 2 用CREATEINDEX创建聚集索引 3 用CREATETABLE创建表 并指定聚集 4 插入数据并进行DML操作 54 簇的定义与管理 示例 创建一个簇 将员工表和部门表按簇存放 1创建聚簇CREATECLUSTERpersonnel department numberNUMBER 2 tablespaceusersSTORAGE INITIAL100KNEXT50K 55 簇的定义与管理 2创建员工表和部门表CREATETABLEemp cluster empnoNUMBERPRIMARYKEY enameVARCHAR2 10 NOTNULLCHECK ename UPPER ename jobVARCHAR2 9 mgrNUMBER hiredateDATE salnumber 9 0 commNUMBER 9 0 DEFAULTNULL deptnoNUMBER 2 NOTNULL CLUSTERpersonnel deptno 56 簇的定义与管理 CREATETABLEdept cluster deptnoNUMBER 2 dnameVARCHAR2 9 locVARCHAR2 9 CLUSTERpersonnel deptno 57 簇的定义与管理 3 创建聚簇索引CREATEINDEXidx personnelONCLUSTERpersonnel 58 簇的定义与管理 使用下面视图可以收集属于用户本人的簇信息 DBA CLU COLUMNS或USER CLU COLUMNSDBA CLUSTERS或USER CLUSTER 59 删除聚簇 删除聚簇的步骤 删除聚簇引用的表删除聚簇注意 如果聚簇表已经删除 则聚簇索引不需删除 一定要先删除表 后删除聚簇 否则 会引起如下错误 ERROR位于第1行 ORA 00951 群集非空 60 7 8完整性的管理 关系数据库的核心就是一致性和完整性 可以使用NOTNULL CHECK PrimaryKey ForeignKey和Unique等 这些关键字就是用来描述完整性的 如果要建立完整性描述和使某个完整性有一个名字 就要用CONSTRAINT关键来给出 如果不要CONSTRAINT关键字的话 该完整性限制的名字缺省为SYS Cnnnnnn 比如SYS C001009等 61 7 8完整性的管理 示例 Createtableworker empnonumber 4 primarykey namevarchar2 10 agenumber 2 CHECK agebetween18and65 62 7 8完整性的管理 selectowner constraint name table namefromuser constraintswheretable name WORKER 结果如下 OWNERCONSTRAINT NAMETABLE NAME ZHAOSYS C001009WORKER 由于没有用constraint给出完整性的名字 所以名字缺省为SYS C001009 63 7 8完整性的管理 selectSEARCH CONDITIONfromuser constraintswheretable name WORKER SEARCH CONDITION agebetween18and65 64 7 8完整性的管理 主键和外部键的例子CREATETABLEdept deptnonumber 2 dnamevarchar2 20 locvarchar2 20 CONSTRAINTpk deptPRIMARYKEY deptno 65 7 8完整性的管理 Createtableempl Empnonumber 5 primarykey Enamevarchar2 15 notnull Jobvarchar2 10 Mgrnumber 5 Hiredatedatedefaultsysdate Salnumber 7 2 CHECK sal 100 Commnumber 3 default0 0 Deptnumberconstraintdept fkeyReferenceszhao dept 66 7 8完整性的管理 selectconstraint name table name SEARCH CONDITIONfromuser constraints CONSTRAINT NTABLE NAMESEARCH CONDITION PK DEPTDEPTDEPT FKEYEMPLSYS C001013EMPL ENAME ISNOTNULLSYS C001014EMPLsal 100SYS C001009WORKERagebetween18and65已选择7行 67 7 8完整性的管理 例3使用唯一索引的例子Createtableemp2 Empnonumber 5 Enamevarchar2 15 notnull Per idvarchar2 18 CONSTRAINTperidUNIQUEUSINGINDEXTABLESPACEusers 68 7 8完整性的管理 selectconstraint name table name status bad last changefromuser const