第四章 ORACLE表管理

第四章

ORACLE數(shù)據(jù)庫對象表４.1ORACLE表和數(shù)據(jù)類型第四講Oracle數(shù)據(jù)表

４.2DUAL表４.3常規(guī)表(堆表)(NormalTable)４.5索引組織表(IndexOrganizedTableIOT)４.6對象表(ObjectTable)４.7嵌套表(NestedTable)４.8其他表４.4分區(qū)表(PartitionsTable)

重點掌握各種類型表的創(chuàng)建方法了解常規(guī)表、分區(qū)表、索引組織表和對象表的物理組織形式及其特點了解各種表的使用場合

４.1ORACLE表和數(shù)據(jù)類型表是數(shù)據(jù)庫數(shù)據(jù)存儲的基本單元,它對應(yīng)于顯示世界中的對象(部門和雇員等)。當進行數(shù)據(jù)庫設(shè)計時,需要構(gòu)造E-R圖,在將E-R圖轉(zhuǎn)變?yōu)閿?shù)據(jù)庫對象時,實體最終要轉(zhuǎn)換為數(shù)據(jù)庫表。４.1.1ORACLE表簡介BOSTONOPERATIONS40CHICAGOSALES30DALLASRESEARCH20NEWYORKACCOUNTING10LOCDNAMEDEPTNO表中存儲的數(shù)據(jù)的邏輯結(jié)構(gòu)是一張由行列組成的二維表。表中的一行又叫一條記錄，或一個元組。一條記錄描述一個實體;一列描述實體的屬性，如部門有部門代碼、部門名稱、位置等屬性，每個列還具有列名、數(shù)據(jù)類型、長度、約束條件、默認值等等。

ROWID是表的偽列，ROWID給出了表行的物理位置,用來唯一的標識表行。是定位表行最快的方式，它與其他列一樣可以直接查詢。ROWID在數(shù)據(jù)文件中其數(shù)據(jù)是掩碼格式存放，所以一般用戶不能直接讀懂它的內(nèi)容。而必須用包DBMS_ROWID進行轉(zhuǎn)換。

４.1.2ORACLE數(shù)據(jù)行的物理標識rowid４.1ORACLE表和數(shù)據(jù)類型selectdeptno,dname,rowidfromscott.dept;selectdeptno,dname,rowid,dbms_rowid.rowid_relative_fno(rowid)file#,dbms_rowid.rowid_block_number(rowid)block#,dbms_rowid.rowid_row_number(rowid)row#fromscott.dept;為DEPT表的DEPTNO建立索引,分析數(shù)據(jù)庫是如何存儲索引信息的?如何利用索引來加快查找速度的?用戶表空間DEPTROWIDDEPTNO其他列rowid110CHICAGOG1rowid230NEWYORKG1rowid340DALLASG2rowid420BOSTONG2形成索引列表rowid340rowid230rowid420rowid110INDEX_DEPTNO索引表空間Select*fromdeptWheredeptno=20

最大長度4000chars根據(jù)字符集而定的可變長度字符串(以字符為單位)VARCHAR2(nchar)

最大長度2000chars根據(jù)字符集而定的固定長度字符串(以字符為單位)CHAR(nchar)可做索引的最大長度749最大長度4000bytes可變長度的字符串(以字節(jié)為單位)VARCHAR2(n)

最大長度2000bytes固定長度字符串(以字節(jié)為單位)CHAR(n)其它說明

限制條件中文說明字段類型字符型４.1.3ORACLE基本數(shù)據(jù)類型(PAGE40)

４.1ORACLE表和數(shù)據(jù)類型

注解：因為char類型總是會用空格填充使之達到一個固定寬度，無論在是表段中還是索引段中，它都會占用很大的存儲空間。NUMBER(63)，精度更高實數(shù)類型REALNUMBER(38)，雙精度浮點數(shù)類型FLOAT小的整數(shù)NUMBER(38)整數(shù)類型INTEGER/INTP為整數(shù)位，S為小數(shù)位數(shù)字類型DECIMAL(P,S)/NUMERIC(P,S)精度13位；范圍-10308.25--10308.25在內(nèi)存中占9個字節(jié)雙精度浮點數(shù)類型BINARY_DOUBLE精度6位；范圍-1038.53--1038.53;在內(nèi)存中占5個字節(jié)單精度浮點數(shù)類型BINARY_FLOATP為總位數(shù)，S為小數(shù)位；精度P<=38；長度0~22字節(jié)，范圍10e-130~10e126數(shù)字類型NUMBER(P,S)

數(shù)值型４.1.3ORACLE基本數(shù)據(jù)類型

為數(shù)值型分配存儲空間的公式:

Round((length(number)+s)/2)+1

當number>=0s=0;當number<0s=1４.1ORACLE表和數(shù)據(jù)類型

注解：number可以很精確的存儲數(shù)值;如果對科學數(shù)據(jù)執(zhí)行數(shù)據(jù)挖掘或進行復雜的數(shù)值分析，精度損失往往是可以接受的，使用binary_float、binary_double可能會得到非常顯著的性能提升。字段類型中文說明限制條件DATE(1)固定日期/時間格式;(2)7字節(jié)寬度描述世紀、年、月、日、時、分、秒7個屬性;(3)使用DUMP函數(shù)，可查看實際存儲。默認格式DD-MM-YY（HH-MI-SS），可以通過修改nls_language和nls_date_format兩個參數(shù)來更改日期的顯示。TIMESTAMP（n）(1)與DATE類似;存儲方式與DATE相同;(2)支持小數(shù)秒N的取值為0~9INTERVAL時間量４.1ORACLE表和數(shù)據(jù)類型日期時間型４.1.3ORACLE基本數(shù)據(jù)類型

注解:date類型世紀和年份采用一種“加100”表示法來存儲。月和日采用自然的存儲方式。時、分、秒采用“加1”表示法存儲。４.1ORACLE表和數(shù)據(jù)類型其他數(shù)據(jù)型４.1.3ORACLE基本數(shù)據(jù)類型

最大長度4000bytes二進制數(shù)據(jù)表中記錄的唯一行號NROWID10bytes********.****.****格式，*為0或1數(shù)據(jù)表中記錄的唯一行號ROWID最大長度4G存放在數(shù)據(jù)庫外的二進制數(shù)據(jù)BFILE最大長度4G根據(jù)字符集而定的字符數(shù)據(jù)NCLOB最大長度4G字符數(shù)據(jù)CLOB最大長度4G二進制數(shù)據(jù)BLOB最大長度2G可變長度的二進制數(shù)據(jù)LONGRAW最大長度2000bytes固定長度的二進制數(shù)據(jù)RAW最大長度2G（231-1）超長字符串LONG４.2DUAL表

DUAL表屬于SYS模式,它在數(shù)據(jù)字典創(chuàng)建時自動建立。DUAL表只有一列和一行，列名為dummy。由于ORACLE中大部分操作都必須在表中，如對一個代數(shù)表達式求值，可以在該表上使用SELECT命令計算一個常量表達式。

DUAL表在此充當了那些表達式的包羅萬象的容器。SQLServerClientSomeQueryResultsdummydual４.3常規(guī)表(NormalTable)創(chuàng)建常規(guī)表的方法和過程3創(chuàng)建常規(guī)表前準備工作2常規(guī)表及其特征1DDLTABLESPACE數(shù)據(jù)定義命令usersTABLE1表結(jié)構(gòu)的查看與維護4常規(guī)表及其特征1４.3常規(guī)表(NormalTable)常規(guī)表又叫叫做堆組織表（（heap）,它是一個無序行集合合，并不是以以某種特定定的順序來來存儲數(shù)據(jù)據(jù)。堆是計算機機領(lǐng)域中深深入研究的的一種經(jīng)典典的數(shù)據(jù)結(jié)結(jié)構(gòu)，它的的特點是會會將數(shù)據(jù)放放在最合適適的地方，，而不是按按照某種特特定順序來來放置。Example:建立一個測測試表t，數(shù)據(jù)庫中中的每個數(shù)數(shù)據(jù)塊剛好好能放一個整整行（數(shù)據(jù)據(jù)庫塊是8KB),向表中插入入多條記錄錄,驗證記錄存存儲的無序序性。根據(jù)據(jù)數(shù)數(shù)據(jù)據(jù)需需求求分分析析設(shè)設(shè)計計數(shù)數(shù)據(jù)據(jù)庫庫表表的的模模式式,應(yīng)該該使使用用范范式式來來規(guī)規(guī)劃劃每每個個表表。。定義義表表列列時時，，應(yīng)應(yīng)選選擇擇合合適適的的數(shù)數(shù)據(jù)據(jù)類類型型和和長長度度。。確定定表表中中所所需需要要的的完完整整性性約約束束。。規(guī)劃劃表表的的位位置置，，將將表表部部署署在在特特定定的的表表空空間間上上(USERS表空空間間)。預計計和和規(guī)規(guī)劃劃表表的的大大小小。。在在oracle10G中，，通通過過OEMDatabasecontrol估算算表表大大小小。。用戶戶與與權(quán)權(quán)限限問問題題。。(CREATEANYTABLE、CREATETABLE、UNLIMITEDTABLESPACE)４.3常規(guī)規(guī)表表(NormalTable)創(chuàng)建常規(guī)表前準備工作2４.3常規(guī)規(guī)表表(NormalTable)report_card學生生成成績績管管理理數(shù)數(shù)據(jù)據(jù)庫庫deptcoursestudent實例例：：學學生生成成績績管管理理項項目目數(shù)數(shù)據(jù)據(jù)庫庫設(shè)設(shè)計計dept字段名注釋類型長度備注dept_no部門編號varchar28PKdept_name部門名稱varchar240dept_mng部門經(jīng)理varchar28address地址varchar280cont部門人數(shù)numberstudent字段名注釋類型長度備注student_no學號varchar212PKstudent_name姓名varchar210dept_no部門編號varchar28FKsex性別varchar21CKtype學生類型varchar21CKhiredate入學日期dateprovince籍貫varchar220empid身份證號varchar225address詳細地址varchar260borndate出生日期datespeciality所學專業(yè)varchar220nation民族varchar220htelno電話Varchar220money繳費總計Number(10,2)remarks備注Varchar280course字段名注釋類型長度備注course_no課程號varchar28PKcourse_name課程名稱varchar240grade開設(shè)年級varchar28period學時數(shù)numberreport_card字段名注釋類型長度備注student_no學號varchar212FKcourse_no課程號varchar28FKterm學期varchar28result成績number（5，2）remark備注varchar280４.3常規(guī)規(guī)表表(NormalTable)創(chuàng)建常規(guī)表的方法和過程3使用CreateTable腳本在SQLPlus環(huán)境中實現(xiàn)。使用OEM控制臺通過圖形界面面創(chuàng)建表結(jié)構(gòu)構(gòu)。在原來已有表的的基礎(chǔ)上建立新表。４.3常規(guī)表(NormalTable)使用CreateTable腳本創(chuàng)建常規(guī)表Createtableschema.table_name(列名1類型（長度））列約束,列名2類型（長度））列約束,……,表約束)PctfreenPctusednInitransnMaxtransnTablespacetablespace_nameStorage(InitialnNextnPctincreasenMinextentsnMaxextentsn)指定表的每一個數(shù)據(jù)塊為Update操作所保留的空間百分比,默認10指定在數(shù)據(jù)塊上可以重新插入數(shù)據(jù)的已用空間最低百分比。

指定可同時對表所有塊進行更新操作的最小和最大的事務(wù)數(shù)將所創(chuàng)建的表部署到規(guī)定的表空間中指定表第一個區(qū)的大小。指定初始擴展區(qū)的大小.指定擴展區(qū)遞增的百分比。NEXT=(1+n/100）*Old_next指定為表段分配的區(qū)數(shù)的最小值和最大值。Createtabletest(anumber)

storage(initial100Knext100Kminextents2maxextents100pctincrease100);４.3常規(guī)表(NormalTable)示例:以新用戶zhang登錄,在方案zhang下創(chuàng)建學生成成績管理項目四個個表。zhangCREATETABLEZHANG.dept(DEPT_NOvarchar2(8),DEPT_NAMEvarchar2(40),DEPT_MNGvarchar2(8),ADDRESSvarchar2(80),CONTnumber,PRIMARYKEY(DEPT_NO))pctfree20pctused50tablespaceusersstorage(initial100knext100kminextents2maxextents100pctincrease0);使用OEM創(chuàng)建常規(guī)表４.3常規(guī)表(NormalTable)示例:創(chuàng)建學生基本本信息表student,包含student_no等15個字段。其中中student_no為主鍵；student_name非空；dept_no參照與表dept中的dept_no的取值;type取值僅為1、2、3；sex取值只能為0或1。該表的表空間間為users，保留用于更更新的空閑空空間的百分比比為10%，并發(fā)事務(wù)個數(shù)初初始值為2個，最多并發(fā)發(fā)255個事務(wù)。為表分配的區(qū)區(qū)最少為2個，最多為20個，第一個區(qū)區(qū)大小為64K，第一個擴展區(qū)的大小小為64K，隨著數(shù)據(jù)量量的增加新分分配的區(qū)相對對于上一個區(qū)區(qū)增長50%.通過Createtable…...asselect……….語句(Createtable嵌套子查詢,簡稱CTAS)，可以基于原原有的表或視視圖來創(chuàng)建新新表，而不必必逐個定義列列。當新表與與原表結(jié)構(gòu)相相似或者新表表需要使用原原表中大量數(shù)數(shù)據(jù)時很有必必要采用這種種方式。４.3常規(guī)表(NormalTable)CTAS方法建立常規(guī)表語法一:createtable<table_name>[NOLOGGING]asselect<selected_column_name>fromtable_old[wherecondition];語法二:createtable<table_name>[NOLOGGING]asselect<selected_column_name>fromtable_oldwhere1=2;建議使用NOLOGGING選項。如果不不使用這個選選項，則每插插入一條記錄錄都將會產(chǎn)生生重做日志信信息，占用了了空間和時間間。注意：不能修改列的的數(shù)據(jù)類型和和長度，新表表中的數(shù)據(jù)類類型和長度都都必須和查詢詢中的一致。。SELECT語句中不能包包含大對象數(shù)數(shù)據(jù)類型和LONG數(shù)據(jù)類型。約束性條件和和列的默認值值定義都不會會被復制。建議使用NOLOGGING選項。如果不不使用這個選選項，則每插插入一條記錄錄都將會產(chǎn)生生重做日志信信息，占用了了空間和時間間。４.3常規(guī)表(NormalTable)CTAS方法建立常規(guī)表４.3常規(guī)表(NormalTable)表結(jié)構(gòu)的查看與維護4查看表結(jié)構(gòu)修改表結(jié)構(gòu)刪除表結(jié)構(gòu)可通過數(shù)據(jù)字字典dba_tables、all_tables、user_tables查看表的定義義信息如：SELECTowner,tablespace_name,pct_free,pct_used,initial_extent,next_extent,min_extents,max_extentsFROMdba_tablesWHEREtable_name=’STUDENT’;可通過查詢數(shù)據(jù)字典典dba_tab_columns、dba_constraints查看表的字段段信息與約束束信息。通過SQL命令修改表的的語法格式如如下所示：ALTERTABLE[schema.]table_nameRENAMETONewTablenameADD[col_namedatatype][DEFAULTexpression][col_constraint]MODIFY[col_namedatatype][DEFAULTexpression][col_constraint]DROPCOLUMNcol_name(col_name1,col_name2)SETUNUSEDCOLUMNcol_nameDROPUNUSEDCOLUMNＳSTORAGE子句;查看表結(jié)構(gòu)修改表結(jié)構(gòu)刪除表結(jié)構(gòu)使用renameto子句修改表名名使用ADD、MODIFY和DROP子句來修改列列定義使用STORAGE子句來修改存存儲參數(shù)及區(qū)區(qū)分配參數(shù)刪除表的結(jié)構(gòu)構(gòu):Droptableschema.tablename[cascadeconstraints][purge]備注:不僅會刪除了了表中的數(shù)據(jù)據(jù),還會刪除表結(jié)結(jié)構(gòu)。這條命命令是不能回回退的，所以以，刪除表的的時候應(yīng)格外外小心?；謴捅粍h除表表：FLASHBACKTABLEschema.tablenameTOBEFOREDROP各類分區(qū)表的創(chuàng)建和使用3分區(qū)表的定義和類型2引入分區(qū)表的目的14.4分區(qū)表表(PartitionsTable)分區(qū)表的維護4訪問一一季度度數(shù)據(jù)據(jù)

一季度數(shù)據(jù)二季度數(shù)據(jù)三季度數(shù)據(jù)四季度數(shù)據(jù)P1分區(qū)段P2分區(qū)段P3分區(qū)段P4分區(qū)段10G4.4分區(qū)表表(PartitionsTable)2003年Sales表段假設(shè)一一張銷銷售表表SALES年數(shù)據(jù)據(jù)總量量達到到10G，每個個季度度平均均2.5G?，F(xiàn)在在執(zhí)行一條條SQL語句訪訪問SALES表中的的第一一季度度銷售售總額額。這這時候候服務(wù)務(wù)器進進程會會對SALES表進行行全表表掃描描。如如果我我們使使用分分區(qū)表表把四四個季季度的的數(shù)據(jù)據(jù)分別別存放放到不同的的分區(qū)區(qū)中，，當統(tǒng)統(tǒng)計某某一季季度銷銷售數(shù)數(shù)據(jù)只只需要要掃描描2.5G數(shù)據(jù)，，提高高I/O性能。。引入分區(qū)表的目的1引入分區(qū)表的目的12.5Goracle允許用用戶將將一個個表分分成多多個分分區(qū)用戶可可以只只訪問問表中中的特特定分分區(qū)，，避免免全表表掃描描，降降低磁磁盤I/O,提高訪訪問性性能將不同同的分分區(qū)存存儲在在不同同的磁磁盤，，均衡衡分布布I/O，提高高訪問問性能能可以獨獨立地地備份份和恢恢復每每個分分區(qū)，，提高高系統(tǒng)統(tǒng)健壯壯性、、可靠靠性及及可用用性4.4分區(qū)表表(PartitionsTable)分區(qū)是是一種種方法法，它它將一一個大大表從從邏輯輯上根根據(jù)某某些條條件把數(shù)數(shù)據(jù)分分成若若干個個較小小的且且更容容易管管理的的區(qū)分區(qū)段段(表)。分區(qū)表表是一一種特特殊的的常規(guī)規(guī)表。。按照分分區(qū)的的方式式可將將分區(qū)區(qū)表分分為：：范圍分分區(qū)（（RangePartitioning）散列分分區(qū)（（HashPartitioning）列表分分區(qū)（（ListPartitioning）復合分分區(qū)(CompositePartitioning)--復合范范圍-散列分分區(qū)(CompositeRange-HashPartitioning)--復合范范圍-列表分分區(qū)(CompositeRange-ListPartitioning)分區(qū)表的定義和類型2分區(qū)表的定義和類型2各類分區(qū)表的創(chuàng)建和使用3各類分區(qū)表的創(chuàng)建和使用34.4分區(qū)表表(PartitionsTable)范圍分分區(qū)（RangePartitioning）根據(jù)表表中某某一列列值的的范圍圍(通常是基基于時時間的的范圍圍)，將表表中的的行映射到各各個分區(qū)。。適用場合:當數(shù)據(jù)量較較大且跨時時間范圍均勻分布數(shù)數(shù)據(jù)時，非非常適合于于創(chuàng)建范圍分區(qū)區(qū)表，這時時的表性能能是最佳的。范圍分區(qū)示示例：假設(shè)一張銷銷售表SALES（customer_id銷售單號、、sales_amount銷售額、sales_date銷售日期））年數(shù)據(jù)總總量達到10G，每個季度度平均2.5G。創(chuàng)建范圍圍分區(qū)表，，將一、二二、三、四季度的銷銷售數(shù)據(jù)存存放到不同同分區(qū)段（（p1,p2,p3,p4）中。操作：1）范圍分區(qū)區(qū)表的創(chuàng)建建：OEM實現(xiàn)、SQL腳本實現(xiàn)。。2）查看范圍圍分區(qū)表的的分區(qū)情況況3）使用范圍圍分區(qū)表范圍分區(qū)（RangePartitioning）SALES范圍分區(qū)表表的創(chuàng)建：：CREATETABLEsales(customer_idnumber(3),sales_amountnumber(10,2),sales_dateDATE)PARTITIONBYRANGE(sales_date)(PARTITIONp1VALUESLESSTHAN('01-APR-2003')TABLESPACEusers,PARTITIONp2VALUESLESSTHAN('01-JUL-2003')TABLESPACEusers,PARTITIONp3VALUESLESSTHAN('01-OCT-2003')TABLESPACEusers,PARTITIONp4VALUESLESSTHAN('01-JAN-2004')TABLESPACEusers);步驟一：：創(chuàng)建分分區(qū)表SALES（1）OEM方式創(chuàng)建建（2）命令方方式創(chuàng)建建范圍分區(qū)區(qū)的語法法：PARTITIONBYRANGE(column_name)(PARTITIONpart1VALUELESSTHAN(range1)TABLESPACEtablespace_name,PARTITIONpart2VALUELESSTHAN(range2),TABLESPACEtablespace_name,...[PARTITIONpartNVALUELESSTHAN(MAXVALUE)]);根據(jù)該列列創(chuàng)建分分區(qū)分區(qū)的名名稱該分區(qū)包包含低于于range1值的數(shù)數(shù)據(jù)信息息，并將將該分區(qū)區(qū)部署到到相應(yīng)的的表空間間范圍分區(qū)區(qū)（RangePartitioning）范圍分區(qū)區(qū)表SALES的創(chuàng)建：：createtablezhang.sales(customer_idnumber(3),sales_amountnumber(10,2),sales_dateDATE)PARTITIONBYRANGE(sales_date)(PARTITIONp1VALUESLESSTHAN('01-APR-2003'),PARTITIONp2VALUESLESSTHAN('01-JUL-2003'),PARTITIONp3VALUESLESSTHAN('01-OCT-2003'),PARTITIONp4VALUESLESSTHAN('01-JAN-2004'));SQL腳本創(chuàng)建建sales表：步驟二：：查看和和使用分分區(qū)表SALES（1）通過OEM或者查詢詢數(shù)據(jù)字字典視圖圖user_segments,查看分區(qū)段信息息。SQL>COLsegment_nameformata10;SQL>SELECTtablespace_name,segment_name,partition_nameFROMuser_segmentsWHEREsegment_name='SALES';（2）當在分分區(qū)表上上執(zhí)行DML操作時，，ORACLE會根據(jù)分分區(qū)列值值的范圍在相相應(yīng)的分分區(qū)上執(zhí)執(zhí)行操作作(T10.SQL)SQL>insertintosalesvalues(1,28500,’25-JAN-2003’);SQL>select*fromsales全表掃描描wheresales_date=to_date(’25-JAN-2003’,’’dd-mon-yyyy’’);SQL>select*fromsalespartition(p1)分區(qū)掃描描wheresales_date=to_date(’25-JAN-2003’,’’dd-mon-yyyy’’);范圍分區(qū)區(qū)（RangePartitioning）各類分區(qū)表的創(chuàng)建和使用3各類分區(qū)表的創(chuàng)建和使用3散列(哈希)分區(qū)（HashPartitioning）散列分區(qū)區(qū)是指按按照ORACLE所提供的散列列（HASH）函數(shù),計算列值值數(shù)據(jù)，并最最終按照照函數(shù)結(jié)結(jié)果將數(shù)數(shù)據(jù)均勻的部署在在不同的的分區(qū)中中。適用場合合：不符合時時間范圍圍分區(qū)且且需要均均勻分布數(shù)據(jù)據(jù)的場合合。4.4分區(qū)表(PartitionsTable)散列分區(qū)區(qū)示例：：創(chuàng)建一張張產(chǎn)品編編碼表PRODUCT(product_id產(chǎn)品編號號,description產(chǎn)品名稱稱)，將產(chǎn)品品編碼的的信息均均勻的部部署在兩兩個不同的邏輯輯分區(qū)上上，插入入示例數(shù)數(shù)據(jù)進行行驗證。。操作：1）散列分區(qū)表表的創(chuàng)建：OEM實現(xiàn)、SQL腳本實現(xiàn)。2）查看散列分分區(qū)表的分區(qū)區(qū)情況3）使用散列分分區(qū)表散列(哈希)分區(qū)（HashPartitioning）創(chuàng)建散列分區(qū)區(qū)表product（1）OEM方式創(chuàng)建PARTITIONBYHASH(column_name)(PARTITIONpart1[TABLESPACEtbs1],PARTITIONpart2[TABLESPACEtbs2],...PARTITIONpartN[TABLESPACEtbsN]);散列(哈希)分區(qū)（HashPartitioning）（2）命令方式創(chuàng)建根據(jù)該列創(chuàng)建建分區(qū)分區(qū)的名稱將各個分區(qū)部部署到指定的的表空間散列分區(qū)表product的創(chuàng)建：duct(product_idnumber(6),descriptionvarchar2(30))PARTITIONBYHASH(product_id)(PARTITIONp1tablespaceusers,PARTITIONp2tablespaceusers);（1）通過OEM或者查詢數(shù)據(jù)據(jù)字典視圖user_segments,查看分區(qū)段信息。SQL>COLsegment_nameformata10;SQL>SELECTtablespace_name,segment_name,partition_nameFROMuser_segmentsWHEREsegment_name=‘‘PROCUDT';（2）插入示例數(shù)數(shù)據(jù)T11.sql，觀察數(shù)據(jù)的的分布。散列(哈希)分區(qū)（HashPartitioning）查看和使用散散列分區(qū)表product各類分區(qū)表的創(chuàng)建和使用3各類分區(qū)表的創(chuàng)建和使用3列表分區(qū)（listPartitioning）列表分區(qū)是專專門用于數(shù)據(jù)據(jù)建模的離散值分布的的一種分區(qū)方方式，它可以以將離散散數(shù)數(shù)據(jù)據(jù)（（如如城城市市、、地地域域））有效效的的部署署到到不不同同的的分分區(qū)區(qū)中中。。適用用場場合合：：大型型表表中中有有一一些些關(guān)關(guān)于于城城市市、、地地域域或類類似似的的屬屬性性值值且且大大部部分分應(yīng)應(yīng)用用基基于于不不同的的地地域域或或城城市市來來進進行行的的情情況況下下。。4.4分區(qū)區(qū)表表(PartitionsTable)列表表分分區(qū)區(qū)示示例例：：某公公司司（在在全全國國北北京京、、上上海海、、重重慶慶、、廣廣州州、、南南京京、、武武漢漢六六個地地區(qū)區(qū)均均有有銷銷售售點點））經(jīng)常常需需要要以以地地理理位位置置統(tǒng)統(tǒng)計計銷銷售售數(shù)數(shù)據(jù)據(jù)，，比比如如統(tǒng)計計上上海海的的銷銷售售數(shù)數(shù)據(jù)據(jù)，，統(tǒng)統(tǒng)計計北北京京的的銷銷售售數(shù)數(shù)據(jù)據(jù)等等等等。。由由于于數(shù)數(shù)據(jù)據(jù)量量大，，公公司司建建議議將將數(shù)數(shù)據(jù)據(jù)存存儲儲在在不不同同的的分分區(qū)區(qū)上上，，避避免免查查找找信信息息的的時時候掃掃描描全全表表增增加加開開銷銷。。根根據(jù)據(jù)以以上上要要求求為為該該公公司司建建立立銷銷售售數(shù)數(shù)據(jù)據(jù)表表SALES_BY_REGION(deptno部門門編編號號,dname部門門名名稱稱,quantity_sales銷售售數(shù)數(shù)量量,city部門門所所在在城城市市））。。列表表分分區(qū)區(qū)（（ListPartitioning）創(chuàng)建建散散列列分分區(qū)區(qū)表表SALES_BY_REGION（1）OEM方式式創(chuàng)創(chuàng)建建列表表分分區(qū)區(qū)（（ListPartitioning）（2）命令令方式式創(chuàng)創(chuàng)建建PARTITIONBYLIST(column_name)(PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),...PARTITIONpartNVALUES(DEFAULT));根據(jù)據(jù)該該列列(離散散值值如如地地理理位位置置)創(chuàng)建建的的列列表表分分區(qū)區(qū)分區(qū)區(qū)的的名名稱稱包含含values_list1值的的記記錄錄列表表分分區(qū)區(qū)sales_by_region的創(chuàng)創(chuàng)建建：：createtablesales_by_region(deptnonumber,dnamevarchar2(20),quantity_salesnumber(10,2),cityvarchar2(10))PARTITIONBYLIST(city)(PARTITIONp1VALUES('北京京','上海海'),PARTITIONp2VALUES('重慶慶','廣州州'),PARTITIONp3VALUES('南京京','武漢漢'));各類分區(qū)表的創(chuàng)建和使用3各類分區(qū)表的創(chuàng)建和使用3復合合分分區(qū)區(qū)（（CompositePartitioning）組合合范范圍圍-散列列分分區(qū)區(qū)(CompositeRange-HashPartitioning)首先先用用范范圍圍分分區(qū)區(qū)對對表表進進行行分分區(qū)區(qū),然后使使用用散散列列模模式式將將每每個個分分區(qū)區(qū)再再分分區(qū)區(qū)。。該分分區(qū)區(qū)方方式式既既具具有有范范圍圍分分區(qū)區(qū)良良好好的的邏邏輯輯管理理性性,還提提供供了了散散列列分分區(qū)區(qū)均均勻勻分分布布數(shù)數(shù)據(jù)據(jù)的優(yōu)優(yōu)勢勢。。4.4分區(qū)區(qū)表表(PartitionsTable)某公公司司的的銷銷售售單單表表sales_order包含含銷銷單單編編號號order_id、銷銷售售日日期order_date、產(chǎn)產(chǎn)品品編編號號procduct_id和數(shù)數(shù)量量quantity四個字字段，，但是該表表按照照邏輯輯范圍圍分區(qū)區(qū)后，，不同同范圍圍的數(shù)數(shù)據(jù)分分布不不均勻勻，試試通過范范圍/散列（（基于于order_id）組合合分區(qū)區(qū)有效效的部部署銷銷售單單表的的數(shù)據(jù)。。組合范范圍-散列分分區(qū)(CompositeRange-HashPartitioning)示例createtablesales_order(order_idnumber,order_datedate,product_idnumber,quantitynumber)PARTITIONBYRANGE(order_date)SUBPARTITIONBYHASH(order_id)SUBPARTITIONS2(PARTITIONp1VALUESLESSTHAN('01-APR-2001'),PARTITIONp2VALUESLESSTHAN('01-JUN-2001'),PARTITIONp3VALUESLESSTHAN('01-OCT-2001'),PARTITIONp4VALUESLESSTHAN('01-JAN-2002'));創(chuàng)建的的四個個范圍圍分區(qū)區(qū)的名名稱在表的的order_date列中創(chuàng)創(chuàng)建范范圍分分區(qū)在每個個范圍圍分區(qū)區(qū)中創(chuàng)建2個散列列子分分區(qū)各類分區(qū)表的創(chuàng)建和使用3各類分區(qū)表的創(chuàng)建和使用3復合分分區(qū)（（CompositePartitioning）組合范范圍-列表分分區(qū)(CompositeRange-HashPartitioning)組合范范圍-列表分分區(qū)(CompositeRange-ListPartitioning)首先用用范圍圍分區(qū)區(qū)對表表進行行分區(qū)區(qū),然后用用列表表分區(qū)區(qū)的方方法將每每個分分區(qū)再再進行行分區(qū)區(qū)。用用戶既既可以以按照照時間間范圍圍來訪訪問數(shù)據(jù)據(jù)（在在分區(qū)區(qū)內(nèi)訪訪問）），也可可以按按照指指定地地理位位置來來訪問數(shù)數(shù)據(jù)（（在子子分區(qū)區(qū)內(nèi)）），，通通過減減少訪訪問的的數(shù)據(jù)據(jù)量，，來進一步步提高高訪問問的性性能。。組合范范圍-列表分分區(qū)(CompositeRange-HashPartitioning)示例創(chuàng)建組組合范范圍-列表分分區(qū)表表createtablesales_region(deptnonumber,sale_datedate,sale_amountnumber,cityvarchar2(10))PARTITIONBYRANGE(sale_date)SUBPARTITIONBYLIST(CITY)(PARTITIONr1_2003VALUESLESSTHAN(to_date(‘1-JUL-2003’’,’’DD-MON-YYYY’’))TABLESPACEusers(SUBPARTITIONr1_2003_1VALUES('北京','上海'),SUBPARTITIONr1_2003_2VALUES('重慶','廣州'),SUBPARTITIONr1_2003_3VALUES('南京','武漢')),PARTITIONr2_2003VALUESLESSTHAN(to_date(‘1-JAN-2004’’,’’DD-MON-YYYY’’))TABLESPACEusers(SUBPARTITIONr2_2003_1VALUES('北京','上海'),SUBPARTITIONr2_2003_2VALUES('重慶','廣州'),SUBPARTITIONr2_2003_3VALUES('南京','武漢')));分區(qū)表的維護4分區(qū)表的維護44.4分區(qū)表表(PartitionsTable)語法格式：ALTERTABLE

表名ADDPARTITION

分區(qū)名;例:在散列分區(qū)表product尾部增加分區(qū)p3。ALTERTABLEproductADDPARTITIONp3tablespaceusers;增加分區(qū)語法格式：ALTERTABLE

表名

SPLITPARTITION

分區(qū)名

AT(分區(qū)列值)

INTO(PARTITION

子分區(qū)1,PARTITION子分區(qū)2);

例:在范圍分區(qū)表sales的中間增加分區(qū);ALTERTABLEsalesSPLITPARTITIONp3AT('01-AUG-2003')INTO(PARTITIONp3_1,PARTITIONp3_2);分割分區(qū)語法格式：ALTERTABLE表名

MERGEPARTITION子分區(qū)名INTOPARTITION分區(qū)名;

例:將sales表分區(qū)p3_1,p3_2合并成一個分區(qū)p3。ALTERTABLEsalesMERGEPARTITIONSp3_1,p3_2INTOPARTITIONp3;合并分區(qū)語法格式：ALTERTABLE表名

RENAMEPARTITION舊名

TO新名;例:將sales表分區(qū)p3更名為p3_1。ALTERTABLEsalesRENAMEPARTITIONp3TOp3_1;重命名分區(qū)語法格式1：ALTERTABLE表名

DROPPARTITION分區(qū)名;語法格式2：ALTERTABLE

表名TRUNCATEPARTITION

分區(qū)名;例:將sales表分區(qū)p3_1中的數(shù)據(jù)刪除掉。刪除分區(qū)語法格式1：ALTERTABLE分區(qū)表

EXCHANGEPARTITION分區(qū)名

WITHTABLE常規(guī)表

例:將sales表p1分區(qū)中的數(shù)據(jù)導入到同結(jié)構(gòu)的常規(guī)表sales_1表中;模擬故障刪除P1分區(qū)中的數(shù)據(jù),然后將sales_1表中的數(shù)據(jù)還原到sales表的p1區(qū)中.交換分區(qū)分區(qū)維護操作有：添加分區(qū)拆分分區(qū)合并分區(qū)重命名分區(qū)刪除分區(qū)交換分區(qū)1234564.4分區(qū)表表(PartitionsTable)分區(qū)表小結(jié)5分區(qū)表小結(jié)5表分區(qū)區(qū)為了簡簡化數(shù)數(shù)據(jù)庫庫大表表的管管理，，例如如在數(shù)數(shù)據(jù)倉倉庫中中一般般都是是TB級的數(shù)數(shù)量級級。Oracle8以后推推出了了分區(qū)區(qū)選項項，分分區(qū)將將表分分離在在若干干不同同的表表空間間上，，用分分而治治之的的方法法來支支撐無無限膨膨脹的的大表表，提提高大大表在在物理理一級級的可可管理理性。。將大大表分分割成成較小小的分分區(qū)可可以改改善表表的維維護、、備份份、恢恢復、、事務(wù)務(wù)及查查詢性性能。。分區(qū)的的優(yōu)點點1.增強可可用性性2.減少關(guān)關(guān)閉時時間3.維護輕輕松4.均衡I/O5.改善性性能6.分區(qū)對對用戶戶透明明索引組織表與常規(guī)表的使用比較3創(chuàng)建索引組織表2索引組織表及特征1索引組織表的優(yōu)勢分析44.5索引組組織表表(IndexOrganizedTableIOT)葉子數(shù)數(shù)據(jù)塊塊中存存儲以以排好好序的的索引主主鍵以以及記記錄對對應(yīng)的的物理理地址rowid，根據(jù)據(jù)rowid定位記記錄。。4.5索引組組織表表(IndexOrganizedTableIOT)索引組織表及特征1索引組織表及特征1…………建立B樹索引…數(shù)據(jù)是是按照照主鍵鍵順序序存放放在葉子子數(shù)據(jù)據(jù)塊中中。找找到主主鍵也就找找到完完整的的數(shù)據(jù)據(jù)記錄錄?！饕M組織表表擁有有索引引和表表兩者者的特特征。。索引引組織織表是是以B-樹索引引結(jié)構(gòu)構(gòu)存儲儲數(shù)據(jù)據(jù)的表表,它將表表的數(shù)數(shù)據(jù)（（非主主鍵列列）和和索引引字段段（主主鍵列列）一一同存存儲在在索引引段中中。因此此找到到記錄錄的主主鍵也也就找找到了了相應(yīng)應(yīng)記錄錄的完完整內(nèi)內(nèi)容。。索引組組織表表的缺缺點：：數(shù)據(jù)塊塊中的的數(shù)據(jù)據(jù)會隨隨著記記錄的的插入入、刪刪除等等操作作在數(shù)數(shù)據(jù)塊塊之間間移動動,因此而而產(chǎn)生生開銷銷，適適用于于OLAP,不適用用OLTP.1..5051..100101..150……10000..100500..1011..1920..25……42..5051..5859..6364..75……98..1001,rowid2,rowid3,rowid……10,rowid11,rowid12,rowid13,rowid……19,rowid10021,rowid10022,rowid10023,rowid……10028,rowid10046,rowid10047,rowid10048,rowid……10050,rowid………………10000..1000910010..1002010021..10028……10046..10050100501004910048…975431備注價格規(guī)格名稱產(chǎn)品編碼4.5索引組組織表表(IndexOrganizedTableIOT)創(chuàng)建索引組織表2創(chuàng)建索引組織表2建立索索引組組織表表的關(guān)關(guān)鍵建表的的同時時必須須定義義主鍵鍵。創(chuàng)建索索引組組織表表EMP_WORK，包括括員工工編號號empnovarchar2(8),工作日日期work_date兩個字字段，，并檢檢查是是否創(chuàng)創(chuàng)建成成功。。createtableemp_work(empnovarchar2(8),work_datedate,constraintpk_ewprimarykey(empno,work_date))organizationindex;測試索索引組組織表表物理理存儲儲的有有序性性t15.sql。4.5索引組組織表表(IndexOrganizedTableIOT)索引組織表與常規(guī)表的使用比較3索引組織表與常規(guī)表的使用比較3例：假假設(shè)設(shè)每個個員工工都有有3~4個（或者者更多））的（地地址）詳詳細記錄錄，但是是這些詳詳細記錄錄是隨機機到來的的。創(chuàng)建建員工詳詳細地址址表包括括員工編編號empno,地址類型型addr_type,街道street,城市city,州state,郵政編碼碼zip，主鍵為為(empno,addr_type)。要求:將同一員員工的地地址信息息盡量存存儲在相相同的或或鄰近的的數(shù)據(jù)塊塊上,以便在反反復獲取取記錄時時,減少系統(tǒng)統(tǒng)訪問的的工作量量.分析：由由于員工工的地址址信息是是隨機到到來的，，那么同同一員工工的各個個地址存存放在同同一個數(shù)數(shù)據(jù)塊或或臨近數(shù)數(shù)據(jù)庫塊塊上概率率基本接接近于0，但是我我們在執(zhí)執(zhí)行SQL操作的時時候又希希望總是是把所有有地址詳詳細記錄錄都取出出來。因因此，建建議對該該地址表表使用IOT表，每插插入一個個員工地地址，都都會根據(jù)據(jù)索引主主鍵將記記錄插入入與該員員工其他他地址相相互“靠靠近”的的地方，，這樣在在反復獲獲取記錄錄時，可可以減少少工作量量。10002workRTstreetbostonAD32334empnoAddr_typestreetcitystatezip10001homeADTstreetchicagoAC6544310001workmainstreetWashingtonDC2013210002homemainstreetWashingtonOP3828210002workRTstreetbostonAD3233410001homeADTstreetchicagoAC6544310001workmainstreetWashingtonDC2013210002homemainstreetWashingtonOP38282將地址表表實現(xiàn)為為常規(guī)表表，數(shù)據(jù)據(jù)的具體體存儲將地址表表實現(xiàn)為為索引組組織表，，數(shù)據(jù)的的具體存存儲10002workRTstreetbostonAD32334empnoAddr_typestreetcitystatezip10001homeADTstreetchicagoAC6544310001workmainstreetWashingtonDC2013210002homemainstreetWashingtonOP3828210002workRTstreetbostonAD3233410001workMainstreetWashingtonDC2013210001homeADTstreetchicagoAC6544310002homemainstreetWashingtonOP3828210001workmainstreetWashingtonDC2013210002homemainstreetWashingtonOP382824.5索引組織織表(IndexOrganizedTableIOT)1、將EMP表設(shè)置為為主表，，EMPNO為其主關(guān)關(guān)鍵字。。將其子子表（員員工的詳詳細地址址表），，用堆組組織表和和索引組組織表的的方式實實現(xiàn)兩次次。創(chuàng)建EMP表并填充充測試數(shù)數(shù)據(jù)；將員工詳詳細地址址表創(chuàng)建建為索引引組織表表iot_addresses；將員工詳詳細地址址表創(chuàng)建建為堆組組織表heap_addresses；2、分別向向heap_addresses、iot_addresses表中插入入大量示示例數(shù)據(jù)據(jù)。3、啟用SQLTRACE工具查看看以下兩兩條SQL語句的CPU使用情況況。select*fromemp,heap_addresseswhereemp.empno=heap_addresses.empnoandemp.empno=42;select*fromemp,iot_addresseswhereemp.empno=iot_addresses.empnoandemp.empno=42;①使用索引引組織表可快快速準確地進進行匹配查找找或根據(jù)主關(guān)關(guān)鍵字在一定定范圍內(nèi)的查查詢。一旦找找到鍵值，在在該位置中也也找到其余數(shù)數(shù)據(jù)。這樣消消除了頻繁的的I/O操作。②最適合數(shù)數(shù)據(jù)庫24x7狀態(tài)下的表結(jié)結(jié)構(gòu)，當數(shù)據(jù)據(jù)庫必須保持持隨時聯(lián)機狀狀態(tài)時，可聯(lián)聯(lián)機重新組織織IOT，無需重建其其輔助索引。。③減少了存存儲需求。關(guān)關(guān)鍵字列與表表和索引并不不重復，也不不需要額外存存儲rowid。當關(guān)鍵字列列占據(jù)了一行行的大部分時時，可節(jié)省存存儲空間。4.5索引組織表(IndexOrganizedTableIOT)索引組織表的優(yōu)勢分析4索引組織表的優(yōu)勢分析44.6對象表(ObjectTable)傳統(tǒng)關(guān)系型僅僅支持簡單數(shù)數(shù)據(jù)類型；不不支持數(shù)組、、嵌套和遞歸歸的數(shù)據(jù)結(jié)構(gòu)構(gòu)，給新型數(shù)數(shù)據(jù)模型-面向?qū)ο蟮臄?shù)數(shù)據(jù)模型(OODBS)提供了應(yīng)用背背景。目前,以關(guān)系數(shù)據(jù)庫庫和SQL為基礎(chǔ)擴展關(guān)關(guān)系模型以及及對象關(guān)系數(shù)據(jù)據(jù)庫(ORDBMS)的發(fā)展?jié)M足了了許多應(yīng)用需需求。ORACLE、DB2UDB、INFORMIX都推出ORDBMS,ANSI/ISO發(fā)布的SQL-99標準是一個對對象關(guān)系標準準。姓名職務(wù)家屬關(guān)系姓名張家口職員妻子李小玫兒子張冠李柴米油經(jīng)理妻子陸續(xù)琴郝成功職員妻子朱那亞兒子郝乃軍女兒郝佳欣employeeseidenameJobnamesex0001趙子龍男經(jīng)理0002張翌德男職員0003孟可飛女職員對象表的引入1對象表的引入1例:createtypenamesex_typeasobject(namevarchar2(8),sexvarchar2(2));創(chuàng)建對象類型2創(chuàng)建對象類型2一個對象類型型由多個成員員屬性構(gòu)成,ORACLE中對象類型由由Createtype語句創(chuàng)建，也可在OEM中創(chuàng)建。createtype類型名asobject(字段1類型（長度）），字段2類型（長度））……）;/SQL*plus中創(chuàng)建對象類類型必須有/定義了一個對對象構(gòu)造器。。4.6對象表(ObjectTable)employeeseidenameJobnamesex0001趙子龍男經(jīng)理0002張翌德男職員0003孟可飛女職員4.6對象表(ObjectTable)基于對象類型創(chuàng)建對象表3基于對象類型創(chuàng)建對象表3createtableemployees(eidvarchar2(4),enamenamesex_type,jobvarchar2(6));4.6對象表(ObjectTable)操作對象表數(shù)據(jù)4操作對象表數(shù)據(jù)4通過對象構(gòu)造造器為對象類類型字段賦值值typename(屬性值)稱為對象構(gòu)造造器通過訪問對象象字段的成員員屬性，訪問問成員值表名.對象屬性．成員屬性INSERTINTOemployeesVALUES('0001',namesex_type('趙子龍','男'),'經(jīng)理');WHEREe.job='經(jīng)理';SELECT*FROMemployeesWHEREe.ename.sex='男';employeeseidenameJobnamesex0001趙子龍男經(jīng)理0002張翌德男職員0003孟可飛女職員4.7嵌套表(NestedTable)嵌套表的使用*3創(chuàng)建嵌套表2嵌套表及特征1嵌套表及其特點１嵌套表及其特點１4.7嵌套表(NestedTable)嵌套表（nestedtable）是oracle對象關(guān)系擴展展的一部分，，它和關(guān)系模模型中傳統(tǒng)的的“父/子表對”中的的子表很相似似。這是數(shù)據(jù)據(jù)元素的一個個無序集合，，所有的數(shù)據(jù)據(jù)元素的數(shù)據(jù)據(jù)類型都相同同，可以是一一個內(nèi)置數(shù)據(jù)據(jù)類型，也可可以是一個對對象數(shù)據(jù)類型型。嵌套表的使用用方法：一種種是在PL/SQL代碼中使用，，用來擴展PL/SQL語言。另一種種方法可作為為物理存儲機機制，持久的的存儲數(shù)據(jù)。。DEPTNODNAMELOC

10ACCOUNTINGNEWYORK20RESEARCHDALLAS

30SALESCHICAGO

40