Oracle 存储与关键表设计(共58张PPT)

OracleRDBMS

存储与表设计赵元杰Zyj5681@2007年8月11/8/20221日期第1页，共58页。内容提要Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表数据存储量大小估计表的修改及其它处理参考资料11/8/20222日期第2页，共58页。Oracle数据库-表空间-对象TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks11/8/20223日期第3页，共58页。Oracle表空间(Tablespaces)表空间类型System/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable表空间种类永久表空间(存储表、索引)临时表空间(存储排序数据)撤消表空间(存储DML数据)11/8/20224日期第4页，共58页。表空间(tablespace)与表(Tables)表存储在表空间(Tablespaces)表由行与列构成表包含数据(表占很多块空间，称“段”)TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks11/8/20225日期第5页，共58页。Oracle表空间与数据文件表空间(Tablespace)：表空间由多个数据文件组成，如SYSTEM表空间由system01.dbf构成；表的数据存储表空间对应的数据文件里；表和索引等可分别存储在不同的表空间；数据文件结构：数据文件由多个Oracle块组成；F1.oraF2.oraF3.oraF4.oraF5.oraTablespace1Tablespace2Table1Table2Table3CREATETABLESPACEuser_dataDATAFILE‘udata.ora’SIZE10MEXTENTMANAGEMENTLOCAL…CREATETABLEmy_emp(namevarchar2(20),…TABLEASPACEuser_datapctfree5;11/8/20226日期第6页，共58页。Oracle块结构与段对象数据文件都是块构成；内存分配也是以块为单位；I/O也以块为单位；块大小在安装时选择（基本块大小）；不同的Oracle版本允许块大小不一样；目前块大小可以2k~32K；Oracle9i/10g/11g在表空间中块大小可变；块大小要根据OS决定；Oracle块太大是OS块大小的倍数(2的幂次方)；Oracle块大小影响到性能；11/8/20227日期第7页，共58页。Oracle块结构与段对象-块内部结构Oracle数据段(Datasegment)存放在块里比我们想象要复杂的多：11/8/20228日期第8页，共58页。表空间对应一个或多个数据文件；数据文件由多个Oracle块构成；Oracle块可存储下面的段：数据段(Datasegment)-tables,triggers等索引段(Indexsegment)-indexes临时段(Temporarysegment):-排序和临时表回滚段(Rollbacksegment)-undodata信息LOB段(LOBsegment)-与表的基本数据存储或分开的LOB数据Oracle块结构与段对象11/8/20229日期第9页，共58页。内容提要Oracle表空间与块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表存储参数在9i/10g的变化表数据存储量大小估计表的修改及其它处理参考资料11/8/202210日期第10页，共58页。创建表结构准备对象命名：名字1~30个字符号；名字只能是a~z、A~Z，也可在字符后加0~9；名字可加$、#、_；不许带空字符；最好不能为保留字（可从v$reserved_words中查）与保留字相同，可用双引号；

列名的统一问题：同一种含义的列命名与类型在应用系统中统一；命名不要采用拼音或拼音缩写-重复率高、不好猜；命名采用英语或英语缩写；列名太长不好；类型能采用字符就不要采用数字类型。11/8/202211日期第11页，共58页。创建表结构准备数据类型使用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版本常用数据类型：11/8/202212日期第12页，共58页。创建表结构准备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个字符11/8/202213日期第13页，共58页。创建表结构准备Oracle10g一般的数据类型：数据类型说明date定长的日期类型，占7字节；可包含年月日时分秒timestape时间邮戳类型，包含可包含年月日时分秒；但是秒数的精度为1/100秒Intervalyear以年月存储的时间区的值，可以是0~9，(默认为2)Intervalday以天、小时、分、秒存储的时间值，可以是0~9，默认为2Number(p,s)数字类型，可存储等任何类型BINARY_FLOAT32Bit的单精度数据类型(仅10g/11g)BINARY_DOUBLE64Bit的单精度数据类型(仅10g/11g)11/8/202214日期第14页，共58页。创建表结构准备Oracle10g一般的数据类型：数据类型说明long可变字符类型，可存储<=2GB；Longraw可变二进制类型，可存储<=2GB；raw可变二进制类型，可存储<=4000；BLOB二进制大对象，可存储图像等类型数据；每数据行可存储<=8~128TBCLOB字符大对象，可存储任何大文本类型数据；每数据行可存储<=8~128TBNCLOB本地语言字符大对象，类似CLOB类型；BFILE包含指向OS路径的大对象定位器11/8/202215日期第15页，共58页。创建表结构准备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-30810g11/8/202216日期第16页，共58页。Oracle对象创建准备-数据类型数据类型选择要点：不要使用long或longraw类型；一般不要NCHAR等类型；Number(p,s)类型要满足应用系统发展需要；Number(p,s)类型在整个应用系统中要保持一致性！！应用系统设计与数据仓库考虑：列名与类型一致性问题；将来的数据整合问题；统一使用Number(p,s)类型；数字类型整数与精度要一致。11/8/202217日期第17页，共58页。内容提要Oracle表空间与块结构与段对象Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料11/8/202218日期第18页，共58页。创建表结构-语法建议采用命令方式：认真研究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]11/8/202219日期第19页，共58页。创建表结构语法-子句解释子句说明Table_name表名，字母数字<=30个schema模式名，默认为用户名datatype列数据类型Column_constraint列约束Table_constraint

integer最大分配次数11/8/202220日期第20页，共58页。创建表结构语法-子句解释Storage子句说明INITIALinteger初始大小NEXTinteger下次扩展大小；在9i/10g创建本地表空间，NEXT由系统自动分配MINEXTENTSinteger最小分配次数，最小默认是2MAXEXTENTSinteger最大分配次数PCTINCREASEinteger增长百分比（>=0），默认50UNLIMITEDMINEXTENTS参数，用于限制空间的增长11/8/202221日期第21页，共58页。创建表结构语法-子句解释子句说明DEFAULT当前列的默认值（常数）INITRANS数据块中用于更新（UPDATE）的空间百分比（1-99），0表示在插入时完全填满数据块，默认为10MAXTRANS每个数据块分配的事务如口数(1-255)，默认1PCTFREE数据块的用于更新的百分比(1-99)，0表示在插入是填满整个数据库块PCTUSED数据块保留的可用空间的最小百分比。取值1-99，默认为40。实际上PCTUSED是控制自由空间的可用性。11/8/202222日期第22页，共58页。创建表结构语法-块变化建议采用命令方式：认真研究Oracle的CREATETABLE子句；关键的项不要采用默认值，语法如下：11/8/202223日期第23页，共58页。内容提要Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料11/8/202224日期第24页，共58页。创建表结构样例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；11/8/202225日期第25页，共58页。创建表结构样例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,…11/8/202226日期第26页，共58页。内容提要Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料11/8/202227日期第27页，共58页。存储参数在9i/10g的变化为什么9i/10g取消段的空间扩展设置：表数据的增长与空间的不断减少是矛盾的关键；NEXT表示必须分配一组连序的Oracle块；如果表空间无NEXT所要求的连续块，则分配失败；9i/10g为表扩展空间时不参考NEXT，而是根据表的增长与实际表空间剩余的空间来确定。存储参数设置：字典类型表空间-可设置STORAGE的所有项；本地管理表空间-仅STORAGE的INITIAL有效；11/8/202228日期第28页，共58页。存储参数在9i/10g的变化块控制参数设置：9i/10g本地管理表空间ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO，Oracle根据dba_tables.avg_row_len来调整PCTUSED的大小。ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL，Oracle根据你的PCTUSED设置来调整PCTUSED的大小；SYSTEM表空间ASSM为MANUAL，USERS表空间ASSM为AUTO(见后面脚本)；11/8/202229日期第29页，共58页。存储参数在9i/10g的变化创建表结构前：根据表数据变化特点，创建相应的表空间；表空间的对应数据文件大小要合理；在创建表时指定到创建好的表空间上，并根据表空间是否是字典/本地来确定是否指定NEXT等参数：Setlong9999SELECTdbms_metadata.get_ddl('TABLESPACE','SYSTEM')fromdual;CREATETABLESPACE"SYSTEM"DATAFILE'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10\SYSTEM01.DBF'SIZE314572800AUTOEXTENDONNEXT10485760MAXSIZE32767MLOGGINGONLINEPERMANENTBLOCKSIZE8192EXTENTMANAGEMENTLOCAL

AUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL11/8/202230日期第30页，共58页。存储参数在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)PCTFREE5PCTUSED85TABLESPACESYSTEM

STORAGE(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----好好观察上面的结果--11/8/202231日期第31页，共58页。存储参数在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参数(见前面样例)；11/8/202232日期第32页，共58页。存储参数在9i/10g的变化LMT为UNIFORM情况：创建的对象的EXTENT都统一用此尺寸去划分大小；为表指定NEXT，PCTINCREASE时，这些参数只在创建时有效，之后的扩展无效；11/8/202233日期第33页，共58页。存储参数在9i/10g的变化LMT指定UNIFORM例子：1.创建一表空间，UNIFORMSIZE为3M：2.在上面表空间上创建一个表：CREATETABLESPACEASSMDATAFILE'D:\oracle\oradata\encore\ASSM01.DBF'SIZE50MAUTOEXTENDOFFEXTENTMANAGEMENTLOCALUNIFORMSIZE3M

SEGMENTSPACEMANAGEMENTAUTO;CREATETABLEASSMTAB(COL1NUMBER(3))STORAGE(INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100)TABLESPACEASSM;11/8/202234日期第34页，共58页。存储参数在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------------------------------------------------------------------------------TABLEASSM03145728384TABLEASSABLEASSM23145728384TABLEASSM33145728384TABLEASSM43145728384TABLEASSM53145728384SQL>selecttable_name,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASEfromuser_tableswheretable_name='ASSMTAB';TABLE_NAMEINITIAL_EXTENTNEXT_EXTENTPCT_INCREASE------------------------------------------------ASSMTAB3178257923145728011/8/202235日期第35页，共58页。表数据存储量大小估计-方法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

一般习惯:总是保守的多11/8/202236日期第36页，共58页。表数据存储量大小估计-方法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是关键!11/8/202237日期第37页，共58页。表数据存储量大小估计-方法3从存在的表中分析以得到统计信息：Step1:ANALYZETABLEEMPESTIMATE[COMPUTE]STATISTICS;Step2:SELECT(NUM_ROWS*AVG_ROW_LEN)FROMUSER_TABLESWHERETABLE_NAME=‘EMP’;11/8/202238日期第38页，共58页。表数据存储量大小估计-检查表增长检查段的扩展次数是否接近MAXEXTENTSSelectsubstr(owner||‘.’||segment_name,1,25)“object”, MaxextentsFromsys.Dba_extentsWheremaxextents-extents<=5Orderbyextents;11/8/202239日期第39页，共58页。内容提要Oracle块结构与段对象创建表结构准备创建表结构语法与参数解释创建带有存储控制的样例表9i/10g存储参数与块参数的变化表的修改及其它处理参考资料11/8/202240日期第40页，共58页。修改表结构-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]11/8/202241日期第41页，共58页。修改表结构-AlterTable表结构的修改问题：不要经常修改表结构；修改表结构带来许多问题-性能、一致性等；修改表结构要分析对其它表的影响。目前可修改：增加列（字段）宽度或增加新列；减少列（字段）宽度（该列必须无数据）；增加一个约束或去掉约束；修改存储分配；记录表已作过BACKUP；删除已存在的列；重新定位和组织表；将表标识为不可用。11/8/202242日期第42页，共58页。修改表结构删除表结构的某个列：删除某个列的语法：ALTERTABLE...DROPCOLUMN[CASCADECONSTRAINTS];

删除某个列的例子：删除列后不能恢复：SQL>ALTERTABLEempDROPCOLUMNcomm;表已更改。11/8/202243日期第43页，共58页。修改表结构标识表结构的某个列不可用：标识某个列不可用的语法：ALTERTABLE...SETUNUSEDCOLUMN标识某个列不可用及删除的例子：标识某个列不用后就不能再恢复为可用：SQL>Altertableemp2setUNUSEDCOLUMNcomm;表已更改。SQL>ALTERTABLEemp2DROPUNUSEDCOLUMNSCHECKPOINT20;

表已更改。

SQL>11/8/202244日期第44页，共58页。删除表结构删除表结构：就是从Oracle系统中删除该表的数据与结构；语法：删除表结构将影响：指向本表的外部键；表中的索引；本表的触发器；本表中的分区；本表的快照；本表的角色和用户权限；加在本表的所有限制。DROPTABLE[user.]table_name[CASCADECONSTRAINTS]11/8/202245日期第45页，共58页。删除表结构删除表结构注意：删除某个表前要查询该是否其它表存在关系；不要轻易带CASCADE子句：删除表结构建议做：查询某个表的子表：DROPTABLEDEPTCASCADECONSTRAINTS;表已删除./*找出模式下的某个表的子表*/SELECTtable_name“childtables”FROMuser_constraintsWHEREr_constraint_nameIN(SELECTconstraint_nameFROMuser_constraints

WHEREtable_name=UPPER(‘&parent_table’)ANDconstraint_type=’P’);11/8/202246日期第46页，共58页。释放不用的空间记录有最高水位“Highwatermark”可释放不使用的扩展FreespaceafterdeleteUnusedblockHighwatermarkBefore

deallocationUsedblockALTERTABLEemployeesDEALLOCATEUNUSED;After

deallocation11/8/202247日期第47页，共58页。10g段收缩-表的存储空间收缩11/8/202248日期第48页，共58页。10g段收缩-表的存储空间收缩Oracle9i提供:删除表,再重建,重加载数据ALTERTABLE…MOVE命令但是Oracle9i:表必须是Offline联机只能重组ALTERTABLEempMOVETABLESPACESTORAGE(INITIAL4MNext2Mminextents2);11/8/202249日期第49页，共58页。10g段收缩-表的存储空间收缩Oracle10g联机段收缩可以:可Online操作不需要额外磁盘空间空间收缩带shrink子句11/8/2022