Oracle 存儲與關鍵表設計

OracleRDBMS

存儲與表設計趙元杰2007年8月1/11/20231內容提要Oracle表空間與塊結構與段對象創(chuàng)建表結構準備創(chuàng)建表結構語法與參數解釋創(chuàng)建帶有存儲控制的樣例表9i/10g存儲參數與塊參數的變化表數據存儲量大小估計表的修改及其它處理參考資料1/11/20232Oracle數據庫-表空間-對象TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks1/11/20233Oracle表空間(Tablespaces)表空間類型System/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportable表空間種類永久表空間(存儲表、索引)臨時表空間(存儲排序數據)撤消表空間(存儲DML數據)1/11/20234表空間(tablespace)與表(Tables)表存儲在表空間(Tablespaces)表由行與列構成表包含數據(表占很多塊空間，稱“段”)TablespaceTables,indexes,etc.DatafilesSegmentExtentBlocks1/11/20235Oracle表空間與數據文件表空間(Tablespace)：表空間由多個數據文件組成，如SYSTEM表空間由system01.dbf構成；表的數據存儲表空間對應的數據文件里；表和索引等可分別存儲在不同的表空間；數據文件結構：數據文件由多個Oracle塊組成；F1.oraF2.oraF3.oraF4.oraF5.oraTablespace1Tablespace2Table1Table2Table3CREATETABLESPACEuser_dataDATAFILE‘udata.ora’SIZE10MEXTENTMANAGEMENTLOCAL…CREATETABLEmy_emp(namevarchar2(20),…TABLEASPACEuser_datapctfree5;1/11/20236Oracle塊結構與段對象數據文件都是塊構成；內存分配也是以塊為單位；I/O也以塊為單位；塊大小在安裝時選擇（基本塊大?。?；不同的Oracle版本允許塊大小不一樣；目前塊大小可以2k~32K；Oracle9i/10g/11g在表空間中塊大小可變；塊大小要根據OS決定；Oracle塊太大是OS塊大小的倍數(2的冪次方)；Oracle塊大小影響到性能；1/11/20237Oracle塊結構與段對象-塊內部結構Oracle數據段(Datasegment)存放在塊里比我們想象要復雜的多：1/11/20238表空間對應一個或多個數據文件；數據文件由多個Oracle塊構成；Oracle塊可存儲下面的段：數據段(Datasegment)-tables,triggers等索引段(Indexsegment)-indexes臨時段(Temporarysegment):-排序和臨時表回滾段(Rollbacksegment)-undodata信息LOB段(LOBsegment)-與表的基本數據存儲或分開的LOB數據Oracle塊結構與段對象1/11/20239內容提要Oracle表空間與塊結構與段對象創(chuàng)建表結構準備創(chuàng)建表結構語法與參數解釋創(chuàng)建帶有存儲控制的樣例表存儲參數在9i/10g的變化表數據存儲量大小估計表的修改及其它處理參考資料1/11/202310創(chuàng)建表結結構準備備對象命名名：名字1~30個個字符號號；名字只能能是a~z、A~Z，，也可可在字符符后加0~9；；名字可加加$、#、_；；不許帶帶空字符符；最好不能能為保留留字（可可從v$reserved_words中查）與保留字字相同，，可用雙雙引號；；列名的統(tǒng)統(tǒng)一問題題：同一種含含義的列列命名與與類型在在應用系系統(tǒng)中統(tǒng)統(tǒng)一；；命名不要要采用拼拼音或拼拼音縮寫寫-重復復率高、、不好猜猜；命名采用用英語或或英語縮縮寫；列名太長長不好；；類型能采采用字符符就不要要采用數數字類型型。12/30/202211創(chuàng)建表結構準準備數據類型使用VARCHAR2(size)變長-1~4000字符CHAR(size)定長–1~2000字符NUMBER(p)整數NUMBER(p,s)定點數十進制NUMBER浮點數十進制DATE日期類型LONG變長大對象(4Gigabyte)NCHAR類似CHAR,使用2-字節(jié)編碼BLOB二進制數據，4GBCLOB單字節(jié)字符數據，可達4GBNCLOB類似CLOB,支持2-字節(jié)編碼BFILE引用的外部的二進制文件LONGRAW原二進制數據Oracle8/8i/9i/10g版本本常用數據類類型：12/30/202212創(chuàng)建表結構準準備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個字符12/30/202213創(chuàng)建表結構準準備Oracle10g一一般的數據據類型：數據類型說明date定長的日期類型，占7字節(jié)；可包含年月日時分秒timestape時間郵戳類型，包含可包含年月日時分秒；但是秒數的精度為1/100秒Intervalyear以年月存儲的時間區(qū)的值，可以是0~9，(默認為2)Intervalday以天、小時、分、秒存儲的時間值，可以是0~9，默認為2Number(p,s)數字類型，可存儲等任何類型BINARY_FLOAT32Bit的單精度數據類型(僅10g/11g)BINARY_DOUBLE64Bit的單精度數據類型(僅10g/11g)12/30/202214創(chuàng)建表結構構準備Oracle10g一般般的數據類類型：數據類型說明long可變字符類型，可存儲<=2GB；Longraw可變二進制類型，可存儲<=2GB；raw可變二進制類型，可存儲<=4000；BLOB二進制大對象，可存儲圖像等類型數據；每數據行可存儲<=8~128TBCLOB字符大對象，可存儲任何大文本類型數據；每數據行可存儲<=8~128TBNCLOB本地語言字符大對象，類似CLOB類型；BFILE包含指向OS路徑的大對象定位器12/30/202215創(chuàng)建表結結構準備備10g新新BINARY_FLOAT數據類型型：32Bit的的單精精度數據據類型BINARY_FLOAT；；BINARY_FLOAT以以5個字節(jié)節(jié)存儲，，含一個個長度字字節(jié)；10gBINARY_DOUBLE：64Bit的的雙精精度數據據類型BINARY_DOUBLE；BINARY_DOUBLE以9個字節(jié)節(jié)存儲，，含一個個長度字字節(jié)；BINARY-FLOATBINARY-DOUBLEMAXIMUMFINITEVALUE3.40282+38F1.79769313486231E+30810g12/30/202216Oracle對對象創(chuàng)創(chuàng)建準備備-數據據類型數據類型型選擇要要點：不要使用用long或longraw類型型；一般不要要NCHAR等等類型；；Number(p,s)類型型要滿足足應用系系統(tǒng)發(fā)展展需要；；Number(p,s)類型型在整個個應用系系統(tǒng)中要要保持一一致性?。?！應用系統(tǒng)統(tǒng)設計與與數據倉倉庫考慮慮：列名與類類型一致致性問題題；將來的數數據整合合問題；；統(tǒng)一使用用Number(p,s)類類型；數字類型型整數與與精度要要一致。。12/30/202217內容提要要Oracle表表空間間與塊結結構與段段對象Oracle塊塊結構構與段對對象創(chuàng)建表結結構準備備創(chuàng)建表結結構語法法與參數數解釋創(chuàng)建帶有有存儲控控制的樣樣例表9i/10g存存儲參數數與塊參參數的變變化表的修改改及其它它處理參考資料料12/30/202218創(chuàng)建表表結構構-語語法建議采采用命命令方方式：：認真研研究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]12/30/202219創(chuàng)建表表結構構語法法-子子句解解釋子句說明Table_name表名，字母數字<=30個schema模式名，默認為用戶名datatype列數據類型Column_constraint列約束Table_constraint

integer最大分配次數12/30/202220創(chuàng)建表結構構語法-子子句解釋Storage子句說明INITIALinteger初始大小NEXTinteger下次擴展大?。辉?i/10g創(chuàng)建本地表空間，NEXT由系統(tǒng)自動分配MINEXTENTSinteger最小分配次數，最小默認是2MAXEXTENTSinteger最大分配次數PCTINCREASEinteger增長百分比（>=0），默認50UNLIMITEDMINEXTENTS參數，用于限制空間的增長12/30/202221創(chuàng)建表結構構語法-子子句解釋子句說明DEFAULT當前列的默認值（常數）INITRANS數據塊中用于更新（UPDATE）的空間百分比（1-99），0表示在插入時完全填滿數據塊，默認為10MAXTRANS每個數據塊分配的事務如口數(1-255)，默認1PCTFREE數據塊的用于更新的百分比(1-99)，0表示在插入是填滿整個數據庫塊PCTUSED數據塊保留的可用空間的最小百分比。取值1-99，默認為40。實際上PCTUSED是控制自由空間的可用性。12/30/202222創(chuàng)建表結結構語法法-塊變變化建議采用用命令方方式：認真研究究Oracle的CREATETABLE子句句；關鍵的項項不要采采用默認認值，語語法如下下：12/30/202223內容提要Oracle塊結構與與段對象創(chuàng)建表結構準準備創(chuàng)建表結構語語法與參數解解釋創(chuàng)建帶有存儲儲控制的樣例例表9i/10g存儲參數與與塊參數的變變化表的修改及其其它處理參考資料12/30/202224創(chuàng)建表表結構構樣例例1-塊控控制參參數createtableuser_dflow(user_namevarchar2(30),/*用用戶戶名名*/bj_datedate,/*日日期期及時時間*/...day_fee_innumber(16,6),/*日日入入流量量費用用*/day_fee_outnumber(16,6)/*日日出出流量量費用用*/)TABLESPACEusersPCTFREE5PCTUSED80/創(chuàng)建網網絡計計費數據流流量明明細表表user_dflow：：根據上上網記記錄的的信息息很少修修改特點，，設置置PCTFREE為為5；而而希望望盡快快釋放放刪除除的塊塊空間間而將將PCTUSED設設85；；該表一一開始始先分分配100MB，用用完再再分配配50MB；12/30/202225創(chuàng)建表表結構構樣例例2-存儲儲分配配參數數createtableuser_dflow(user_namevarchar2(30),/*用用戶戶名名*/bj_datedate,/*日日期期及及時時間間*/...day_fee_innumber(16,6),/*日日入入流流量量費費用用*/day_fee_outnumber(16,6)/*日日出出流流量量費費用用*/)TABLESPACEusersPCTFREE5PCTUSED80STORAGE(INITIAL120MNEXT60MMINEXTENTS1PCTINCREASE0MAXEXTENTS9999)創(chuàng)建建網網絡絡計計費費數據據流流量量明明細細表表user_dflow：：對user_dflow表表的的年年數數據據量量進進行行估估計計：：假假設設年年數數據據量量100MB，，則則可可分分配配120MB；；當分分配配120MB用用完完后后，，由由再再分分配配60MB,……12/30/202226內容容提提要要Oracle塊塊結結構構與與段段對對象象創(chuàng)建建表表結結構構準準備備創(chuàng)建建表表結結構構語語法法與與參參數數解解釋釋創(chuàng)建建帶帶有有存存儲儲控控制制的的樣樣例例表表9i/10g存存儲儲參參數數與與塊塊參參數數的的變變化化表的修改改及其它它處理參考資料料12/30/202227存儲參數數在9i/10g的變變化為什么9i/10g取取消段的的空間擴擴展設置置：表數據的的增長與與空間的的不斷減減少是矛矛盾的關關鍵；NEXT表示示必須分分配一組組連序的的Oracle塊；如果表空空間無NEXT所要求求的連續(xù)續(xù)塊，則則分配失失?。?i/10g為為表擴擴展空間間時不參參考NEXT，，而是根根據表的的增長與與實際表表空間剩剩余的空空間來確確定。存儲參數數設置：：字典類型型表空間間-可設設置STORAGE的的所有項項；本地管理理表空間間-僅STORAGE的INITIAL有有效；12/30/202228存儲參數數在9i/10g的變變化塊控制參參數設置置：9i/10g本地管理理表空間間ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO，Oracle根據dba_tables.avg_row_len來調整整PCTUSED的大大小。ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL，Oracle根據你你的PCTUSED設設置來調調整PCTUSED的的大小；；SYSTEM表表空間ASSM為MANUAL，，USERS表表空間ASSM為AUTO(見后面面腳本)；12/30/202229存儲參數在在9i/10g的變變化創(chuàng)建表結構構前：根據表數據據變化特點點，創(chuàng)建相相應的表空空間；表空間的對對應數據文文件大小要要合理；在創(chuàng)建表時時指定到創(chuàng)創(chuàng)建好的表表空間上，，并根據表表空間是否否是字典/本地地來確定是否否指定NEXT等參參數：Setlong9999SELECTdbms_metadata.get_ddl('TABLESPACE','SYSTEM')fromdual;CREATETABLESPACE"SYSTEM"DATAFILE'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10\SYSTEM01.DBF'SIZE314572800AUTOEXTENDONNEXT10485760MAXSIZE32767MLOGGINGONLINEPERMANENTBLOCKSIZE8192EXTENTMANAGEMENTLOCALAUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL12/30/202230存儲參數在在9i/10g的變變化ASSM下下創(chuàng)建表結結構：觀察下面腳腳本運行的的結果：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----好好觀觀察上面的的結果--12/30/202231存儲參數在在9i/10g的變變化LMT的extent_management_clause：：AUTOALLOCATE(默認值)區(qū)的大小小由系統(tǒng)來來管理(忽忽略NEXT等參數數)；塊小于16K則分配配64K；；大于16K，則分分配1M；；然后再根據據表增長情情況分配區(qū)區(qū)，大小是是64K,1M,8M…LMT為UNIFORM情況況：UNIFORM規(guī)定定每次擴展展都一樣的的大?。籙NIFORM默認認為5個Oracle塊；；segment_management_clause：SEGMENTSPACEMANAGEMENTMANUAL/AUTO；如果SSM=AUTO，則Oracle忽略對對像的PCTUSED,FREELIST和和FREELISTGROUPS參參數(見前面樣例例)；12/30/202232存儲參數在9i/10g的變化LMT為UNIFORM情況：創(chuàng)建的對象的的EXTENT都統(tǒng)一用用此尺寸去劃劃分大小；為表指定NEXT，PCTINCREASE時時，這些參參數只在創(chuàng)建建時有效，之之后的擴展無無效；12/30/202233存儲參數在9i/10g的變化LMT指定UNIFORM例子：：1.創(chuàng)建一表表空間，UNIFORMSIZE為3M：2.在上面表表空間上創(chuàng)建建一個表：CREATETABLESPACEASSMDATAFILE'D:\oracle\oradata\encore\ASSM01.DBF'SIZE50MAUTOEXTENDOFFEXTENTMANAGEMENTLOCALUNIFORMSIZE3MSEGMENTSPACEMANAGEMENTAUTO;CREATETABLEASSMTAB(COL1NUMBER(3))STORAGE(INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100)TABLESPACEASSM;12/30/202234存儲參數在9i/10g的變化LMT指定UNIFORM例子：：3.查看該表表的擴展信息息：根據INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100，，系統(tǒng)分配方方法為：INITIAL_EXTENT=(2)+(5)+(5+(5*100%))=17MNext、pctincrease參數創(chuàng)建時時是有效的，，但以后ORACLE使使用統(tǒng)一的尺尺寸（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_EXTENTPCT_INCREASE------------------------------------------------12/30/202235表數據存儲量量大小估計-方法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一般習慣:總是保守守的多12/30/202236表數數據據存存儲儲量量大大小小估估計計-方方法法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是是關關鍵鍵!12/30/202237表數數據據存存儲儲量量大大小小估估計計-方方法法3從存存在在的的表表中中分分析析以以得得到到統(tǒng)統(tǒng)計計信信息息：：Step1:ANALYZETABLEEMPESTIMATE[COMPUTE]STATISTICS;Step2:SELECT(NUM_ROWS*AVG_ROW_LEN)FROMUSER_TABLESWHERETABLE_NAME=‘‘EMP’’;12/30/202238表數據存存儲量大大小估計計-檢查查表增長長檢查段的的擴展次次數是否否接近MAXEXTENTSSelectsubstr(owner||‘.’||segment_name,1,25)““object”,MaxextentsFromsys.Dba_extentsWheremaxextents-extents<=5Orderbyextents;12/30/202239內容提要Oracle塊結結構與段對對象創(chuàng)建表結構構準備創(chuàng)建表結構構語法與參參數解釋創(chuàng)建帶有存存儲控制的的樣例表9i/10g存儲參參數與塊參參數的變化化表的修改及及其它處理理參考資料12/30/202240修改表結構構-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]12/30/202241修改改表表結結構構-AlterTable表結結構構的的修修改改問問題題：：不要要經經常常修修改改表表結結構構；；修改改表表結結構構帶帶來來許許多多問問題題-性性能能、、一一致致性性等等；；修改改表表結結構構要要分分析析對對其其它它表表的的影影響響。。目前前可可修修改改：：增加列（字段段）寬度或增增加新列；減少列（字段段）寬度（該該列必須無數數據）；增加一個約束束或去掉約束束；修改存儲分配配；記錄表已作過過BACKUP；刪除已存在的的列；重新定位和組組織表；將表標識為不不可用。12/30/202242修改表結構刪除表結構的的某個列：刪除某個列的的語法：ALTERTABLE...DROPCOLUMN[CASCADECONSTRAINTS];刪除某個列的的例子：刪除列后不能能恢復：SQL>ALTERTABLEempDROPCOLUMNcomm;表已更改。12/30/202243修改表結構標識表結構的的某個列不可可用：標識某個列不不可用的語法法：ALTERTABLE...SETUNUSEDCOLUMN標識某個列不不可用及刪除除的例子：標識某個列不不用后就不能能再恢復為可可用：SQL>Altertableemp2setUNUSEDCOLUMNcomm;表已更改。SQL>ALTERTABLEemp2DROPUNUSEDCOLUMNSCHECKPOINT20;表已更改。SQL>12/30/202244刪除表表結構構刪除表表結構構：就是從從Oracle系統(tǒng)統(tǒng)中刪刪除該該表的的數據據與結結構；；語法：：刪除表表結構構將影影響：：指向本本表的的外部部鍵；；表中的的索引引；本表的的觸發(fā)發(fā)器；；本表中中的分分區(qū)；；本表的的快照照；本表的的角色色和用用戶權權限；；加在本本表的的所有有限制制。DROPTABLE[user.]table_name[CASCADECONSTRAINTS]12/30/202245刪除表表結構構刪除表表結構構注意意：刪除某某個表表前要要查詢詢該是是否其其它表表存在在關系系；不要輕輕易帶帶CASCADE子子句：刪除表表結構構建議議做：：查詢某某個表表的子子表：：DROPTABLEDEPTCASCADECONSTRAINTS;表已刪刪除./*找出模模式下下的某某個表表的子子表*/SELECTtable_name““childtables”FROMuser_constraintsWHEREr_constraint_nameIN(SELECTconstraint_nameFROMuser_constraintsWHEREtable_name=UPPER(‘‘&parent_table’’)ANDconstraint_type=’P’);12/30/202246釋放放不不用用的的空空間間記錄錄有有最最高高水水位位““Highwatermark””可釋釋放放不不使使用用的的擴擴展展FreespaceafterdeleteUnusedblockHighwatermarkBeforedeallocationUsedblockALTERTABLEemployeesDEALLOCATEUNUSED;Afterdeallocation12/30/20224710g段段收縮-表的存存儲空