oracle數(shù)據(jù)庫技術(shù)第八講模式對象.ppt

第八講 模式對象,本講內(nèi)容,模式概念 表管理 索引管理 索引化表管理 分區(qū)表與分區(qū)索引的管理 外部表的管理 簇、視圖、序列、同義詞、數(shù)據(jù)庫鏈接管理,模式是指一系列邏輯數(shù)據(jù)結(jié)構(gòu)或?qū)ο蟮募稀?模式與用戶相對應(yīng)，一個模式只能被一個數(shù)據(jù)庫用戶所擁有，并且模式名稱與擁有用戶的名稱相同。 用戶所創(chuàng)建數(shù)據(jù)庫對象都保存在與同名模式中。 同一模式中數(shù)據(jù)庫對象的名稱必須惟一，而在不同模式中的數(shù)據(jù)庫對象可以同名。 默認(rèn)情況下，用戶引用的對象是與同名模式中的對象，如果要引用其他模式中的對象，需要在該對象名之前指明對象所屬模式。,1 模式概念,模式選擇與切換 如果用戶以NORMAL身份登錄，則進(jìn)入同名模式； 若以SYSDBA身份登錄，則進(jìn)入SYS模式； 如果以SYSOPER身份登錄，則進(jìn)入PUBLIC模式。,1 模式概念,2 表,創(chuàng)建表 表約束的管理 表參數(shù)設(shè)置 修改表 刪除表 利用OEM管理表,2.1 創(chuàng)建表,表的創(chuàng)建 創(chuàng)建臨時表 利用子查詢創(chuàng)建表,2.1.1 表的創(chuàng)建,表的規(guī)劃與設(shè)計 表名稱 列的數(shù)量 列名稱與類型 表約束 表內(nèi)部數(shù)據(jù)的組織方式（標(biāo)準(zhǔn)表、索引化表、分區(qū)表） 表存儲位置、存儲空間分配等。,2.1.1 表的創(chuàng)建,使用CREATE TABLE語句 創(chuàng)建表 CREATE TABLE table_name (column_name datatype column_level_constraint ，column_name datatype column_level_constraint ，table_level_constraint) parameter_list;,2.1.1 表的創(chuàng)建,CREATE TABLE employee( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15), deptno NUMBER(3) NOT NULL CONSTRAINT fk_emp REFERENCES dept（deptno） ) TABLESPACE USERS PCTFREE 10 PCTUSED 40 STORAGE(INITIAL 50K NEXT 50K MAXEXTENTS 10 PCTINCREASE 25);,表名（table_name） 必須是合法標(biāo)識符 數(shù)據(jù)類型(datatype) 字符類型 數(shù)字類型 日期類型 LOB類型 二進(jìn)制類型 行類型,2.1.1 表的創(chuàng)建,2.1.1 表的創(chuàng)建,約束（constraint） 表級約束和列級約束2種 對列的約束包括主鍵約束、惟一性約束、檢查約束、外鍵約束和空/非空約束等5種。 參數(shù)（parameter_list） 可以通過參數(shù)設(shè)置表存儲在哪一個表空間中，和存儲空間分配等。,CHAR（n）:定長字符串，n的取值范圍為1-2000字節(jié) VARCHAR2（n）:可變字符串，n取值范圍為1-4000字節(jié) NCHAR(n):用來存儲Unicode類型字符串。 NVARCHAR2(n) :它用來存儲Unicode類型字符串。 LONG:可變長字符列，最大長度為2GB,字符類型,數(shù)字類型,NUMBER（m，n） 用于存儲整數(shù)和實數(shù)。 m表示數(shù)值的總位數(shù)（精度），取值范圍為138，默認(rèn)為38； n表示小數(shù)位數(shù)，若為負(fù)數(shù)則表示把數(shù)據(jù)向小數(shù)點左邊舍入，默認(rèn)值為0。,日期類型,DATE 由世紀(jì)、年、月、日、時、分、秒組成。 范圍為公元前4712年1月1日到公元4712年1月1日。 可在會話中使用參數(shù)NLS_DATE_FORMAT指定日期和時間的格式。 可使用TO_DATE函數(shù)，將表示日期和時間的字符串按特定格式轉(zhuǎn)換成日期和時間。,日期類型,TIMESTAMP(n)：表示時間戳 DATE 數(shù)據(jù)類型的擴展 允許存儲小數(shù)形式的秒值。 n表示秒的小數(shù)位數(shù)，取值范圍為19，默認(rèn)值為6。 TIMESTAMP(n) WITH TIME ZONE 通過存儲一個時區(qū)偏差擴展TIMESTAMP類型。 時區(qū)偏差值為相對于通用協(xié)調(diào)時間（或稱UTC，以前稱為格林威治時間或GMT）的時差。,日期類型,TIMESTAMP(n) WITH LOCAL TIME ZONE 存儲日期時直接轉(zhuǎn)換為數(shù)據(jù)庫時區(qū)日期 而讀取日期時將數(shù)據(jù)庫時區(qū)日期轉(zhuǎn)換為用戶會話時區(qū)日期。 INTERVAL YEAR(n) TO MONTH 存儲以年份和月份表示的時間段。 n是表示YEAR的最多數(shù)字位數(shù)，默認(rèn)為2。,日期類型,INTERVAL DAY(m) TO SECOND(n) 存儲以天數(shù)、小時數(shù)、分鐘數(shù)和秒數(shù)表示的時間段 m是表示DAY的最多數(shù)字位數(shù)，默認(rèn)為2。 n是表示SECOND的小數(shù)部分的位數(shù)，默認(rèn)為6。,LOB類型,CLOB：用于存儲可變長度的字符數(shù)據(jù)，如文本文件等，最大數(shù)據(jù)量為4 GB。 NCLOB：用于存儲可變長度的Unicode字符數(shù)據(jù)，最大數(shù)據(jù)量為4 GB。 BLOB：用于存儲大型的、未被結(jié)構(gòu)化的可變長度的二進(jìn)制數(shù)據(jù)（如二進(jìn)制文件、圖片文件、音頻和視頻等非文本文件），最大數(shù)據(jù)量為4 GB。 BFILE：用于存儲指向二進(jìn)制格式文件的定位器，該二進(jìn)制文件保存在數(shù)據(jù)庫外部的操作系統(tǒng)中，文件最大為4 GB。,二進(jìn)制類型,RAW(n) 用于存儲可變長度的二進(jìn)制數(shù)據(jù) n表示數(shù)據(jù)長度，取值范圍為12000字節(jié)； LONG RAW 用于存儲可變長度的二進(jìn)制數(shù)據(jù) 最大存儲數(shù)據(jù)量為2 GB。,行類型,ROWID 行標(biāo)識符，表中行的物理地址的偽列類型。 ROWID類型數(shù)據(jù)由18位十六進(jìn)制數(shù)構(gòu)成，包括對象編號、文件編號、數(shù)據(jù)塊編號和塊內(nèi)行號。 UROWID 行標(biāo)識符，用于表示索引化表中行的邏輯地址。,2.1.2 創(chuàng)建臨時表,臨時表中的數(shù)據(jù)在特定條件下自動釋放，但其結(jié)構(gòu)將一直存在。 根據(jù)臨時表中數(shù)據(jù)被釋放的時間不同，臨時表分為事務(wù)級別的臨時表和會話級別的臨時表兩類。 ON COMMIT DELETE ROWS /事務(wù)級臨時表 ON COMMIT PRESERVE ROWS /會話級臨時表,2.1.2 創(chuàng)建臨時表,語法 使用CREATE GLOBAL TEMPORARY TABLE語句創(chuàng)建臨時表 使用ON COMMIT子句說明臨時表的類型，默認(rèn)為事務(wù)級別的臨時表。,2.1.2 創(chuàng)建臨時表,事務(wù)級別的臨時表是在事務(wù)提交時系統(tǒng)自動刪除表中所有記錄。 CREATE GLOBAL TEMPORARY TABLE tran_temp(ID NUMBER(2) PRIMARY KEY, name VARCHAR2(20) ) ON COMMIT DELETE ROWS;,2.1.2 創(chuàng)建臨時表,會話級別的臨時表是在會話終止時系統(tǒng)自動刪除表中所有記錄 。 CREATE GLOBAL TEMPORARY TABLE sess_temp(ID NUMBER(2) PRIMARY KEY, name VARCHAR2(20) ) ON COMMIT PRESERVE ROWS;,2.1.3 利用子查詢創(chuàng)建表,語法 CREATE TABLE table_name (column_name column_level_constraint ，column_name column_level_constraint ，table_level_constraint) parameter_list AS subquery;,2.1.3 利用子查詢創(chuàng)建表,注意 可以修改表中列的名稱，但是不能修改列的數(shù)據(jù)類型和長度； 源表中的約束條件和列的缺省值都不會復(fù)制到新表中； 子查詢中不能包含LOB類型和LONG類型列； 當(dāng)子查詢條件為真時，新表中包含查詢到的數(shù)據(jù)；當(dāng)查詢條件為假時，則創(chuàng)建一個空表。,2.1.3 利用子查詢創(chuàng)建表,創(chuàng)建一個標(biāo)準(zhǔn)表，保存工資高于3000的員工的員工號、員工名和部門號。語句為 CREATE TABLE emp_select(emp_no,emp_name,dept_no) AS SELECT empno,ename,deptno FROM employee WHERE sal3000;,2.1.3 利用子查詢創(chuàng)建表,創(chuàng)建一個會話級臨時表，保存部門號、部門人數(shù)和部門的平均工資。 CREATE GLOBAL TEMPORARY TABLE ept_temp ON COMMIT PRESERVE ROWS AS SELECT deptno,count(*) num,avg(sal) avgsal FROM emp GROUP BY deptno;,2.2 表約束,約束的類別 定義約束 添加和刪除約束 設(shè)置約束狀態(tài) 約束的延遲檢查 查詢約束信息,2.2.1 約束的類別,約束作用 在表中定義的用于維護數(shù)據(jù)庫完整性的一些規(guī)則。 通過對表中列定義約束，可以防止在執(zhí)行DML操作時，將不符合要求的數(shù)據(jù)插入到表中。,2.2.1 約束的類別,約束類型 PRIMARY KEY UNIQUE CHECK FOREIGN KEY NULL/NOT NULL,PRIMARY KEY,特點 定義主鍵，起惟一標(biāo)識作用，其值不能為NULL，也不能重復(fù)； 一個表中只能定義一個主鍵約束； 建立主鍵約束的同時，在該列上建立一個惟一性索引，可以為它指定存儲位置和存儲參數(shù)； 主鍵約束可以是列級約束，也可以是表級約束。,UNIQUE,特點 定義為惟一性約束的某一列或多個列的組合的取值必須惟一； 如果某一列或多個列僅定義惟一性約束，而沒有定義非空約束，則該約束列可以包含多個空值； Oracle自動在惟一性約束列上建立一個惟一性索引，可以為它指定存儲位置和存儲參數(shù)； 惟一性約束可以是列級約束，也可以是表級約束。,在一個基本表中只能定義一個PRIMARY KEY約束，但可定義多個UNIQUE約束； 對于指定為PRIMARY KEY的一個列或多個列的組合，其中任何一個列都不能出現(xiàn)空值，而對于UNIQUE所約束的唯一鍵，則允許為空。 不能為同一個列或一組列既定義UNIQUE約束，又定義PRIMARY KEY約束。,PRIMARY KEY與UNIQUE比較,CHECK,特點 檢查約束用來限制列值所允許的取值范圍，其表達(dá)式中必須引用相應(yīng)列，并且表達(dá)式的計算結(jié)果必須是一個布爾值； 約束表達(dá)式中不能包含子查詢，也不能包含SYSDATE、USER等SQL函數(shù)，和ROWID、ROWNUM等偽列； 一個列可以定義多個檢查約束； 檢查約束可以是列級約束，也可以是表級約束。,概念 FOREIGN KEY約束指定某一個列或一組列作為外部鍵。 系統(tǒng)保證從表在外部鍵上的取值要么是主表中某一個主鍵值或唯一鍵值，要么取空值。 保證兩個表之間的連接，確保了實體的參照完整性。,FOREIGN KEY,特點 定義外鍵約束的列的取值要么是主表參照列的值，要么為空； 外鍵列只能參照于主表中的主鍵約束列或惟一性約束列； 可以在一列或多列組合上定義外鍵約束； 外鍵約束可以是列級約束，也可以是表級約束。,FOREIGN KEY,NULL/NOT NULL,特點 在同一個表中可以定義多個NOT NULL約束； 只能是列級約束。,2.2.2 定義約束,列約束 對某一個特定列的約束，包含在列定義中 直接跟在該列的其他定義之后，用空格分隔，不必指定列名； 表約束 與列定義相互獨立，不包括在列定義中。 用于對多個列一起進(jìn)行約束，與列定義用,分隔。 定義表約束時必須指出要約束的那些列的名稱。,定義列級約束的語法為： CONSTRAINT constraint_name constraint_type conditioin; 定義表級約束的語法為： CONSTRAINT constraint_name constraint_type(column1_name, column2_name,|condition); 注意 Oracle約束通過名稱進(jìn)行標(biāo)識。在定義時可以通過CONSTRAINT關(guān)鍵字為約束命名。如果用戶沒有為約束命名，Oracle將自動為約束命名。,2.2.2 定義約束,創(chuàng)建一個student表。 CREATE TABLE student( sno NUMBER(6) CONSTRAINT S_PK PRIMARY KEY, sname VARCHAR2(10) NOT NULL, sex CHAR(2) CONSTRAINT S_CK1 CHECK(sex in(M, F), sage NUMBER(6,2), CONSTRAINT S_CK2 CHECK( sage between 18 and 60) );,2.2.2 定義約束,2.2.2 定義約束,創(chuàng)建一個course表，同時為主鍵約束列上的唯一性索引設(shè)置存儲位置和存儲參數(shù)，語句為 CREATE TABLE course( cno NUMBER(6) PRIMARY KEY, cname CHAR(20) UNIQUE USING INDEX TABLESPACE indx STORAGE( INITIAL 64K NEXT 64K) );,2.2.2 定義約束,創(chuàng)建一個SC表，語句為 CREATE TABLE SC( sno NUMBER(6) REFERENCES student(sno), cno NUMBER(6) REFERENCES course(cno), grade NUMBER(5,2), CONSTRAINT SC_PK PRIMARY KEY(sno, cno) );,定義列級FOREIGN KEY約束 CONSTRAINT constraint_name FOREIGN KEY REFERENCES ref_table_name (column_name,),2.2.2 定義約束,2.2.2 定義約束,定義表級FOREIGN KEY約束 CONSTRAINT constraint_name FOREIGN KEY (column_name,) REFERENCES ref_table_name (column_name,) ON DELETE CASCADE|SET NULL;,ON DELETE CASCADE 刪除子表中所有相關(guān)記錄 ON DELETE SET NULL 將子表中相關(guān)記錄的外鍵約束列值設(shè)置為NULL ON DELETE RESTRICTED 受限刪除，即如果子表中有相關(guān)子記錄存在，則不能刪除主表中的父記錄，默認(rèn)引用方式。,2.2.2 定義約束,DEFAULT 如果用戶在插入新行時沒有顯示為列提供數(shù)據(jù)，系統(tǒng)將默認(rèn)值賦給該列。 語法 CONSTRAINT DEFAULT 表達(dá)式,2.2.2 定義約束,2.2.3 添加和刪除約束,添加約束語法為： ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type(column1_name,column2_name,)condition;,創(chuàng)建一個player表 CREATE TABLE player( ID NUMBER(6), sno NUMBER(6), sname VARCHAR2(10), sage NUMBER(6,2), resume VARCHAR2(1000) );,2.2.3 添加和刪除約束,2.2.3 添加和刪除約束,添加主鍵約束 ALTER TABLE player ADD CONSTRAINT P_PK PRIMARY KEY(ID); 添加惟一性約束 ALTER TABLE player ADD CONSTRAINT P_UK UNIQUE(sname);,添加檢查約束 ALTER TABLE player ADD CONSTRAINT P_CK CHECK(sage BETWEEN 20 AND 30); 添加外鍵約束 ALTER TABLE player ADD CONSTRAINT P_FK FOREIGN KEY(sno)REFERENCES student(sno) ON DELETE CASCADE;,2.2.3 添加和刪除約束,添加空/非空約束 為表列添加空/非空約束時必須使用MODIFY子句代替ADD子句 ALTER TABLE player MODIFY resume NOT NULL; ALTER TABLE player MODIFY resume NULL;,2.2.3 添加和刪除約束,刪除約束 使用ALTER TABLEDROP語句刪除已經(jīng)定義約束。 通過直接指定約束的名稱來刪除約束或指定約束的內(nèi)容來刪除約束。 刪除指定內(nèi)容的約束 ALTER TABLE player DROP UNIQUE(sname); 刪除指定名稱的約束 ALTER TABLE player DROP CONSTRAINT P_CK;,2.2.3 添加和刪除約束,如果要在刪除主鍵約束、唯一性約束的同時將刪除唯一性索引 刪除約束時保留唯一性索引，則必須在ALTER TABLEDORP 語句中指定KEEP INDEX 子句。 ALTER TABLE player DROP CONSTRAINT P_UK KEEP INDEX;,2.2.3 添加和刪除約束,2.2.3 添加和刪除約束,如果要在刪除約束的同時，刪除引用該約束的其他約束，則需要在ALTER TABLEDORP語句中指定CASCADE關(guān)鍵字。 ALTER TABLE player DROP CONSTRAINT P_PK CASCADE;,2.2.4 設(shè)置約束狀態(tài),激活（ENABLE）狀態(tài) 當(dāng)約束處于激活狀態(tài)時，約束將對表的插入或更新操作進(jìn)行檢查，與約束規(guī)則沖突的操作被回退。 禁用（DISABLE）狀態(tài) 當(dāng)約束處于禁用狀態(tài)時，約束不起作用，與約束規(guī)則沖突的插入或更新操作也能夠成功執(zhí)行。 從外部數(shù)據(jù)源提取大量數(shù)據(jù)到數(shù)據(jù)庫中； 進(jìn)行數(shù)據(jù)庫中數(shù)據(jù)的大量導(dǎo)入、導(dǎo)出操作； 針對表執(zhí)行一項包含大量數(shù)據(jù)操作的批處理工作時。,禁用約束 在定義約束時，可以將約束設(shè)置為禁用狀態(tài)，默認(rèn)為激活狀態(tài)。 創(chuàng)建表時禁用約束 CREATE TABLE S (SNO CHAR() PRIMARY KEY DISALBE,); 利用ALTER TABLEDISABLE禁用約束 ALTER TABLE STUDENT DISABLE CONSTRAINT S_CK1; ALTER TABLE STUDENT DISABLE UNIQUE (sname);,2.2.4 設(shè)置約束狀態(tài),禁用主鍵約束、惟一性約束時，會刪除其對應(yīng)的惟一性索引，而在重新激活時，Oracle為它們重建惟一性索引。 若在禁用約束時，保留對應(yīng)的惟一性索引，可使用ALTER TABLEDISABLEKEEP INDEX語句。 ALTER TABLE STUDENT DISABLE UNIQUE (sname) KEEP INDEX; ALTER TABLE STUDENT DISABLE PRIMARY KEY KEEP INDEX;,2.2.4 設(shè)置約束狀態(tài),2.2.4 設(shè)置約束狀態(tài),若當(dāng)前約束(主鍵約束、惟一性約束)列被引用，則需要使用ALTER TABLEDISABLECASCADE語句同時禁用引用該約束的約束。 ALTER TABLE STUDENT DISABLE PRIMARY KEY KEEP INDEX CASCADE;,激活約束 創(chuàng)建或添加約束時，默認(rèn)為激活狀態(tài)。 利用ALTER TABLEENABLE語句激活約束 ALTER TABLE STUDENT ENABLE UNIQUE (sname);,2.2.4 設(shè)置約束狀態(tài),2.2.4 設(shè)置約束狀態(tài),禁用主鍵約束、惟一性約束時，會刪除其對應(yīng)的惟一性索引， 而在重新激活時，Oracle為它們重建惟一性索引，可以為索引設(shè)置存儲位置和存儲參數(shù)(索引與表盡量分開存儲)。 ALTER TABLE STUDENT ENABLE PRIMARY KEY USING INDEX TABLESPACE indx STORAGE(INITIAL 32K NEXT 16K);,2.2.4 設(shè)置約束狀態(tài),通過ALTER TABLE MODIFY DISABLE| ENABLE 語句改變約束狀態(tài) ALTER TABLE STUDENT MODIFY CONSTRAINT S_CK2 DISABLE;,2.2.5 約束的延遲檢查,默認(rèn)情況下，在表中的約束都是不可延遲約束，在一條DML語句執(zhí)行完畢之后立即進(jìn)行約束檢查（除非禁用）。 創(chuàng)建約束時可以顯式使用DEFERRABLE關(guān)鍵字，創(chuàng)建可延遲的約束。 INITIALLY IMMEDIATE 或INITIALLY DEFERRED說明可延遲約束在初始狀態(tài)下是立即檢查還是延遲檢查 如果在定義約束時設(shè)定為不可延遲，則約束創(chuàng)建后不能更改其可延遲性。只有創(chuàng)建時設(shè)定為可延遲的約束，創(chuàng)建后才能更改其可延遲性。,創(chuàng)建兩個表，其約束都是可延遲的。 CREATE TABLE new_dept( deptno NUMBER PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE, dname CHAR() UNIQUE );,2.2.5 約束的延遲檢查,2.2.5 約束的延遲檢查,CREATE TABLE new_emp( empno NUMBER PRIMARY KEY, ename CHAR(), deptno NUMBER CONSTRAINT NE_FK REFERENCES new_dept(deptno) ON DELETE CASCADE DEFERRABLE );,由于外鍵約束的作用，執(zhí)行下面語句時會產(chǎn)時錯誤。 INSERT INTO new_emp VALUES(1,ZHANG,); * ERROR 位于第 1 行: ORA-02291: 違反完整約束條件 (SCOTT.E_FK) - 未找到父項關(guān)鍵字,2.2.5 約束的延遲檢查,將new_emp表的外鍵約束檢查延遲。 ALTER TABLE new_emp MODIFY CONSTRAINT NE_FK INITIALLY DEFERRED; 此時，由于將new_emp表外鍵約束延遲到事務(wù)結(jié)束后進(jìn)行檢查，因此可以先向new_emp中插入數(shù)據(jù)，而后向new_dept中插入數(shù)據(jù)。 INSERT INTO new_emp VALUES(1,ZHANG,); INSERT INTO new_dept VALUES(,COMPUTER); COMMIT;,2.2.5 約束的延遲檢查,操作完后，應(yīng)將new_emp外鍵約束檢查恢復(fù)原來狀態(tài)。 ALTER TABLE new_emp MODIFY CONSTRAINT NE_FK INITIALLY IMMEDIATE; 注意 在修改約束的檢查延遲性時，如果無法確定約束的名稱或需要設(shè)置多個約束的延遲性，可以一次性將所有可延遲的約束延遲或恢復(fù)。 SET CONSTRAINT ALL DEFERRED; SET CONSTRAINT ALL IMMEDIATE;,2.2.5 約束的延遲檢查,2.2.6 查詢約束信息,ALL_CONSTRAINTS USER_CONSTRAINTS DBA_CONSTRAINTS ALL_CONS_COLUMNS USER_CONS_COLUMNS DAB_CONS_COLUMNS,查看student表中的所有約束。 SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,DEFERRED,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME=STUDENT; 查看student表中各個約束所作用的列。 SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME=STUDENT;,2.2.6 查詢約束信息,2.3 表參數(shù)設(shè)置,TABLESPACE TABLESPACE子句用于指定表存儲的表空間。 STORAGE STORAGE子句用于設(shè)置表的存儲參數(shù)。若不指定，則繼承表空間的存儲參數(shù)設(shè)置。 NITIAL NEXT PCTINCREASE MINEXTENTS MAXEXTENTS BUFFER_POOL (KEEP、RECYCLE、DEFAULT),2.3 表參數(shù)設(shè)置,STORAGE參數(shù)設(shè)置需注意 如果表空間管理方式為EXTENT MANAGEMENT LOCAL AUTOALLOCATE，則在STORAGE中只能指定INITIAL，NEXT和MINEXTENTS這3個參數(shù)； 如果表空間管理方式為EXTENT MANAGEMENT LOCAL UNIFORM，則不能指定任何STORAGE子句； 如果表空間管理方式為EXTENT MANAGEMENT DICTIONARY，則在STORAG中可以設(shè)置任何參數(shù)。,2.3 表參數(shù)設(shè)置,數(shù)據(jù)塊管理參數(shù) PCTFREE：用于指定數(shù)據(jù)塊中必須保留的最小空閑空間。 PCTUSED：用于指定當(dāng)數(shù)據(jù)塊空閑空間達(dá)到PCTFREE參數(shù)的限制后，數(shù)據(jù)塊能夠被再次使用前，已占用的存儲空間必須低于的比例。 INITRANS：用于指定能夠并發(fā)訪問同一個數(shù)據(jù)塊的事務(wù)的數(shù)量。 MAXTRANS：用于指定能夠并發(fā)訪問同一個數(shù)據(jù)塊的事務(wù)的最大數(shù)量。,LOGGING與NOLOGGING子句 默認(rèn)為NOLOGGING，即表的創(chuàng)建操作不會記錄到重做日志文件中，尤其適合通過查詢創(chuàng)建表的情況。使用LOGGING子句，表的創(chuàng)建操作（包括通過查詢創(chuàng)建表時的插入記錄操作）都將記錄到重做日志文件中。 PARALLEL、NOPARALLEL CACHE、NOCACHE MONITORING、NOMONITORING,2.3 表參數(shù)設(shè)置,2.4 修改表,基本語法 列的添加、刪除、修改 表參數(shù)修改 表結(jié)構(gòu)重組 表重命名等 為表和列添加注釋,（）修改表基本語法,ALTER TABLE ADD RENAME COLUMN oldname TO newname SET UNUSED COLUMN column /single column SET UNUSED COLUMNS（column1，column2） DROP COLUMN /single column DROP /multi column DROP UNUSED COLUMNS,（）列的添加、刪除、修改,添加列 語法 ALTER TABLE table_name ADD(new_column_name datatypeNOT NULLDEFAULT value); 示例 ALTER TABLE employee ADD(phone VARCHAR2(),hiredate DATE DEFAULT SYSDATE NOT NULL);,修改列類型 語法 ALTER TABLE table_name MODIFY column_name new_datatype; 修改表中列類型時，必須滿足下列條件： 可以增大字符類型列的長度和數(shù)值類型列的精度； 如果字符類型列、數(shù)值類型列中數(shù)據(jù)滿足新的長度、精度，則可以縮小類型的長度、精度； 如果不改變字符串的長度，可以將VARCHAR2類型和CAHR類型轉(zhuǎn)換； 如果更改數(shù)據(jù)類型為另一種非同系列類型，則列中數(shù)據(jù)必須為NULL。,示例 ALTER TABLE employee MODIFY ename CHAR(20); ALTER TABLE employee MODIFY phone NUMBER; 修改列名 語法 ALTER TABLE table_name RENAME COLUMN oldname TO newname; 示例 ALTER TABLE employee RENAME COLUMN ename TO employee_name；,刪除列 直接刪除列語法 ALTER TABLE table_name DROP COLUMN column_name| (column1_name,column2_name,) CASCADE CONSTRAINTS; 直接刪除列示例 ALTER TABLE sc DROP COLUMN sno CASCADE CONSTRAINTS; ALTER TABLE employee DROP (phone,hiredate);,將列標(biāo)記為UNUSED，然后進(jìn)行刪除。 ALTER TABLE table_name SET UNUSED COLUMN column_name| (column1_name,column2_name,) CASCADE CONSTRAINTS; 示例 ALTER TABLE player SET UNUSED COLUMN sage; ALTER TABLE player SET UNUSED (sname,resume); ALTER TABLE player DROP UNUSED COLUMNS;,如果數(shù)據(jù)庫表空間處于字典管理方式，可以對表的參數(shù)進(jìn)行修改，包括存儲參數(shù)、存儲位置、數(shù)據(jù)塊設(shè)置等。 ALTER TABLE employee PCTFREE 30 PCTUSED 60 STORAGE(NEXT 512K PCTINCREASE ); 注意 表創(chuàng)建后不能對INITIAL、MINEXTENTS兩個參數(shù)進(jìn)行修改。,（）表參數(shù)修改,將一個非分區(qū)的表移動到一個新的數(shù)據(jù)段中，或者移動到其他的表空間中，通過這種操作可以重建表的存儲結(jié)構(gòu)，稱為表結(jié)構(gòu)重組。 如果發(fā)現(xiàn)表的數(shù)據(jù)段具有不合理的區(qū)分配方式，但是又不能通過別的方法來進(jìn)行調(diào)整（改變存儲參數(shù)不會影響到已經(jīng)分配的區(qū)），可以考慮將表移動到一個新的數(shù)據(jù)段中。此外，如果頻繁地對表進(jìn)行DML操作，會產(chǎn)生大量空間碎片和行遷移、行連接，可以考慮進(jìn)行表結(jié)構(gòu)重組。,（）表結(jié)構(gòu)重組,語法 ALTER TABLE tbname MOVE TABLESPACE tbs_name 注意 直到表被完全移動到新的數(shù)據(jù)段中之后，Oracle才會刪除原來的數(shù)據(jù)段； 表結(jié)構(gòu)重組后，表中每個記錄的ROWID會發(fā)生變化，因此該表的所有索引失效，需要重新建立索引； 如果表中包含LOB列，則默認(rèn)情況下不移動LOB列數(shù)據(jù)和LOB索引段。,語法 ALTER TABLE old_name RENAME TO new_name; RENAME old_name TO new_name; 說明 表重命名后，Oracle會自動將舊表上的對象權(quán)限、約束條件等轉(zhuǎn)換到新表上，但是所有與舊表相關(guān)聯(lián)的對象都會失效，需要重新編譯。,（）表重命名,為表添加注釋 COMMENT ON TALBE table_name IS； 為列添加注釋 COMMENT ON COLUMN table_name.column_name IS 注釋可以通過以下數(shù)據(jù)字典來查看. ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS,（）為表和列添加注釋,語法 DROP TABLE table_name CASCADE CONSTRAINTSPURGE 刪除一個表同時，Oracle將執(zhí)行下列操作： 刪除該表中所有記錄； 從數(shù)據(jù)字典中刪除該表定義； 刪除與該表相關(guān)的所有索引和觸發(fā)器； 回收為該表分配的存儲空間； 依賴于該表的數(shù)據(jù)庫對象處于INVALID狀態(tài)。,.2.5 刪除表,注意 在Oracle 10g中，使用DROP TABLE語句刪除一個表時，并不立即回收該表的空間，而只是將表及其關(guān)聯(lián)對象的信息寫入一個稱為“回收站”（RECYCLEBIN）的邏輯容器中，從而可以實現(xiàn)閃回刪除表操作。如果要回收該表空間，可以采用清空“回收站”（PURGE RECYCLEBIN）或在DROP TABLE語句中使用PURGE語句。,.2.6利用OEM管理表,創(chuàng)建表 刪除表 修改表 查詢表,（1）創(chuàng)建表,（2）表的其他管理,.3 索引,索引概述 索引管理 利用OEM管理索引,.3.1 索引概述,索引概念及作用 索引是為了加速對表中元組的檢索而創(chuàng)建的一種分散存儲結(jié)構(gòu)； 是對表而建立的，由除存放表的數(shù)據(jù)頁面以外的索引頁面組成，獨立于被索引的表； 通過使用索引加速行的檢索，但減慢更新的速度； 快速定位數(shù)據(jù)，減少磁盤 I/O； Oracle自動使用、維護索引,索引分類 惟一性索引與非惟一性索引 平衡樹索引與位圖索引 單列索引與復(fù)合索引 函數(shù)索引 聚簇索引 全局索引與本地索引,索引使用原則 導(dǎo)入數(shù)據(jù)后再創(chuàng)建索引 在適當(dāng)?shù)谋砗妥侄紊蟿?chuàng)建索引 經(jīng)常查詢的記錄數(shù)目少于表中所有記錄總數(shù)的5%時就應(yīng)當(dāng)創(chuàng)建索引； 經(jīng)常進(jìn)行連接查詢表時，在連接列上建立索引能夠顯著提高查詢的速度； 對于取值范圍很大的列應(yīng)當(dāng)創(chuàng)建B樹索引； 對于取值范圍很小的列應(yīng)當(dāng)創(chuàng)建位圖索引； 不能在LONG，LONG RAW，LOB數(shù)據(jù)類型的列上創(chuàng)建索引； Oracle會自動在PRIMARY KEY和UNIQUE約束的列上創(chuàng)建唯一性索引。 合理設(shè)置復(fù)合索引中的字段順序,限制表中索引的數(shù)目 表中索引數(shù)目越多，查詢速度越快，但表的更新速度越慢。因為索引越多，維護索引所需開銷越大，當(dāng)更新表時，需要同時更新與表相關(guān)的所有索引。 為索引設(shè)置合適的PCTFREE參數(shù) 選擇存儲索引的表空間 默認(rèn)情況下，索引與表存儲在同一表空間中。索引與表存儲在同一表空間中，有利于數(shù)據(jù)庫維護操作，具有較高的可用性；反之，若索引與表存儲在不同的表空間中，則可提高系統(tǒng)的存取性能，減少硬盤I/O沖突，但是表與索引可用狀態(tài)可能出現(xiàn)不一致，如一個處于聯(lián)機狀態(tài)，另一個處于脫機狀態(tài)。,.3.2管理索引,創(chuàng)建索引 修改索引 監(jiān)視索引 刪除索引 索引的查詢,語法 CREATE UNIQUE|BITMAP INDEX index_name ON table_name(column_nameASC|DESC,| expression) REVERSE parameter_list； 說明 UNIQUE表示建立惟一性索引； BITMAP表示建立位圖索引； ASC/DESC用于指定索引值的排列順序，ASC表示按升序排序，DESC表示按降序排序，缺省值為ASC； REVERSE表示建立反鍵索引； parameter_list用于指定索引的存放位置、存儲空間分配和數(shù)據(jù)塊參數(shù)設(shè)置。,（1）創(chuàng)建索引,創(chuàng)建非惟一性索引 CREATE INDEX employee_ename ON employee(ename)TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75); 創(chuàng)建惟一性索引 CREATE UNIQUE INDEX deptartment_index ON department(dname); 創(chuàng)建位圖索引 CREATE BITMAP INDEX student_sex ON student(sex); 創(chuàng)建反序索引 CREATE INDEX player_sage ON player(sage) REVERSE;,創(chuàng)建函數(shù)索引 CREATE INDEX idx ON employee(UPPER(ename); 定義約束時創(chuàng)建索引 CREATE TABLE new_employee( empno NUMBER(5) PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0, ename VARCHAR2(20) );,（2）修改索引,合并索引 合并索引是對索引進(jìn)行合并操作，但只是簡單地將B樹葉節(jié)點中的存儲碎片合并在一起，并不會改變索引的物理組織結(jié)構(gòu) 語法 ALTER INDEXCOALESCE 示例 ALTER INDEX employee_ename COALESCE;,重建索引 重建索引的實質(zhì)是在指定的表空間中重新建立一個新的索引，然后再刪除原來的索引，這樣不僅能夠消除存儲碎片，還可以改變索引的存儲參數(shù)設(shè)置，并且將索引移動到其他的表空間中。 語法 ALTER INDEXREBUILD 示例 ALTER INDEX player_sage REBUILD;,合并索引與重建索引比較,索引重命名 語法 ALTER INDEXRENAME TO語句為索引重命名。 示例 ALTER INDEX employee_ename RENAME TO employee_new_ename;,（3）監(jiān)視索引,已經(jīng)建立的索引是否能夠有效地工作，取決于在查詢執(zhí)行過程中是否會使用到這個索引。要查看某個指定索引的使用情況，可以使用ALTER INDEX語句打開索引的監(jiān)視狀態(tài)。 打開索引監(jiān)視狀態(tài) ALTER INDEX index_ename MONITORING USAGE; 關(guān)閉索引監(jiān)視狀態(tài) ALTER INDEX index_ename NOMONITORING USAGE;,查看索引使用情況 通過V$OBJECT_USAGE動態(tài)性能視圖中查看索引的使用情況。USED列為YES，表示索引正被引用，否則為NO 。,（4）刪除索引,語法 DROP INDEX index_name; 在下面幾種情況下，可以考慮刪除索引 該索引不再使用。 通過一段時間監(jiān)視，發(fā)現(xiàn)幾乎沒有查詢或只有極少數(shù)查詢會使用該索引。 由于索引中包含損壞的數(shù)據(jù)塊或包含過多的存儲碎片等，需要刪除該索引，然后重建索引。 由于移動了表數(shù)據(jù)而導(dǎo)致索引失效,（5）索引的查詢,DBA_INDEXES、ALL_INDEXES、USER_INDEXES 包含索引的基本描述信息和統(tǒng)計信息，包括索引的所有者、索引的名稱、索引的類型、對應(yīng)表的名稱、索引的存儲參數(shù)設(shè)置、由分析得到的統(tǒng)計信息等信息 DBA_IND_COLUMNS、ALL_IND_COLUMNS、USER_IND_COLUMNS 包含索引列的描述信息，包括索引的名稱、表的名稱和索引列的名稱等信息 DBA_IND_EXPRESSIONS、ALL_IND_EXPRESSIONS、USER_IND_EXPRESSIONS 包含函數(shù)索引的描述信息，通過該視圖可以查看到函數(shù)索引的函數(shù)或表達(dá)式 V$OBJECT_USAGE 包含通過ALTER INDEXMONITORING USAGE語句對索引進(jìn)行監(jiān)視后得到的索引使用信息。,.3.3利用OEM管理索引,創(chuàng)建索引 修改索引 刪除索引 查詢索引,（1）創(chuàng)建索引,（2）索引的其他管理,.4 索引化表,索引化表概念 創(chuàng)建索引化表 設(shè)置溢出存儲 修改索引化表 利用OEM管理索引化表,索引化表概念,索引化表（Index-Organized Table，IOT）是一種特殊的表，它按B樹的索引結(jié)構(gòu)來組織和存儲數(shù)據(jù)，將表的數(shù)據(jù)和索引的數(shù)據(jù)存儲在一起。主要適合于經(jīng)常通過主鍵查詢整個記錄或部分記錄的情況，表中記錄的存放順序與主鍵的順序一致。 索引化表類似于B樹結(jié)構(gòu)，不過索引條目不是標(biāo)準(zhǔn)B樹結(jié)構(gòu)中索引值與ROWID這樣的結(jié)構(gòu)，而是主鍵列與非主鍵列形式的結(jié)構(gòu)。,標(biāo)準(zhǔn)表、索引結(jié)構(gòu)和索引化表結(jié)構(gòu)的比較,.4.1創(chuàng)建索引化表,說明 在CREATE TABLE語句中指定ORGANIZATION INDEX關(guān)鍵字 必須建立一個PRIMARY KEY 約束 示例 CREATE TABLE new_student( sno NUMBER PRIMARY KEY , sname CHAR(), sage NUMBER, sex CHAR(2) ORGANIZATION INDEX TABLESPACE USERS ;,可以通過子查詢創(chuàng)建索引化表 CREATE TABLE new_emp( empno PRIMARY KEY,ename) ORGANIZATION INDEX AS SELECT empno,ename FROM emp; 注意 利用子查詢創(chuàng)建索引化表時，必須指定主鍵列和其他所有列，指定的列的個數(shù)必須與查詢語句中目標(biāo)列個數(shù)一致。,.4.2 設(shè)置溢出存儲,所謂的溢出存儲是指將索引化表中每個記錄分成兩部分，其中一部分（包含主鍵列）保存在索引化表自身中（以索引的樹狀結(jié)構(gòu)存儲，稱為索引條目部分），而另一部分保存在溢出表空間中（以標(biāo)準(zhǔn)表的堆結(jié)構(gòu)存儲，稱為溢出部分）。 若要啟用溢出存儲功能，必須在創(chuàng)建索引化表時指定OVERFLOW子句指定溢出部分的存儲空間，同時使用PCTTHRESHOLD子句或INCLUDING子句來設(shè)置溢出方式。,用PCTTHRESHOLD設(shè)置溢出存儲 示例 CREATE TABLE iot_pct( ID NUMBER PRIMARY KEY, col1 VARCHAR2(20), col2 VARCHAR2(10), col3 NUMBER) ORGANIZATION INDEX TABLESPACE USERS PCTTHRESHOLD 30 OVERFLOW TABLESPACE ORCLTBS1;,使用PCTTHRESHOLD子句設(shè)置溢出時注意 溢出存儲是以列為單位，一個列要么全部保留在索引條目中，要么全部溢出； 列的順序主要取決于創(chuàng)建表時的列的定義順序，但是Oracle會自動將主鍵列移動到非主鍵列的前面，保證主鍵列保存在索引條目中； PCTTHRESHOLD子句所指定的比例必須包含索引化表中所有主鍵列。,用INCLUDING設(shè)置溢出存儲 INCLUDING子句顯式地指定需要保存在索引條目中的列，而該列之后的列都將溢出。 示例 CREATE TABLE iot_pct_include( ID NUMBER PRIMARY KEY, col1 VARCHAR2(20), col2 VARCHAR2(10), col3 NUMBER) ORGANIZATION INDEX TABLESPACE USERS PCTTHRESHOLD 30 INCLUDING col2 OVERFLOW TABLESPACE ORCLTBS1;,.4.3 修改索引化表,修改索引數(shù)據(jù)段與溢出數(shù)據(jù)段 重建索引化表 將索引化表轉(zhuǎn)換為普通表,（1）修改索引數(shù)據(jù)段與溢出數(shù)據(jù)段,使用ALTER TABLE語句可以對索引化表中索引數(shù)據(jù)段和溢出數(shù)據(jù)段進(jìn)行修改，包括存儲參數(shù)和溢出方式的修改。其中OVERFLOW關(guān)鍵字之前的參數(shù)都是針對索引條目數(shù)據(jù)段的，而OVERFLOW之后的參數(shù)都是針對溢出數(shù)據(jù)段的。 如果原來的索引化表中沒有使用溢出存儲功能，可以通過ALTER TABLEADD OVERFLOW為它應(yīng)用溢出存儲功能。,示例 ALTER TABLE new_student INITRANS 4 OVERFLOW INITRANS 6; ALTER TABLE iot_pct_include PCTTHRESHOLD 15 INCLUDING col2; ALT