01-0 Oracle 存儲與關(guān)鍵表設(shè)計

OracleRDBMS

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

integer最大分配次數(shù)2/11/202020Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計創(chuàng)建表結(jié)結(jié)構(gòu)語法法-子句句解釋Storage子句說明INITIALinteger初始大小NEXTinteger下次擴(kuò)展大小；在9i/10g創(chuàng)建本地表空間，NEXT由系統(tǒng)自動分配MINEXTENTSinteger最小分配次數(shù)，最小默認(rèn)是2MAXEXTENTSinteger最大分配次數(shù)PCTINCREASEinteger增長百分比（>=0），默認(rèn)50UNLIMITEDMINEXTENTS參數(shù)，用于限制空間的增長2/11/202021Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計創(chuàng)建表結(jié)結(jié)構(gòu)語法法-子句句解釋子句說明DEFAULT當(dāng)前列的默認(rèn)值（常數(shù)）INITRANS數(shù)據(jù)塊中用于更新（UPDATE）的空間百分比（1-99），0表示在插入時完全填滿數(shù)據(jù)塊，默認(rèn)為10MAXTRANS每個數(shù)據(jù)塊分配的事務(wù)如口數(shù)(1-255)，默認(rèn)1PCTFREE數(shù)據(jù)塊的用于更新的百分比(1-99)，0表示在插入是填滿整個數(shù)據(jù)庫塊PCTUSED數(shù)據(jù)塊保留的可用空間的最小百分比。取值1-99，默認(rèn)為40。實際上PCTUSED是控制自由空間的可用性。2/11/202022Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計創(chuàng)建表結(jié)結(jié)構(gòu)語法法-塊變變化建議采用用命令方方式：認(rèn)真研究究Oracle的CREATETABLE子句；關(guān)鍵的項項不要采采用默認(rèn)認(rèn)值，語語法如下下：2/11/202023Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計內(nèi)容提要要Oracle塊塊結(jié)構(gòu)構(gòu)與段對對象創(chuàng)建表結(jié)結(jié)構(gòu)準(zhǔn)備備創(chuàng)建表結(jié)結(jié)構(gòu)語法法與參數(shù)數(shù)解釋創(chuàng)建帶有有存儲控控制的樣樣例表9i/10g存存儲參數(shù)數(shù)與塊參參數(shù)的變變化表的修改改及其它它處理參考資料料2/11/202024Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計創(chuàng)建表結(jié)結(jié)構(gòu)樣例例1-塊塊控制參參數(shù)createtableuser_dflow(user_namevarchar2(30),,/**用戶名名**/bj_datedate,//*日期及時時間**/.....day__fee_innumber((16,,6),,/**日入流量量費用**/day__fee_outnumber((16,,6)//**日出流量量費用**/)TABLESPACEusersPCTFREE5PCTUSED80/創(chuàng)建網(wǎng)絡(luò)絡(luò)計費數(shù)據(jù)流量量明細(xì)表表user_dflow：根據(jù)上網(wǎng)網(wǎng)記錄的的信息很少修改改特點，設(shè)設(shè)置PCTFREE為為5；；而希望望盡快釋釋放刪除除的塊空空間而將將PCTUSED設(shè)85；；該表一開開始先分分配100MB，用完完再分配配50MB；2/11/202025Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計創(chuàng)建表結(jié)結(jié)構(gòu)樣例例2-存存儲分配配參數(shù)createtableuser_dflow(user_namevarchar2(30),,/**用戶名名**/bj_datedate,//*日期及時時間**/.....day__fee_innumber((16,,6),,/**日入流量量費用**/day__fee_outnumber((16,,6)//**日出流量量費用**/)TABLESPACEusersPCTFREE5PCTUSED80STORAGE(INITIAL120MNEXT60MMINEXTENTS1PCTINCREASE0MAXEXTENTS9999)創(chuàng)建網(wǎng)絡(luò)絡(luò)計費數(shù)據(jù)流量量明細(xì)表表user_dflow：對user_dflow表的的年數(shù)據(jù)據(jù)量進(jìn)行行估計：：假設(shè)年年數(shù)據(jù)量量100MB，，則可分分配120MB；當(dāng)分配120MB用完完后，由由再分配配60MB,……2/11/202026Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計內(nèi)容提要要Oracle塊塊結(jié)構(gòu)構(gòu)與段對對象創(chuàng)建表結(jié)結(jié)構(gòu)準(zhǔn)備備創(chuàng)建表結(jié)結(jié)構(gòu)語法法與參數(shù)數(shù)解釋創(chuàng)建帶有有存儲控控制的樣樣例表9i/10g存存儲參數(shù)數(shù)與塊參參數(shù)的變變化表的修改改及其它它處理參考資料料2/11/202027Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化為什么9i/10g取取消段的的空間擴(kuò)擴(kuò)展設(shè)置置：表數(shù)據(jù)的的增長與與空間的的不斷減減少是矛矛盾的關(guān)關(guān)鍵；NEXT表示示必須分分配一組組連序的的Oracle塊；如果表空空間無NEXT所要求求的連續(xù)續(xù)塊，則則分配失失?。?i/10g為為表擴(kuò)擴(kuò)展空間間時不參參考NEXT，，而是根根據(jù)表的的增長與與實際表表空間剩剩余的空空間來確確定。存儲參數(shù)數(shù)設(shè)置：：字典類型型表空間間-可設(shè)設(shè)置STORAGE的的所有項項；本地管理理表空間間-僅STORAGE的INITIAL有有效；2/11/202028Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化塊控制參參數(shù)設(shè)置置：9i/10g本地管理理表空間間ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTAUTO，Oracle根據(jù)dba__tables.avg_row__len來調(diào)整整PCTUSED的大大小。ASSM(AUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL，Oracle根據(jù)你你的PCTUSED設(shè)設(shè)置來調(diào)調(diào)整PCTUSED的的大?。?；SYSTEM表表空間ASSM為MANUAL，，USERS表表空間ASSM為AUTO((見后面面腳本))；2/11/202029Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化創(chuàng)建表結(jié)結(jié)構(gòu)前：：根據(jù)表數(shù)數(shù)據(jù)變化化特點，，創(chuàng)建相相應(yīng)的表表空間；；表空間的的對應(yīng)數(shù)數(shù)據(jù)文件件大小要要合理；；在創(chuàng)建表表時指定定到創(chuàng)建建好的表表空間上上，并根根據(jù)表空空間是否否是字典/本本地來確定是是否指定定NEXT等參參數(shù)：Setlong9999SELECTdbms_metadata.get_ddl('TABLESPACE'','SYSTEM'')fromdual;;CREATETABLESPACE""SYSTEM"DATAFILE'E:\\ORACLE\PRODUCT\\10..2.0\ORADATA\\ORA10\\SYSTEM01..DBF'SIZE314572800AUTOEXTENDONNEXT10485760MAXSIZE32767MLOGGINGONLINEPERMANENTBLOCKSIZE8192EXTENTMANAGEMENTLOCALAUTOALLOCATESEGMENTSPACEMANAGEMENTMANUAL2/11/202030Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化ASSM下創(chuàng)建建表結(jié)構(gòu)構(gòu)：觀察下面面腳本運運行的結(jié)結(jié)果：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----好好觀察察上面的的結(jié)果--2/11/202031Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化LMT的的extent_management__clause：AUTOALLOCATE((默認(rèn)值值)區(qū)的的大小由由系統(tǒng)來來管理((忽略NEXT等參數(shù)數(shù))；塊小于16K則則分配64K；；大于16K，，則分配配1M；；然后再根根據(jù)表增增長情況況分配區(qū)區(qū)，大小小是64K,1M,8M…LMT為為UNIFORM情況況：UNIFORM規(guī)定每每次擴(kuò)展展都一樣樣的大小小；UNIFORM默認(rèn)為為5個Oracle塊塊；segment_management__clause：SEGMENTSPACEMANAGEMENTMANUAL//AUTO；如果SSM=AUTO，則Oracle忽忽略對像像的PCTUSED,,FREELIST和FREELISTGROUPS參數(shù)數(shù)(見前面樣樣例)；2/11/202032Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化LMT為為UNIFORM情況況：創(chuàng)建的對對象的EXTENT都都統(tǒng)一用用此尺寸寸去劃分分大小；；為表指定定NEXT，PCTINCREASE時時，這些些參數(shù)只只在創(chuàng)建建時有效效，之后后的擴(kuò)展展無效；；2/11/202033Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化LMT指指定UNIFORM例例子：：1.創(chuàng)建建一表空空間，UNIFORMSIZE為為3M：2.在上上面表空空間上創(chuàng)創(chuàng)建一個個表：CREATETABLESPACEASSMDATAFILE''D:\\oracle\oradata\encore\ASSM01.DBF''SIZE50MAUTOEXTENDOFFEXTENTMANAGEMENTLOCALUNIFORMSIZE3MSEGMENTSPACEMANAGEMENTAUTO;CREATETABLEASSMTAB((COL1NUMBER(3)))STORAGE(INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100)TABLESPACEASSM;2/11/202034Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計存儲參數(shù)數(shù)在9i/10g的變變化LMT指指定UNIFORM例例子：：3.查看看該表的的擴(kuò)展信信息：根據(jù)INITIAL2MNEXT5MMINEXTENTS3PCTINCREASE100，系系統(tǒng)分配配方法為為：INITIAL_EXTENT=((2)++(5))+((5+((5*100%%))==17MNext、pctincrease參數(shù)創(chuàng)創(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---------------------------------------------------------------------------------------------------------TABLEASSM03145728384TABLEASSABLEASSM23145728384TABLEASSM33145728384TABLEASSM43145728384TABLEASSM53145728384SQL>>selecttable_name,INITIAL__EXTENT,NEXT__EXTENT,PCT__INCREASEfromuser__tableswheretable_name=''ASSMTAB'';TABLE_NAMEINITIAL__EXTENTNEXT__EXTENTPCT_INCREASE----------------------------------------------------------------ASSMTAB317825792314572802/11/202035Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計表數(shù)據(jù)存存儲量大大小估計計-方法法1從DDL中了解解數(shù)據(jù)與與行大小小，如：：Tableemp((namevarchar2((30)),salarynumber(8,2)),join_datedate);;Step1:每行行大小-->20B++6B++7B=43BStep2:估計計總行數(shù)數(shù)->>10000(rows))Step3:表的的小-->43(B)**10000(rows))=0.5MBStep4:每年年增長率率->>43*2000(rows)==100KB一般習(xí)慣慣:總總是保保守的多多2/11/202036Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計表數(shù)據(jù)存存儲量大大小估計計-方法法2從存在的的數(shù)據(jù)得得到平均均行大小小Step1:Initial40,Next50,Extents4,PctIncrease50Step2:Size::(90++50*1..5++50*1..5*1.5))=287..5Step3:MaxExtents((高版本本)不不要設(shè)置置低的值值：S=項項目總總數(shù)==[NEXT(RN-1)//(R--1)]]這里R==(100++PctIncrease))/100;N=((MAXEXTENTS--1))=>>總大小==((S++INITIAL)一般習(xí)慣慣:要要合理理設(shè)置pctincrease是關(guān)關(guān)鍵!2/11/202037Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計表數(shù)據(jù)存存儲量大大小估計計-方法法3從存在的的表中分分析以得得到統(tǒng)計計信息：：Step1:ANALYZETABLEEMPESTIMATE[[COMPUTE]STATISTICS;Step2:SELECT(NUM__ROWS*AVG__ROW_LEN)FROMUSER_TABLESWHERETABLE_NAME=‘‘EMP’;;2/11/202038Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計表數(shù)據(jù)存存儲量大大小估計計-檢查查表增長長檢查段的的擴(kuò)展次次數(shù)是否否接近MAXEXTENTSSelectsubstr(owner|||‘‘.’’||segment_name,1,,25))“object””,MaxextentsFromsys.Dba_extentsWheremaxextents-extents<<=5Orderbyextents;2/11/202039Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計內(nèi)容提要要Oracle塊塊結(jié)構(gòu)構(gòu)與段對對象創(chuàng)建表結(jié)結(jié)構(gòu)準(zhǔn)備備創(chuàng)建表結(jié)結(jié)構(gòu)語法法與參數(shù)數(shù)解釋創(chuàng)建帶有有存儲控控制的樣樣例表9i/10g存存儲參數(shù)數(shù)與塊參參數(shù)的變變化表的修改改及其它它處理參考資料料2/11/202040Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計修改表結(jié)結(jié)構(gòu)-AlterTable表結(jié)構(gòu)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]2/11/202041Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計修改表結(jié)結(jié)構(gòu)-AlterTable表結(jié)構(gòu)的的修改問問題：不要經(jīng)常常修改表表結(jié)構(gòu)；；修改表結(jié)結(jié)構(gòu)帶來來許多問問題-性性能、一一致性等等；修改表結(jié)結(jié)構(gòu)要分分析對其其它表的的影響。。目前可修修改：增加列（（字段））寬度或或增加新新列；減少列（（字段））寬度（（該列必必須無數(shù)數(shù)據(jù)）；；增加一個個約束或或去掉約約束；修改存儲儲分配；；記錄表已已作過BACKUP；；刪除已存存在的列列；重新定位位和組織織表；將表標(biāo)識識為不可可用。。2/11/202042Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計修改表結(jié)結(jié)構(gòu)刪除表結(jié)結(jié)構(gòu)的某某個列：：刪除某個個列的語語法：ALTERTABLE.....DROPCOLUMN[[CASCADECONSTRAINTS]];刪除某個個列的例例子：刪除列后后不能恢恢復(fù)：SQL>>ALTERTABLEempDROPCOLUMNcomm;表已更改改。2/11/202043Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計修改表結(jié)結(jié)構(gòu)標(biāo)識表結(jié)結(jié)構(gòu)的某某個列不不可用：：標(biāo)識某個個列不可可用的語語法：ALTERTABLE.....SETUNUSEDCOLUMN標(biāo)識某個個列不可可用及刪刪除的例例子：標(biāo)識某個個列不用用后就不不能再恢恢復(fù)為可可用：SQL>>Altertableemp2setUNUSEDCOLUMNcomm;表已更改改。SQL>>ALTERTABLEemp2DROPUNUSEDCOLUMNSCHECKPOINT20;表已更改改。SQL>>2/11/202044Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計刪除表結(jié)結(jié)構(gòu)刪除表結(jié)結(jié)構(gòu)：就是從Oracle系系統(tǒng)中刪刪除該表表的數(shù)據(jù)據(jù)與結(jié)構(gòu)構(gòu)；語法：刪除表結(jié)結(jié)構(gòu)將影影響：指向本表表的外部部鍵；表中的索索引；本表的觸觸發(fā)器；；本表中的的分區(qū)；；本表的快快照；本表的角角色和用用戶權(quán)限限；加在本表表的所有有限制。。DROPTABLE[user.]table_name[CASCADECONSTRAINTS]]2/11/202045Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計刪除表結(jié)結(jié)構(gòu)刪除表結(jié)結(jié)構(gòu)注意意：刪除某個個表前要要查詢該該是否其其它表存存在關(guān)系系；不要輕易易帶CASCADE子子句：刪除表結(jié)結(jié)構(gòu)建議議做：查詢某個個表的子子表：DROPTABLEDEPTCASCADECONSTRAINTS;;表已刪除除./*找出模式式下的某某個表的的子表*/SELECTtable_name““childtables””FROMuser_constraintsWHEREr_constraint__nameIN(SELECTconstraint_nameFROMuser_constraintsWHEREtable__name==UPPER(‘&&parent_table’)ANDconstraint_type==’P’));2/11/202046Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計釋放不用用的空間間記錄有最最高水位位“Highwatermark””可釋放不不使用的的擴(kuò)展FreespaceafterdeleteUnusedblockHighwatermarkBeforedeallocationUsedblockALTERTABLEemployeesDEALLOCATEUNUSED;Afterdeallocation2/11/202047Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計10g段段收縮--表的存存儲空間間收縮2/11/202048Oracle數(shù)數(shù)據(jù)庫庫優(yōu)化設(shè)設(shè)計10g段段收縮--表的存存儲空間間收縮Oracle9i提提供: