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

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

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