Oracle 存储与关键表设计

OracleRDBMS

存储与表设计赵元杰2007年8月1/18/20231内容提要Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表数据存储量大小估计表的修改及其它处理参考资料1/18/20232Oracle数据库-表空间-对象TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks1/18/20233Oracle表空间(Tablespaces)表空间类型System/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable表空间种类永久表空间(存储表、索引)临时表空间(存储排序数据)撤消表空间(存储DML数据)1/18/20234表空间(tablespace)与表(Tables)表存储在表空间(Tablespaces)表由行与列构成表包含数据(表占很多块空间，称“段”)TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks1/18/20235Oracle表空间与数据文件表空间(Tablespace)：表空间由多个数据文件组成，如SYSTEM表空间由system01.dbf构成；表的数据存储表空间对应的数据文件里；表和索引等可分别存储在不同的表空间；数据文件结构：数据文件由多个Oracle块组成；F1.oraF2.oraF3.oraF4.oraF5.oraTablespace1Tablespace2Table1Table2Table3CREATETABLESPACEuser_dataDATAFILE‘udata.ora’SIZE10MEXTENTMANAGEMENTLOCAL…CREATETABLEmy_emp(namevarchar2(20),…TABLEASPACEuser_datapctfree5;1/18/20236Oracle块结构与段对象数据文件都是块构成；内存分配也是以块为单位；I/O也以块为单位；块大小在安装时选择（基本块大小）；不同的Oracle版本允许块大小不一样；目前块大小可以2k~32K；Oracle9i/10g/11g在表空间中块大小可变；块大小要根据OS决定；Oracle块太大是OS块大小的倍数(2的幂次方)；Oracle块大小影响到性能；1/18/20237Oracle块结构与段对象-块内部结构Oracle数据段(Datasegment)存放在块里比我们想象要复杂的多：1/18/20238表空间对应一个或多个数据文件；数据文件由多个Oracle块构成；Oracle块可存储下面的段：数据段(Datasegment)-tables,triggers等索引段(Indexsegment)-indexes临时段(Temporarysegment):-排序和临时表回滚段(Rollbacksegment)-undodata信息LOB段(LOBsegment)-与表的基本数据存储或分开的LOB数据Oracle块结构与段对象1/18/20239内容提要Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表存储参数在9i/10g的变化表数据存储量大小估计表的修改及其它处理参考资料1/18/202310创建表结构构准备对象命名：：名字1~30个字符符号；名字只能是是a~z、、A~Z，，也可在在字符后加加0~9；；名字可加$、#、_；不许带带空字符；；最好不能为为保留字（（可从v$reserved_words中查）与保留字相相同，可用用双引号；；列名的统一一问题：同一种含义义的列命名名与类型在在应用系统统中统一；；命名不要采采用拼音或或拼音缩写写-重复率率高、不好好猜；命名采用英英语或英语语缩写；列名太长不不好；类型能采用用字符就不不要采用数数字类型。1/1/202311创建表结构准准备数据类型使用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版本本常用数据类类型：1/1/202312创建表结构准准备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个字符1/1/202313创建表结构准准备Oracle10g一一般的数据据类型：数据类型说明date定长的日期类型，占7字节；可包含年月日时分秒timestape时间邮戳类型，包含可包含年月日时分秒；但是秒数的精度为1/100秒Intervalyear以年月存储的时间区的值，可以是0~9，(默认为2)Intervalday以天、小时、分、秒存储的时间值，可以是0~9，默认为2Number(p,s)数字类型，可存储等任何类型BINARY_FLOAT32Bit的单精度数据类型(仅10g/11g)BINARY_DOUBLE64Bit的单精度数据类型(仅10g/11g)1/1/202314创建表结构准准备Oracle10g一一般的数据据类型：数据类型说明long可变字符类型，可存储<=2GB；Longraw可变二进制类型，可存储<=2GB；raw可变二进制类型，可存储<=4000；BLOB二进制大对象，可存储图像等类型数据；每数据行可存储<=8~128TBCLOB字符大对象，可存储任何大文本类型数据；每数据行可存储<=8~128TBNCLOB本地语言字符大对象，类似CLOB类型；BFILE包含指向OS路径的大对象定位器1/1/202315创建表结构准准备10g新BINARY_FLOAT数据类型：32Bit的单精度度数据类型BINARY_FLOAT；BINARY_FLOAT以5个字节存储储，含一个长长度字节；10gBINARY_DOUBLE：64Bit的双精度度数据类型BINARY_DOUBLE；BINARY_DOUBLE以9个字节存储储，含一个长长度字节；BINARY-FLOATBINARY-DOUBLEMAXIMUMFINITEVALUE3.40282+38F1.79769313486231E+30810g1/1/202316Oracle对对象象创创建建准准备备-数数据据类类型型数据据类类型型选选择择要要点点：：不要要使使用用long或或longraw类类型型；；一般般不不要要NCHAR等等类类型型；；Number(p,s)类类型型要要满满足足应应用用系系统统发发展展需需要要；；Number(p,s)类类型型在在整整个个应应用用系系统统中中要要保保持持一一致致性性！！！！应用用系系统统设设计计与与数数据据仓仓库库考考虑虑：：列名名与与类类型型一一致致性性问问题题；；将来来的的数数据据整整合合问问题题；；统一一使使用用Number(p,s)类类型型；；数字字类类型型整整数数与与精精度度要要一一致致。。1/1/202317内容容提提要要Oracle表表空空间间与与块块结结构构与与段段对对象象Oracle块块结结构构与与段段对对象象创建建表表结结构构准准备备创建建表表结结构构语语法法与与参参数数解解释释创建建带带有有存存储储控控制制的的样样例例表表9i/10g存存储储参参数数与与块块参参数数的的变变化化表的的修修改改及及其其它它处处理理参考考资资料料1/1/202318创建表表结构构-语语法建议采采用命命令方方式：：认真研研究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]1/1/202319创建表结构构语法-子子句解释子句说明Table_name表名，字母数字<=30个schema模式名，默认为用户名datatype列数据类型Column_constraint列约束Table_constraint

integer最大分配次数1/1/202320创建表结结构语法法-子句句解释Storage子句说明INITIALinteger初始大小NEXTinteger下次扩展大小；在9i/10g创建本地表空间，NEXT由系统自动分配MINEXTENTSinteger最小分配次数，最小默认是2MAXEXTENTSinteger最大分配次数PCTINCREASEinteger增长百分比（>=0），默认50UNLIMITEDMINEXTENTS参数，用于限制空间的增长1/1/202321创建表结结构语法法-子句句解释子句说明DEFAULT当前列的默认值（常数）INITRANS数据块中用于更新（UPDATE）的空间百分比（1-99），0表示在插入时完全填满数据块，默认为10MAXTRANS每个数据块分配的事务如口数(1-255)，默认1PCTFREE数据块的用于更新的百分比(1-99)，0表示在插入是填满整个数据库块PCTUSED数据块保留的可用空间的最小百分比。取值1-99，默认为40。实际上PCTUSED是控制自由空间的可用性。1/1/202322创建表结结构语法法-块变变化建议采用用命令方方式：认真研究究Oracle的CREATETABLE子句句；关键的项项不要采采用默认认值，语语法如下下：1/1/202323内容提要Oracle块结构与与段对象创建表结构准准备创建表结构语语法与参数解解释创建带有存储储控制的样例例表9i/10g存储参数与与块参数的变变化表的修改及其其它处理参考资料1/1/202324创建表结构样样例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；1/1/202325创建建表表结结构构样样例例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,……1/1/202326内容容提提要要Oracle块块结结构构与与段段对对象象创建建表表结结构构准准备备创建建表表结结构构语语法法与与参参数数解解释释创建建带带有有存存储储控控制制的的样样例例表表9i/10g存存储储参参数数与与块块参参数数的的变变化化表的的修修改改及及其其它它处处理理参考考资资料料1/1/202327存储参数数在9i/10g的变变化为什么9i/10g取取消段的的空间扩扩展设置置：表数据的的增长与与空间的的不断减减少是矛矛盾的关关键；NEXT表示示必须分分配一组组连序的的Oracle块；如果表空空间无NEXT所要求求的连续续块，则则分配失失败；9i/10g为为表扩扩展空间间时不参参考NEXT，，而是根根据表的的增长与与实际表表空间剩剩余的空空间来确确定。存储参数数设置：：字典类型型表空间间-可设设置STORAGE的的所有项项；本地管理理表空间间-仅STORAGE的INITIAL有有效；1/1/202328存储参参数在在9i/10g的变变化块控制制参数数设置置：9i/10g本地管管理表表空间间ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO，Oracle根据据dba_tables.avg_row_len来调调整PCTUSED的大大小。。ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL，Oracle根据据你的的PCTUSED设设置来来调整整PCTUSED的的大小小；SYSTEM表表空间间ASSM为MANUAL，，USERS表表空间间ASSM为AUTO(见后后面脚脚本)；1/1/202329存储参参数在在9i/10g的变变化创建表表结构构前：：根据表表数据据变化化特点点，创创建相相应的的表空空间；；表空间间的对对应数数据文文件大大小要要合理理；在创建建表时时指定定到创创建好好的表表空间间上，，并根根据表表空间间是否否是字典/本地地来确定定是否否指定定NEXT等参参数：：Setlong9999SELECTdbms_metadata.get_ddl('TABLESPACE','SYSTEM')fromdual;CREATETABLESPACE"SYSTEM"DATAFILE'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10\SYSTEM01.DBF'SIZE314572800AUTOEXTENDONNEXT10485760MAXSIZE32767MLOGGINGONLINEPERMANENTBLOCKSIZE8192EXTENTMANAGEMENTLOCALAUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL1/1/202330存储参数在在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----好好观观察上面的的结果--1/1/202331存储参数在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参数(见前面样例)；1/1/202332存储参参数在在9i/10g的变变化LMT为UNIFORM情况况：创建的的对象象的EXTENT都都统一一用此此尺寸寸去划划分大大小；；为表指指定NEXT，，PCTINCREASE时时，，这些些参数数只在在创建建时有有效，，之后后的扩扩展无无效；；1/1/202333存储参参数在在9i/10g的变变化LMT指定定UNIFORM例例子子：1.创创建一一表空空间，，UNIFORMSIZE为为3M：：2.在在上面面表空空间上上创建建一个个表：：CREATETABLESPACEASSMDATAFILE'D:\oracle\oradata\encore\ASSM01.DBF'SIZE50MAUTOEXTENDOFFEXTENTMANAGEMENTLOCALUNIFORMSIZE3MSEGMENTSPACEMANAGEMENTAUTO;CREATETABLEASSMTAB(COL1NUMBER(3))STORAGE(INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100)TABLESPACEASSM;1/1/202334存储参数数在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------------------------------------------------------------------------------TABLEASSM03145728384TABLEASSM23145728384TABLEASSM33145728384TABLEASSM43145728384TABLEASSM53145728384SQL>selecttable_name,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASEfromuser_tableswheretable_name='ASSMTAB';TABLE_NAMEINITIAL_EXTENTNEXT------------------------------------------------ASSMTAB317825792314572801/1/202335表数据存存储量大大小估计计-方法法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一般习惯:总是保守守的多1/1/202336表数据存储量量大小估计-方法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是关键键!1/1/202337表数据据存储储量大大小估估计-方法法3从存在在的表表中分分析以以得到到统计计信息息：Step1:ANALYZETABLEEMPESTIMATE[COMPUTE]STATISTICS;Step2:SELECT(NUM_ROWS*AVG_ROW_LEN)FROMUSER_TABLESWHERETABLE_NAME=‘‘EMP’;1/1/202338表数数据据存存储储量量大大小小估估计计-检检查查表表增增长长检查查段段的的扩扩展展次次数数是是否否接接近近MAXEXTENTSSelectsubstr(owner||‘‘.’’||segment_name,1,25)““object””,MaxextentsFromsys.Dba_extentsWheremaxextents-extents<=5Orderbyextents;1/1/202339内容提要要Oracle块块结构构与段对对象创建表结结构准备备创建表结结构语法法与参数数解释创建带有有存储控控制的样样例表9i/10g存存储参数数与块参参数的变变化表的修改改及其它它处理参考资料料1/1/202340修改表结结构-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]1/1/202341修改表结构-AlterTable表结构的修改改问题：不要经常修改改表结构；修改表结构带带来许多问题题-性能、一一致性等；修改表结构要要分析对其它它表的影响。。目前可修改：：增加列（字段段）宽度或增增加新列；减少列（字段段）宽度（该该列必须无数数据）；增加一个约束束或去掉约束束；修改存储分配配；记录表已作过过BACKUP；删除已存在的的列；重新定位和组组织表；将表标识为不不可用。1/1/202342修改表结构删除表结构构的某个列列：删除某个列列的语法：：ALTERTABLE...DROPCOLUMN[CASCADECONSTRAINTS];删除某个列列的例子：：删除列后不不能恢复：：SQL>ALTERTABLEempDROPCOLUMNcomm;表已更改。。1/1/202343修改表结构构标识表结构构的某个列列不可用：：标识某个列列不可用的的语法：ALTERTABLE...SETUNUSEDCOLUMN标识某个列列不可用及及删除的例例子：标识某个列列不用后就就不能再恢恢复为可用用：SQL>Altertableemp2setUNUSEDCOLUMNcomm;表已已更更改改。。SQL>ALTERTABLEemp2DROPUNUSEDCOLUMNSCHECKPOINT20;表已已更更改改。。SQL>1/1/202344删除表表结构构删除表结构构：就是从Oracle系统中删删除该表的的数据与结结构；语法：删除表结构构将影响：：指向本表的的外部键；；表中的索引引；本表的触发发器；本表中的分分区；本表的快照照；本表的角色色和用户权权限；加在本表的的所有限制制。DROPTABLE[user.]table_name[CASCADECONSTRAINTS]1/1/202345删除表结构构删除表结构构注意：删除某个表表前要查询询该是否其其它表存在在关系；不要轻易带带CASCADE子子句：删除表结结构建议议做：查询某个个表的子子表：DROPTABLEDEPTCASCADECONSTRAINTS;表已删除除./*找出模式式下的某某个表的的子表*/SELECTtable_name““childtables””FROMuser_constraintsWHEREr_constraint_nameIN(SELECTconstraint_nameFROMuser_constraintsWHEREtable_name=UPPER(‘&parent_table’)ANDconstraint_type=’P’);1/1/202346释放不用用的空间间记录有最最高水位位“Highwatermark””可释放不不使用的的扩展FreespaceafterdeleteUnusedblockHighwatermarkBeforedeallocationUsedblockALTERTABLEemployeesDEALLOCATEUNUSED;Afterdeallocation1/1/20234710g段段收缩-表的存存储空间间收缩1/1/20234810g段收缩缩-表的存储储空间收缩Oracle9i提提供:删除表,再重重建,重加载载数据ALTERTABLE…MOVE命令但是Oracle9i:表必须是Offline联机只能重组组ALTERTABLEe