01-0 Oracle 存储与关键表设计(50)

OracleRDBMS存储与表设计赵元杰[e-mail]2007年8月2019年7月31日Oracle数据库优化设计2/50内容提要Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表数据存储量大小估计表的修改及其它处理参考资料2019年7月31日Oracle数据库优化设计3/50Oracle数据库-表空间-对象TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks2019年7月31日Oracle数据库优化设计4/50Oracle表空间(Tablespaces)表空间类型System/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable表空间种类永久表空间(存储表、索引)临时表空间(存储排序数据)撤消表空间(存储DML数据)2019年7月31日Oracle数据库优化设计5/50表空间(tablespace)与表(Tables)表存储在表空间(Tablespaces)表由行与列构成表包含数据(表占很多块空间，称“段”)TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks2019年7月31日Oracle数据库优化设计6/50Oracle表空间与数据文件表空间(Tablespace)：表空间由多个数据文件组成，如SYSTEM表空间由system01.dbf构成；表的数据存储表空间对应的数据文件里；表和索引等可分别存储在不同的表空间；数据文件结构：数据文件由多个Oracle块组成；F1.oraF2.oraF3.oraF4.oraF5.oraTablespace1Tablespace2Table1Table2Table3CREATETABLESPACEuser_dataDATAFILE‘udata.ora’SIZE10MEXTENTMANAGEMENTLOCAL…CREATETABLEmy_emp(namevarchar2(20),…TABLEASPACEuser_datapctfree5;2019年7月31日Oracle数据库优化设计7/50Oracle块结构与段对象数据文件都是块构成；内存分配也是以块为单位；I/O也以块为单位；块大小在安装时选择（基本块大小）；不同的Oracle版本允许块大小不一样；目前块大小可以2k~32K；Oracle9i/10g/11g在表空间中块大小可变；块大小要根据OS决定；Oracle块太大是OS块大小的倍数(2的幂次方)；Oracle块大小影响到性能；2019年7月31日Oracle数据库优化设计8/50TypeFormatFillerDBASCNBaseSCNWrapSeqFlagChkValFillerTailObjIDSCNofLastCleanoutNoofITLSlotsFreeListFlagBlockTypeITLFreelistSlotDBAofnextblockonFreelistBlockHeaderCacheLayerTransLayerITLIndexNumberTransIDUndoAddress...NoofRowsAffectedCommittedSCN/FreeSpaceCreditsTableDirectoryRowDirectoryRowFlagClusterKeyColumnNumberLockByteDataColLengthDataColLengthRows...TransactionFreeListsFreeSpaceOracle块结构与段对象-块内部结构Oracle数据段(Datasegment)存放在块里比我们想象要复杂的多：2019年7月31日Oracle数据库优化设计9/50表空间对应一个或多个数据文件；数据文件由多个Oracle块构成；Oracle块可存储下面的段：数据段(Datasegment)-tables,triggers等索引段(Indexsegment)-indexes临时段(Temporarysegment):-排序和临时表回滚段(Rollbacksegment)-undodata信息LOB段(LOBsegment)-与表的基本数据存储或分开的LOB数据Oracle块结构与段对象2019年7月31日Oracle数据库优化设计10/50内容提要Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表存储参数在9i/10g的变化表数据存储量大小估计表的修改及其它处理参考资料2019年7月31日Oracle数据库优化设计11/50创建表结构准备对象命名：名字1~30个字符号；名字只能是a~z、A~Z，也可在字符后加0~9；名字可加$、#、_；不许带空字符；最好不能为保留字（可从v$reserved_words中查）与保留字相同，可用双引号；列名的统一问题：同一种含义的列命名与类型在应用系统中统一；命名不要采用拼音或拼音缩写-重复率高、不好猜；命名采用英语或英语缩写；列名太长不好；类型能采用字符就不要采用数字类型。2019年7月31日Oracle数据库优化设计12/50创建表结构准备数据类型使用VARCHAR2(size)变长-1~4000字符CHAR(size)定长–1~2000字符NUMBER(p)整数NUMBER(p,s)定点数十进制NUMBER浮点数十进制DATE日期类型LONG变长大对象(4Gigabyte)NCHAR类似CHAR,使用2-字节编码BLOB二进制数据，4GBCLOB单字节字符数据，可达4GBNCLOB类似CLOB,支持2-字节编码BFILE引用的外部的二进制文件LONGRAW原二进制数据Oracle8/8i/9i/10g版本常用数据类型：2019年7月31日Oracle数据库优化设计13/50创建表结构准备Oracle10g一般的数据类型：数据类型说明Char(size)定长字符类型,size=2000字符Varchar(size)可变字符类型，size=4000个字符Varchar2(size)可变字符类型，size=4000个字符(Oracle建议用)Nchar(size)定长本地语言字符类型,size=2000字符Nvarchar(size)可变本地语言字符类型，size=4000个字符Nvarchar(size)可变本地语言字符类型，size=4000个字符2019年7月31日Oracle数据库优化设计14/50创建表结构准备Oracle10g一般的数据类型：数据类型说明date定长的日期类型，占7字节；可包含年月日时分秒timestape时间邮戳类型，包含可包含年月日时分秒；但是秒数的精度为1/100秒Intervalyear以年月存储的时间区的值，可以是0~9，(默认为2)Intervalday以天、小时、分、秒存储的时间值，可以是0~9，默认为2Number(p,s)数字类型，可存储等任何类型BINARY_FLOAT32Bit的单精度数据类型(仅10g/11g)BINARY_DOUBLE64Bit的单精度数据类型(仅10g/11g)2019年7月31日Oracle数据库优化设计15/50创建表结构准备Oracle10g一般的数据类型：数据类型说明long可变字符类型，可存储=2GB；Longraw可变二进制类型，可存储=2GB；raw可变二进制类型，可存储=4000；BLOB二进制大对象，可存储图像等类型数据；每数据行可存储=8~128TBCLOB字符大对象，可存储任何大文本类型数据；每数据行可存储=8~128TBNCLOB本地语言字符大对象，类似CLOB类型；BFILE包含指向OS路径的大对象定位器2019年7月31日Oracle数据库优化设计16/50创建表结构准备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-30810g2019年7月31日Oracle数据库优化设计17/50Oracle对象创建准备-数据类型数据类型选择要点：不要使用long或longraw类型；一般不要NCHAR等类型；Number(p,s)类型要满足应用系统发展需要；Number(p,s)类型在整个应用系统中要保持一致性！！应用系统设计与数据仓库考虑：列名与类型一致性问题；将来的数据整合问题；统一使用Number(p,s)类型；数字类型整数与精度要一致。2019年7月31日Oracle数据库优化设计18/50内容提要Oracle表空间与块结构与段对象Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料2019年7月31日Oracle数据库优化设计19/50创建表结构-语法建议采用命令方式：认真研究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]2019年7月31日Oracle数据库优化设计20/50创建表结构语法-子句解释子句说明Table_name表名，字母数字=30个schema模式名，默认为用户名datatype列数据类型Column_constraint列约束Table_constraintinteger最大分配次数2019年7月31日Oracle数据库优化设计21/50创建表结构语法-子句解释Storage子句说明INITIALinteger初始大小NEXTinteger下次扩展大小；在9i/10g创建本地表空间，NEXT由系统自动分配MINEXTENTSinteger最小分配次数，最小默认是2MAXEXTENTSinteger最大分配次数PCTINCREASEinteger增长百分比（=0），默认50UNLIMITEDMINEXTENTS参数，用于限制空间的增长2019年7月31日Oracle数据库优化设计22/50创建表结构语法-子句解释子句说明DEFAULT当前列的默认值（常数）INITRANS数据块中用于更新（UPDATE）的空间百分比（1-99），0表示在插入时完全填满数据块，默认为10MAXTRANS每个数据块分配的事务如口数(1-255)，默认1PCTFREE数据块的用于更新的百分比(1-99)，0表示在插入是填满整个数据库块PCTUSED数据