《Oracle大型數(shù)據(jù)庫基礎(chǔ)開發(fā)教程》（微課視頻版）課件 第6章 索引與視圖

第6章索引與視圖6.1索引一、索引概述1.索引的概念索引是將每一條記錄在某個（或某些）屬性上的取值與該記錄的（在數(shù)據(jù)文件中）物理地址直接聯(lián)系起來。一種根據(jù)記錄屬性值快速訪問文件記錄的機制。2.建立索引的優(yōu)缺點為表創(chuàng)建索引有許多優(yōu)點，如創(chuàng)建唯一索引后可以保證每行數(shù)據(jù)的唯一性；可以加速檢索數(shù)據(jù)的速度；多表查詢時，可以加速表之間的連接；明顯減少分組和排序的時間等。建立索引也有缺點，如索引需占據(jù)存儲空間。例如，創(chuàng)建一個聚集索引大約需要1.2倍于數(shù)據(jù)大小的空間；創(chuàng)建索引和維護索引需要一定的時間；數(shù)據(jù)更新時索引也需要更新，降低了系統(tǒng)效率。6.1索引3.建立索引的原則對于經(jīng)常需要進行查詢、連接、統(tǒng)計操作，且數(shù)據(jù)量大的基本表可考慮建立索引。例如,在主鍵字段或經(jīng)常出現(xiàn)在WHERE子句或連接條件中的建立建立索引；取值范圍較大的字段；NULL值比較多的字段；經(jīng)常需要排序的字段。而對于經(jīng)常執(zhí)行插入、刪除、更新操作或小數(shù)據(jù)量的基本表應(yīng)盡量避免建立索引。二、Oracle索引分類1.按邏輯設(shè)計分類（1）單字段簡單索引與多字段復(fù)合索引單字段簡單索引是被索引字段是一個字段的索引。多字段復(fù)合索引是被索引字段是多個字段的索引。6.1索引（2）唯一索引與非唯一索引唯一索引是被索引字段不能存在重復(fù)值，也就是字段中不會有兩行相同的索引鍵值。非唯一索引是被索引字段可以存在重復(fù)值。（3）分區(qū)索引是指索引可以分散在多個不同的表空間中?？梢蕴岣邤?shù)據(jù)的查詢效率。（4）正向索引與反向索引正向索引是創(chuàng)建索引時不必指定對其排序而使用默認的順序。反向索引是索引同樣保持索引字段按順序排列，但是顛倒已索引的每個字段的字節(jié)。適用于Oracle實時應(yīng)用集群。（5）基于表達式的索引指索引中的一個字段或者多個字段是一個函數(shù)或者表達式，索引根據(jù)函數(shù)或者表達式計算索引列的值。6.1索引2.按物理實現(xiàn)分類（1）B*樹索引B*索引的存儲結(jié)構(gòu)類似于書的索引結(jié)構(gòu)，有“分支”和“頁”兩種類型的存儲數(shù)據(jù)塊，分支塊相當(dāng)于書的大目錄，葉塊相當(dāng)于索引到的具體的書頁，這種方式可以保證用最短路徑訪問數(shù)據(jù)。常見的惟一索引與非惟一索引、單字段簡單索引與多字段復(fù)合索引均屬于此類。（2）反向鍵索引反向鍵索引通過簡單的反向被索引的字段中的數(shù)據(jù)來解決問題，首先反向每個字段鍵值的字節(jié)，然后在反向后的新數(shù)據(jù)上進行索引，而新數(shù)據(jù)在值的范圍上的分布通常比原來的有序數(shù)更均勻。因此，反向鍵索引通常建立在一些值連續(xù)增長的字段上。6.1索引（3）位圖索引位圖索引主要針對大量相同值的字段而創(chuàng)建的，位圖索引適用于具有很少字段值的列（也叫低基數(shù)列）。位圖索引存儲主要用于節(jié)省空間，減少Oracle對數(shù)據(jù)塊的訪問，它為索引字段的每個取值建立一個位圖。在這個位圖中，為表中每一行使用一個位元（bit，取值為1或0）來表示該行是否包含該位圖的索引列的值。三、索引管理1.創(chuàng)建索引創(chuàng)建索引的一般語法格式如下所示：CREATE[UNIQUE|BITMAP]INDEX[schema.]index_nameON[schema.]table_name(column_name[ASC|DESC],…n)6.1索引UNIQUE代表創(chuàng)建唯一索引，不指明為創(chuàng)建非唯一索引。BITMAP代表創(chuàng)建位圖索引，如果不指明該參數(shù)，則創(chuàng)建B*樹索引。字段名是創(chuàng)建索引的關(guān)鍵字段，可以是一個字段或多個字段。默認升序:ASC，降序：DESC例6.1在EMP表上的ENAME字段上創(chuàng)建索引ENAME_INDEX。CREATEINDEXENAME_INDEXONEMP(ENAMEDESC);例6.2在EMP表的JOB和MGR字段上創(chuàng)建復(fù)合索引JOBMGR_INDEX。CREATEINDEXJOBMGR_INDEXONEMP(JOB,MGR);6.1索引2.查看索引Oracle數(shù)據(jù)庫中，索引信息存放在數(shù)據(jù)字典中，如DBA_INDEXES、USER_INDEXES、USER_IND_COLUMNS等，分別描述有關(guān)索引信息和創(chuàng)建索引的列信息。使用SELECT命令和DESC均可查看索引的信息。例：查看EMP表的索引是否已經(jīng)創(chuàng)建。SELECTindex_nameFROMUSER_INDEXESWHERETABLE_NAME='EMP';或：DESCDBA_INDEXES;例：查看EMP表的索引信息。SELECTindex_name,table_name,uniqueness,statusFROMUSER_INDEXESWHERETABLE_NAME='EMP';6.1索引3.刪除索引刪除索引的語法是：DDROPINDEX索引名;索引的刪除對表沒有影響。例：刪除EMP表的索引ENAME_INDEX。DROPINDEXENAME_INDEX;四、索引使用舉例可以通過ANALYZETABLE、SETAUTOTRACEONEXPLAIN命令來進行查看。ANALYZETABLE是統(tǒng)計索引分布信息，命令格式如下所示：ANALYZETABLEtable_nameCOMPUTESTATISTICS;SETAUTOTRACEONEXPLAIN是顯示優(yōu)化器執(zhí)行路徑報告，命令格式如下所示：SETAUTOTRACEONEXPLAIN;6.1索引1.無索引（1）統(tǒng)計索引分布信息ANALYZETABLEEMPCOMPUTESTATISTICS;（2）顯示優(yōu)化器執(zhí)行路徑報告SETAUTOTRACEONEXPLAIN;（3）查看索引的引用SELECT*FROMEMPWHEREENAME='SCOTT';6.1索引2.有索引（1）創(chuàng)建索引根據(jù)EMP表的ENAME字段建立一個索引ENAME_INDEX，命令如下：CREATEINDEXENAME_INDEXONEMP(ENAMEDESC);（2）統(tǒng)計索引分布信息ANALYZETABLEEMPCOMPUTESTATISTICS;（3）顯示優(yōu)化器執(zhí)行路徑報告SETAUTOTRACEONEXPLAIN;（4）查看索引的引用SELECT*FROMEMPWHEREENAME='SCOTT';6.1索引注意事項：對于Oracle系統(tǒng)來講，并不是創(chuàng)建了索引就一定會使用索引，當(dāng)Oracle自動搜集了表和索引的統(tǒng)計信息之后，才會確定是否要使用索引，只有高選擇性的索引才會比全表掃描更有效率。系統(tǒng)要估算查詢結(jié)果的元組數(shù)目如果比例較小(<10%)可以使用索引掃描方法，否則還是使用全表掃描。6.2視圖一、視圖概述1.視圖的概念視圖也稱為虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表。視圖不同于基本表，本身不包含任何數(shù)據(jù)。基本表是實際獨立存在的實體，是用于存儲數(shù)據(jù)的基本結(jié)構(gòu)。而視圖只是一種定義，數(shù)據(jù)庫中只存放視圖的定義，對應(yīng)一個查詢語句。用戶可以像使用基本表一樣對視圖執(zhí)行各種DML操作，如SELECT、INSERT、UPDATE、DELETE。視圖內(nèi)沒有存儲任何數(shù)據(jù)，對視圖中數(shù)據(jù)的操縱實際上是對組成視圖的基本表的操縱。當(dāng)基本表中的數(shù)據(jù)發(fā)生變化時，視圖的查詢結(jié)果也會發(fā)生變化。6.2視圖2.視圖的優(yōu)點（1）提供附加的安全層通過視圖往往只可以訪問數(shù)據(jù)庫中表的特定部分，限制了用戶訪問表的全部行和字段。（2）簡化了查詢語句，隱藏了查詢的復(fù)雜性。視圖的數(shù)據(jù)來自一個復(fù)雜的查詢，用戶對視圖的檢索卻很簡單。（3）集中用戶使用的數(shù)據(jù)一個視圖可以檢索多張表的數(shù)據(jù)，因此用戶通過訪問一個視圖，可完成對多個表的訪問。（4）簡化用戶權(quán)限的管理視圖是相同數(shù)據(jù)的不同表示，通過為不同的用戶創(chuàng)建同一個表的不同視圖，使用戶可分別訪問同一個表的不同部分。6.2視圖（5）利用視圖可以更清晰的表達查詢在進行某一個查詢時，使用視圖使得查詢更加清晰。例如，經(jīng)常需要執(zhí)行這樣的查詢：查詢每個部門中獲得最高工資的雇員姓名?？梢韵榷x一個視圖：每個部門的部門號和最高工資，假設(shè)為DEPTMAXSAL(DEPTNO,MAXSAL)。然后執(zhí)行如下查詢：SELECTENAMEFROMEMP,DEPTMAXSALWHEREEMP.DEPTNO=DEPTMAXSAL.DEPTNOANDSAL=DEPTMAXSAL.MAXSAL;6.2視圖二、創(chuàng)建視圖創(chuàng)建視圖的語法格式如下：CREATE[ORREPLACE][FORCE|NOFORCE]VIEW[schema.]view_name[(view_column1,view_column2,…)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint_name]][WITHREADONLY];各子句含義如下：ORREPLACE表示替代已經(jīng)存在的視圖。FORCE表示不管基表是否存在，創(chuàng)建視圖。NOFORCE表示只有基表存在時，才創(chuàng)建視圖，是默認值。6.2視圖view_column1，view_column2是為子查詢中選中的字段新定義的名字，替代子查詢subquery中原有的字段名。subquery是一個用于定義視圖的SELECT查詢語句，可以包含連接、分組及子查詢。WITHCHECKOPTION表示進行視圖插入或修改時必須滿足子查詢的約束條件。后面的約束名是該約束條件的名字。WITHREADONLY表示視圖是只讀的，不能進行插入、刪除、修改等操作。1.創(chuàng)建簡單視圖簡單視圖是從單個基本表中導(dǎo)出數(shù)據(jù)，不包含字符或組合之類函數(shù)。只是去掉了基本表的某些行和某些列，不包含函數(shù)、分組等，可以直接進行DML操作。例：創(chuàng)建所有雇員的編號和雇員姓名的視圖EMP_VIEW。CREATEVIEWEMP_VIEWASSELECTEMPNO,ENAMEFROMEMP;6.2視圖或：CREATEVIEWEMP_VIEW(雇員編號,雇員姓名)ASSELECTEMPNO,ENAMEFROMEMP;簡單視圖的視圖字段名是可以省略，也可以自己指定。如果未指定則默認子查詢中的字段名為視圖的字段名。2.創(chuàng)建復(fù)雜視圖復(fù)雜視圖是從一個或多個表中導(dǎo)出數(shù)據(jù)，可包含連接、分組、表達式或集函數(shù)等。例：創(chuàng)建每個部門的部門號和平均工資視圖DEPTAVGSAL。CREATEVIEWDEPTAVGSAL(DEPTNO,AVGSAL)ASSELECTDEPTNO,AVG(SAL)FROMEMPGROUPBYDEPTNO;6.2視圖EPTAVGSAL視圖中的視圖字段名能否省略？答案是不能缺省的。原因是子查詢中使用了集函數(shù)AVG()。如果要省略可以在子查詢中使用字段別名，命令如下所示：CREATEVIEWDEPTAVGSALASSELECTDEPTNO,AVG(SAL)AVGSALFROMEMPGROUPBYDEPTNO;下列三種情況下必須指定視圖字段名。（1）某個目標字段不是單純的屬性名，而是聚集函數(shù)或字段表達式。（2）多表連接時選出了幾個同名字段作為視圖的字段。例：CREATEVIEWEMP(EMPNO，EMP#，ENAME)AS

SELECTEMP.EMPNO,EMP.EMPNO,ENAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO;6.2視圖（3）需要在視圖中為某個字段啟用新的更合適的名字。3.創(chuàng)建帶檢查約束的視圖創(chuàng)建帶檢查約束的視圖需要指定子句WITHCHECKOPTION。例：建立一個部門雇員工資大于3000的雇員信息視圖SAL_3000，指定WITHCHECKOPTION子句。CREATEVIEWSAL_3000ASSELECT*FROMEMPWHERESAL>3000WITHCHECKOPTION;指定WITHCHECKOPTION選項后，如果在此視圖上執(zhí)行增加和修改操作，要求新數(shù)據(jù)必須符合指定的約束條件SAL>3000，否則禁止執(zhí)行。6.2視圖4.創(chuàng)建只讀視圖創(chuàng)建只讀視圖需要指定子句WITHREADONLY。例：CREATEVIEWSAL_3000ASSELECT*FROMEMP;WHERESAL>3000WITHREADONLY;指定子句WITHREADONLY用戶在此只讀視圖上只可以執(zhí)行查詢操作，禁止增加、刪除和更改等操作。6.2視圖三、查詢視圖1.查詢視圖定義當(dāng)前用戶的視圖定義放在數(shù)據(jù)字典USER_VIEWS中，查看命令如下：SELECTVIEW_NAME,TEXTFROMUSER_VIEWS2.查看視圖的結(jié)構(gòu)查看視圖定義可以使用DESCRIBE命令,其命令格式如下：DESCRIBE視圖名例：查看視圖SAL_3000的結(jié)構(gòu)。DESCRIBESAL_3000;6.2視圖3.查詢視圖中數(shù)據(jù)視圖可以像基本表一樣被查詢，查詢數(shù)據(jù)使用SELECT語句。由于視圖中沒有數(shù)據(jù)，所以實質(zhì)上是查詢的基本表中的數(shù)據(jù)，是視圖中的子查詢和查詢視圖的命令相結(jié)合轉(zhuǎn)換成一個新的SELECT語句對視圖基于的基本表進行查詢，這個過程為視圖消解。例：建立一個部門雇員工資大于3000的雇員信息視圖SAL_3000CREATEVIEWSAL_3000ASSELECT*FROMEMPWHERESAL>3000;例：查詢視圖SAL_3000中部門為10的雇員信息。SELECT*FROMSAL_3000WHEREDEPTNO=10;實際上查詢視圖SAL_3000的語句最后會轉(zhuǎn)換成如下查詢語句：SELECT*FROMEMPWHERESAL>3000ANDDEPTNO=10;6.2視圖四、更新視圖視圖的更新操作是指對視圖的INSERT、DELETE、UPDATE等操作。由于視圖中沒有數(shù)據(jù)，所以實質(zhì)上是更新的基本表中的數(shù)據(jù)，是視圖中的子查詢和更新視圖的命令相結(jié)合轉(zhuǎn)換成一個新的更新語句對視圖基于的基本表進行更新。在簡單視圖中可以進行更新操作。但在復(fù)雜視圖上進行更細操作有一系限制，限制如下：連接視圖中不能有ORDERBY排序語句。表中所有的NOTNULL字段都必須在這個視圖中。需要更新的字段不包含偽列或表達式。建立視圖的SELECT語句中，不能包含集合運算符、連接運算符、子查詢、集函數(shù)和GROUPBY子句等。6.2視圖例：把視圖SAL_3000中雇員編號為7839的雇員工資加1000。UPDATESAL_3000SETSAL=SAL+1000WHEREEMPNO=7839;例：把視圖DEPTAVGSAL中所有人的平均工資加1000;CREATEVIEWDEPTAVGSAL(DEPTNO,AVGSAL)ASSELECTDEPTNO,AVG(SAL)FROMEMPGROUPBYDEPTNO;6.2視圖更新命令如下：UPDATEDEPTAVGSALSETAVGSAL=AVGSAL+1000;執(zhí)行時會顯示如下錯誤：第1行出現(xiàn)錯誤:ORA-01732:此視圖的數(shù)據(jù)操縱操作非法。因此一般情況下只能對簡單視圖更新，其復(fù)雜的視圖一般不能更新。例：創(chuàng)建每個部門的部門名稱、雇員編號、雇員姓名、工資視圖DEPTEMP_VIEW，把視圖中7788雇員的工資加500，把其所在的部門名稱改為’開發(fā)部’。6.2視圖CREATEVIEWDEPTEMP_VIEW