01-0 Oracle 存储与关键表设计.ppt

OracleRDBMS存储与表设计 Zyj5681 2007年8月 内容提要 Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i 10g存储参数与块参数的变化表数据存储量大小估计表的修改及其它处理参考资料 Oracle数据库 表空间 对象 Tablespace Tables indexes etc Datafiles Blocks Oracle表空间 Tablespaces 表空间类型System NonsystemOnline OfflineRead Write Read OnlyTransportable表空间种类永久表空间 存储表 索引 临时表空间 存储排序数据 撤消表空间 存储DML数据 表空间 tablespace 与表 Tables 表存储在表空间 Tablespaces 表由行与列构成表包含数据 表占很多块空间 称 段 Tablespace Tables indexes etc Datafiles Blocks Oracle表空间与数据文件 表空间 Tablespace 表空间由多个数据文件组成 如SYSTEM表空间由system01 dbf构成 表的数据存储表空间对应的数据文件里 表和索引等可分别存储在不同的表空间 数据文件结构 数据文件由多个Oracle块组成 F1 ora F2 ora F3 ora F4 ora F5 ora Tablespace1 Tablespace2 Table1 Table2 Table3 CREATETABLESPACEuser dataDATAFILE udata ora SIZE10MEXTENTMANAGEMENTLOCAL CREATETABLEmy emp namevarchar2 20 TABLEASPACEuser datapctfree5 Oracle块结构与段对象 数据文件都是块构成 内存分配也是以块为单位 I O也以块为单位 块大小在安装时选择 基本块大小 不同的Oracle版本允许块大小不一样 目前块大小可以2k 32K Oracle9i 10g 11g在表空间中块大小可变 块大小要根据OS决定 Oracle块太大是OS块大小的倍数 2的幂次方 Oracle块大小影响到性能 Oracle块结构与段对象 块内部结构 Oracle数据段 Datasegment 存放在块里比我们想象要复杂的多 表空间对应一个或多个数据文件 数据文件由多个Oracle块构成 Oracle块可存储下面的段 数据段 Datasegment tables triggers等索引段 Indexsegment indexes临时段 Temporarysegment 排序和临时表回滚段 Rollbacksegment undodata信息LOB段 LOBsegment 与表的基本数据存储或分开的LOB数据 Oracle块结构与段对象 内容提要 Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表存储参数在9i 10g的变化表数据存储量大小估计表的修改及其它处理参考资料 创建表结构准备 对象命名 名字1 30个字符号 名字只能是a z A Z 也可在字符后加0 9 名字可加 不许带空字符 最好不能为保留字 可从v reserved words中查 与保留字相同 可用双引号 列名的统一问题 同一种含义的列命名与类型在应用系统中统一 命名不要采用拼音或拼音缩写 重复率高 不好猜 命名采用英语或英语缩写 列名太长不好 类型能采用字符就不要采用数字类型 创建表结构准备 Oracle8 8i 9i 10g版本常用数据类型 创建表结构准备 Oracle10g一般的数据类型 创建表结构准备 Oracle10g一般的数据类型 创建表结构准备 Oracle10g一般的数据类型 创建表结构准备 10g新BINARY FLOAT数据类型 32Bit的单精度数据类型BINARY FLOAT BINARY FLOAT以5个字节存储 含一个长度字节 10gBINARY DOUBLE 64Bit的双精度数据类型BINARY DOUBLE BINARY DOUBLE以9个字节存储 含一个长度字节 BINARY FLOATBINARY DOUBLEMAXIMUMFINITEVALUE3 40282 38F1 79769313486231E 308MINIMUMFINITEVALUE1 17549E 38F2 22507485850720E 308 Oracle对象创建准备 数据类型 数据类型选择要点 不要使用long或longraw类型 一般不要NCHAR等类型 Number p s 类型要满足应用系统发展需要 Number p s 类型在整个应用系统中要保持一致性 应用系统设计与数据仓库考虑 列名与类型一致性问题 将来的数据整合问题 统一使用Number p s 类型 数字类型整数与精度要一致 内容提要 Oracle表空间与块结构与段对象Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i 10g存储参数与块参数的变化表的修改及其它处理参考资料 创建表结构 语法 建议采用命令方式 认真研究Oracle的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 name ENABLE DISABLE ASQUERY 创建表结构语法 子句解释 创建表结构语法 子句解释 创建表结构语法 子句解释 创建表结构语法 块变化 建议采用命令方式 认真研究Oracle的CREATETABLE子句 关键的项不要采用默认值 语法如下 内容提要 Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i 10g存储参数与块参数的变化表的修改及其它处理参考资料 创建表结构样例1 块控制参数 createtableuser dflow user namevarchar2 30 用户名 bj datedate 日期及时间 day fee innumber 16 6 日入流量费用 day fee outnumber 16 6 日出流量费用 TABLESPACEusersPCTFREE5PCTUSED80 创建网络计费数据流量明细表user dflow 根据上网记录的信息很少修改特点 设置PCTFREE为5 而希望尽快释放删除的块空间而将PCTUSED设85 该表一开始先分配100MB 用完再分配50MB 创建表结构样例2 存储分配参数 createtableuser dflow user namevarchar2 30 用户名 bj datedate 日期及时间 day fee innumber 16 6 日入流量费用 day fee outnumber 16 6 日出流量费用 TABLESPACEusersPCTFREE5PCTUSED80STORAGE INITIAL120MNEXT60MMINEXTENTS1PCTINCREASE0MAXEXTENTS9999 创建网络计费数据流量明细表user dflow 对user dflow表的年数据量进行估计 假设年数据量100MB 则可分配120MB 当分配120MB用完后 由再分配60MB 内容提要 Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i 10g存储参数与块参数的变化表的修改及其它处理参考资料 存储参数在9i 10g的变化 为什么9i 10g取消段的空间扩展设置 表数据的增长与空间的不断减少是矛盾的关键 NEXT表示必须分配一组连序的Oracle块 如果表空间无NEXT所要求的连续块 则分配失败 9i 10g为表扩展空间时不参考NEXT 而是根据表的增长与实际表空间剩余的空间来确定 存储参数设置 字典类型表空间 可设置STORAGE的所有项 本地管理表空间 仅STORAGE的INITIAL有效 存储参数在9i 10g的变化 块控制参数设置 9i 10g本地管理表空间ASSM AUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO Oracle根据dba tables avg row len来调整PCTUSED的大小 ASSM AUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL Oracle根据你的PCTUSED设置来调整PCTUSED的大小 SYSTEM表空间ASSM为MANUAL USERS表空间ASSM为AUTO 见后面脚本 存储参数在9i 10g的变化 创建表结构前 根据表数据变化特点 创建相应的表空间 表空间的对应数据文件大小要合理 在创建表时指定到创建好的表空间上 并根据表空间是否是字典 本地来确定是否指定NEXT等参数 Setlong9999SELECTdbms metadata get ddl TABLESPACE SYSTEM fromdual CREATETABLESPACE SYSTEM DATAFILE E ORACLE PRODUCT 10 2 0 ORADATA ORA10 SYSTEM01 DBF SIZE314572800AUTOEXTENDONNEXT10485760MAXSIZE32767MLOGGINGONLINEPERMANENTBLOCKSIZE8192EXTENTMANAGEMENTLOCALAUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL 存储参数在9i 10g的变化 ASSM下创建表结构 观察下面脚本运行的结果 DROPTABLEcustomer CREATETABLEcustomer customerIDINTNOTNULLPRIMARYKEY firstNameVARCHAR2 30 NOTNULL lastNameVARCHAR2 30 NOTNULL phoneVARCHAR2 20 NULL address1VARCHAR2 30 NULL address2VARCHAR2 30 NULL cityVARCHAR2 30 NULL stateCHAR 2 NULL zipCHAR 10 NULL PCTFREE5PCTUSED85TABLESPACESYSTEMSTORAGE INITIAL4MNEXT1MPCTINCREASE0 DROPTABLEproduct CREATETABLEproduct productIDINTNOTNULLPRIMARYKEY productNameVARCHAR2 30 NOTNULL productVendorVARCHAR2 50 NULL costFLOATNOTNULLCONSTRAINTchk costCHECK cost 0 priceFLOATNOTNULLCONSTRAINTchk priceCHECK price 0 PCTFREE5PCTUSED85TABLESPACEUSERSSTORAGE INITIAL4MNEXT2M SQL selecttablespace name table name initial extent next extent pct free pct used2fromdba tableswheretable namein PRODUCT CUSTOMER 3 TABLESPACE NAMTABLE NAMEINITIAL EXTENTNEXT EXTENTPCT FREEPCT USED SYSTEMCUSTOMER4194304585USERSPRODUCT41943045 好好观察上面的结果 存储参数在9i 10g的变化 LMT的extent management clause AUTOALLOCATE 默认值 区的大小由系统来管理 忽略NEXT等参数 块小于16K则分配64K 大于16K 则分配1M 然后再根据表增长情况分配区 大小是64K 1M 8M LMT为UNIFORM情况 UNIFORM规定每次扩展都一样的大小 UNIFORM默认为5个Oracle块 segment management clause SEGMENTSPACEMANAGEMENTMANUAL AUTO 如果SSM AUTO 则Oracle忽略对像的PCTUSED FREELIST和FREELISTGROUPS参数 见前面样例 存储参数在9i 10g的变化 LMT为UNIFORM情况 创建的对象的EXTENT都统一用此尺寸去划分大小 为表指定NEXT PCTINCREASE时 这些参数只在创建时有效 之后的扩展无效 存储参数在9i 10g的变化 LMT指定UNIFORM例子 1 创建一表空间 UNIFORMSIZE为3M 2 在上面表空间上创建一个表 CREATETABLESPACEASSMDATAFILE D oracle oradata encore ASSM01 DBF SIZE50MAUTOEXTENDOFFEXTENTMANAGEMENTLOCALUNIFORMSIZE3MSEGMENTSPACEMANAGEMENTAUTO CREATETABLEASSMTAB COL1NUMBER 3 STORAGE INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100 TABLESPACEASSM 存储参数在9i 10g的变化 LMT指定UNIFORM例子 3 查看该表的扩展信息 根据INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100 系统分配方法为 INITIAL EXTENT 2 5 5 5 100 17MNext pctincrease参数创建时是有效的 但以后ORACLE使用统一的尺寸 3M 来为表分配EXTENT 由于UNIFORMSIZE 3M 所以必须6个EXTENT 5次只有5x3 15M SELECTSEGMENT TYPE TABLESPACE NAME EXTENT ID BYTES BLOCKSFROMUSER EXTENTSWHERESEGMENT NAME ASSMTAB SEGMENT TYPETABLESPACE NAMEEXTENT IDBYTESBLOCKS TABLEASSM03145728384TABLEASSABLEASSM23145728384TABLEASSM33145728384TABLEASSM43145728384TABLEASSM53145728384 SQL selecttable name INITIAL EXTENT NEXT EXTENT PCT INCREASEfromuser tableswheretable name ASSMTAB TABLE NAMEINITIAL EXTENTNEXT EXTENTPCT INCREASE ASSMTAB31782579231457280 表数据存储量大小估计 方法1 从DDL中了解数据与行大小 如 Tableemp namevarchar2 30 salarynumber 8 2 join datedate Step1 每行大小 20B 6B 7B 43BStep2 估计总行数 10000 rows Step3 表的小 43 B 10000 rows 0 5MBStep4 每年增长率 43 2000 rows 100KB一般习惯 总是保守的多 表数据存储量大小估计 方法2 从存在的数据得到平均行大小Step1 Initial40 Next50 Extents4 PctIncrease50Step2 Size 90 50 1 5 50 1 5 1 5 287 5Step3 MaxExtents 高版本 不要设置低的值 S 项目总数 NEXT RN 1 R 1 这里R 100 PctIncrease 100 N MAXEXTENTS 1 总大小 S INITIAL 一般习惯 要合理设置pctincrease是关键 表数据存储量大小估计 方法3 从存在的表中分析以得到统计信息 Step1 ANALYZETABLEEMPESTIMATE COMPUTE STATISTICS Step2 SELECT NUM ROWS AVG ROW LEN FROMUSER TABLESWHERETABLE NAME EMP 表数据存储量大小估计 检查表增长 检查段的扩展次数是否接近MAXEXTENTS Selectsubstr owner segment name 1 25 object MaxextentsFromsys Dba extentsWheremaxextents extents 5Orderbyextents 内容提要 Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i 10g存储参数与块参数的变化表的修改及其它处理参考资料 修改表结构 AlterTable 表结构Altertable语句 下面是TABLETABLE简单语法 ALTERTABLE user table ADD colum element table constraint column element table constraint MODIFY column element column element DROPCONSTRAINTconstraint PCTFREEinteger PCTUSEDinteger INITRANSinteger MAXTRANSinteger STORAGEstorage BACKUP 修改表结构 AlterTable 表结构的修改问题 不要经常修改表结构 修改表结构带来许多问题 性能 一致性等 修改表结构要分析对其它表的影响 目前可修改 增加列 字段 宽度或增加新列 减少列 字段 宽度 该列必须无数据 增加一个约束或去掉约束 修改存储分配 记录表已作过BACKUP 删除已存在的列 重新定位和组织表 将表标识为不可用 修改表结构 删除表结构的某个列 删除某个列的语法 ALTERTABLE DROPCOLUMN CASCADECONSTRAINTS 删除某个列的例子 删除列后不能恢复 SQL ALTERTABLEempDROPCOLUMNcomm 表已更改 修改表结构 标识表结构的某个列不可用 标识某个列不可用的语法 ALTERTABLE SETUNUSEDCOLUMN标识某个列不可用及删除的例子 标识某个列不用后就不能再恢复为可用 SQL Altertableemp2setUNUSEDCOLUMNcomm 表已更改 SQL ALTERTABLEemp2DROPUNUSEDCOLUMNSCHECKPOINT20 表已更改 SQL 删除表结构 删除表结构 就是从Oracle系统中删除该表的数据与结构 语法 删除表结构将影响 指向本表的外部键 表中的索引 本表的触发器 本表中的分区 本表的快照 本表的角色和用户权限 加在本表的所有限制 DROPTABLE user table name CASCADECONSTRAINTS 删除表结构 删除表结构注意 删除某个表前要查询该是否其它表存在关系 不要轻易带CASCADE子句 删除表结构建议做 查询某个表的子表 DROPTABLEDEPTCASCADECONSTRAINTS 表已删除 找出模式下的某个表的子表 SELECTtable name childtables FROMuser constraintsWHEREr constraint nameIN SELECTconstraint nameFROMuser constraintsWHEREtable name UPPER 释放不用的空间 记录有最高水位 Highwatermark 可释放不使用的扩展 Freespaceafterdelete Unusedblock Highwatermark Beforedeallocation Usedblock ALTERTABLEemployeesDEALLOCATEUNUSED Afterdeallocation 10g段收缩 表的存储空间收缩 10g段收缩 表的存储空间收缩 Oracle9i提供 删除表 再重建 重加载数据ALTER