《Oracle數(shù)據(jù)庫實(shí)用教程（第二版）》課件第06章 索引與視圖

索引與表和簇相關(guān)，用于縮短查詢數(shù)據(jù)的時(shí)間索引中存儲(chǔ)了記錄的關(guān)鍵字和ROWID考慮按索引的邏輯設(shè)計(jì)、物理實(shí)現(xiàn)分類建立索引索引的邏輯分類從應(yīng)用的角度劃分索引的物理分類從其物理存儲(chǔ)劃分索引在邏輯上和物理上都與相關(guān)表的數(shù)據(jù)無關(guān)索引需要單獨(dú)的存儲(chǔ)空間。否則使用表所在表空間創(chuàng)建或刪除一個(gè)索引不會(huì)影響基于數(shù)據(jù)表的數(shù)據(jù)庫應(yīng)用或其他索引創(chuàng)建索引后一般會(huì)增加數(shù)據(jù)更新操作的執(zhí)行時(shí)間6.1管理索引

Oracle數(shù)據(jù)庫的索引模式1.索引概述(1)B-樹索引(2)B-樹簇索引(3)散列簇索引(4)全局和本地索引(5)反序索引(6)位圖索引(7)基于函數(shù)的索引(8)域索引B-樹索引(B-treeindex)又稱平衡樹索引，是現(xiàn)代關(guān)系型數(shù)據(jù)庫中最為普遍采用的索引管理索引的準(zhǔn)則(1)在表中插入數(shù)據(jù)后創(chuàng)建索引(2)索引正確的表和列(3)為性能而安排索引列(4)限制每個(gè)表的索引的數(shù)量(5)刪除不再需要的索引(6)指定索引數(shù)據(jù)塊空間使用參數(shù)(7)估計(jì)索引大小并設(shè)置存儲(chǔ)參數(shù)(8)為每個(gè)索引指定表空間(9)考慮創(chuàng)建并行索引(10)考慮創(chuàng)建具有NOLOGGING的索引(11)考慮合并或重建索引時(shí)的損益(12)在停用或刪除索引之前考慮開銷1.索引概述如果滿足如下條件則需要?jiǎng)?chuàng)建索引(1)列有較大的取值范圍(2)列有很多空值，但經(jīng)常查詢所有具有值的行(3)一列或多列被經(jīng)常一起用于WHERE條件或連接條件(4)表很大，且大部分查詢只返回不到2%～4%的數(shù)據(jù)行1.索引概述具有如下一個(gè)或多個(gè)特征的列適合作索引列(1)列中的值相對(duì)比較唯一(2)取值范圍大(適合建常規(guī)索引)(3)取值范圍小(適合建位圖索引)(4)列中有許多空值，但經(jīng)常查詢所有具有值的行

LONG和LONGRAW列不能被索引創(chuàng)建索引的語法圖1.索引概述創(chuàng)建索引的簡要語法CREATE[UNIQUE|BITMAP]INDEX[schema.]indexON[schema.]table(column[ASC|DESC][,…])[INITRANSn] [MAXTRANSn] [PCTFREEn][LOGGING|NOLOGGING[NOSORT][STORAGEstorage][TABLESPACEtablespace];默認(rèn)情況下，索引中數(shù)據(jù)按升序(ASC)排列1.索引概述數(shù)據(jù)字典USER_INDEXESUSER_IND_COLUMNS例6.1為雇員表emp建立以ename為索引列的索引，索引名為emp_ename_index，索引存放到表空間my_index。EXA_06_01.SQL唯一索引可以保證在索引列上不會(huì)有兩行相同的值

CREATEUNIQUEINDEX語句2.創(chuàng)建唯一索引例6.2為部門表dept的dname列創(chuàng)建唯一索引。EXA_06_02.SQL例6.3唯一索引測試。EXA_06_03.SQL位圖索引適用于僅有幾個(gè)固定值的列3.創(chuàng)建位圖索引例6.4給student表的性別列創(chuàng)建位圖索引。EXA_06_04.SQL將整個(gè)位圖索引段裝入內(nèi)存，地提高了查找效率初始化參數(shù)CREATE_BITMAP_AREA_SIZE默認(rèn)值為8MB。創(chuàng)建位圖索引的限制(1)不能在全局分區(qū)索引上創(chuàng)建位圖索引(2)不能同時(shí)指定UNIQUE和位圖索引(3)不能在本地索引中使用位圖索引(4)不能用位圖索引來生成具有唯一性的索引和反序索引

CREATEBITMAPINDEX語句適用于經(jīng)常執(zhí)行以某種函數(shù)形式為條件查詢數(shù)據(jù)的應(yīng)用因函數(shù)值已計(jì)算，極大地改善了查詢的執(zhí)行性能基于函數(shù)的表達(dá)式索引列被稱為虛列4.創(chuàng)建基于函數(shù)的索引例6.5設(shè)student表的學(xué)生編號(hào)sno列的編碼規(guī)則為：4位年+2位系別+2位班級(jí)+2位順序號(hào)。為學(xué)號(hào)列按年級(jí)查詢學(xué)生的應(yīng)用創(chuàng)建一索引。EXA_06_05.SQL在較早版本中，用戶需具有如下之一系統(tǒng)特權(quán)QUERYREWRITECREATEANYINDEX索引默認(rèn)保存到與表相同的表空間在約束定義子句中，使用USINGINDEX子句為約束指定存儲(chǔ)空間5.設(shè)置與約束相關(guān)的索引例6.6為student表name列的唯一約束指定索引表空間EXA_06_05.SQL

USINGINDEX子句語法USINGINDEX[[schema.]index|(create_index_statement)|{LOCAL|global_partitioned_index}|{PCTFREEinteger|INITRANSinteger|MAXTRANSinteger|TABLESPACEtablespace|storage_clause|{SORT|NOSORT}|logging_clause}]必須為簇創(chuàng)建索引才能向簇表插入數(shù)據(jù)創(chuàng)建簇索引不需要指定索引列6.創(chuàng)建簇索引創(chuàng)建簇索引語法CREATEINDEXindex_nameONCLUSTER[schema.]cluster_nameindex_attributes;CREATEINDEXemp_dept_indexONCLUSTERemp_deptSTORAGE(INITIAL50KNEXT50KMINEXTENTS2MAXEXTENTS10PCTINCREASE0)TABLESPACEusersINITRANS2MAXTRANS5PCTFREE5;例:當(dāng)所有索引分區(qū)與表分區(qū)對(duì)應(yīng)時(shí)，稱為本地分區(qū)索引全局分區(qū)索引的分區(qū)列與表分區(qū)無關(guān)7.對(duì)索引創(chuàng)建分區(qū)例6.7建立本地索引，分區(qū)設(shè)置與創(chuàng)建表時(shí)一致EXA_06_07.SQL用LOCAL指明建立本地分區(qū)索引用GLOBAL指明建立全局分區(qū)索引通常全局分區(qū)索引比本地分區(qū)索引需要更多的維護(hù)開銷例6.8建立本地分區(qū)索引，省略分區(qū)設(shè)置

EXA_06_08.SQL例6.9分區(qū)名稱與表分區(qū)名不一致，但分區(qū)數(shù)量一樣

EXA_06_09.SQL例6.10建立本地分區(qū)索引，索引分區(qū)數(shù)量與表分區(qū)數(shù)量不一致

EXA_06_10.SQL例6.11建立全局分區(qū)索引

EXA_06_11.SQL導(dǎo)致刪除索引的主要原因(1)不再需要該索引(2)索引沒有提供所期望的性能改善(3)應(yīng)用程序沒有用該索引來查詢數(shù)據(jù)(4)該索引已經(jīng)變成無效(5)該索引已經(jīng)變得太碎8.索引的刪除與重建如何刪除索引，取決于索引的創(chuàng)建方式使用CREATEINDEX語句定義主鍵或唯一約束刪除索引語法DROPINDEXindex_name;重建索引語法ALTERINDEX[schema.]indexREBUILDONLINE;消除索引塊中那些數(shù)據(jù)記錄已經(jīng)刪除的無用索引項(xiàng)重新平衡索引樹9.索引的維護(hù)例6.12緊湊并重建tudent表sno列基于函數(shù)的索引EXA_06_12.SQL例6.13重建PK_EMP索引并指定新的數(shù)據(jù)塊空間使用參數(shù)EXA_06_13.SQL例6.14手工分配與回收索引空間。EXA_06_14.SQL10.索引使用的標(biāo)識(shí)例6.15查看scott用戶的PK_EMP索引的使用情況。EXA_06_15.SQL索引使用的標(biāo)識(shí)開啟對(duì)索引使用的監(jiān)視ALTERINDEXindexMONITORINGUSAGE;查看動(dòng)態(tài)性能監(jiān)視視圖v$object_usage對(duì)索引使用情況的監(jiān)視會(huì)降低數(shù)據(jù)庫系統(tǒng)的性能關(guān)閉對(duì)索引的監(jiān)視ALTERINDEX…NOMONITORINGUSAGE;視圖是一個(gè)基于基表和(或)其他視圖的邏輯表視圖提供了從某個(gè)角度觀察數(shù)據(jù)的窗口6.2視圖的創(chuàng)建與使用視圖的作用具體表現(xiàn)為(1)限制用戶對(duì)數(shù)據(jù)的訪問(2)使復(fù)雜查詢對(duì)報(bào)表等編程人員變得容易使用(3)為應(yīng)用提供數(shù)據(jù)獨(dú)立性(4)為相同的數(shù)據(jù)提供不同的視角創(chuàng)建視圖的子查詢不能使用序列，不可選取currval、nextval偽列只有加別名才能使用rowid、rownum、level等偽列創(chuàng)建視圖的子查詢一般不應(yīng)包含ORDERBY子句最好不要在視圖上再建視圖用DESCRIBE命令查看視圖的結(jié)構(gòu)1.創(chuàng)建與刪除視圖創(chuàng)建視圖語法CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias,alias…)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];(1)子查詢中的表達(dá)式必須有別名(2)FORCE：強(qiáng)制創(chuàng)建該視圖(3)WITHCHECKOPTION：DML語句操作的數(shù)據(jù)應(yīng)滿足視圖定義時(shí)的條件(4)WITHREADONLY：視圖只讀(5)CREATE子句指定的列名優(yōu)先于在subquery中指定的列名1.創(chuàng)建與刪除視圖例6.16基于dept表創(chuàng)建一只讀視圖。EXA_06_16.SQL數(shù)據(jù)字典USER_VIEWS刪除視圖語法DROPVIEW[schema.]view[CASCADECONSTRAINTS];重建視圖帶上ORREPLACE選項(xiàng)，語法如下：CREATEORREPLACEVIEWview……;例6.17為表emp建立視圖v_emp_d20，只顯示20號(hào)部門的雇員和他們的年收入。EXA_06_17.SQL2.對(duì)視圖執(zhí)行DML操作的限制可以對(duì)簡單視圖(行列子集視圖)執(zhí)行DML操作

子查詢包含如下內(nèi)容，則不能通過它刪除基表數(shù)據(jù)(1)分組函數(shù)(2)GROUPBY子句(3)DISTINCT關(guān)鍵字(4)ROWNUM偽列子查詢包含如下內(nèi)容，則不能通過它修改基表數(shù)據(jù)(1)分組函數(shù)(2)GROUPBY子句(3)DISTINCT關(guān)鍵字(4)ROWNUM偽列(5)計(jì)算列子查詢包含如下內(nèi)容，則不能通過它增加基表數(shù)據(jù)(1)