目錄： Oracle數(shù)據(jù)完整性和鎖機(jī)制 索引及優(yōu)化之表分析 表分析、約束及表間關(guān)系 Oracle體系結(jié)構(gòu)1 Oracle體系結(jié)構(gòu)2 海量數(shù)據(jù)庫及分區(qū)1 海量數(shù)據(jù)庫及分區(qū)2 海量數(shù)據(jù)庫及分區(qū)3 海量數(shù)據(jù)庫及分區(qū)4 高級(jí)SQL優(yōu)化(一) 高級(jí)SQL優(yōu)化(二) 高級(jí)SQL優(yōu)化(三) 常用優(yōu)化工具 PPT和源碼下載： http:///forum/posts/list/6365.html 配套視頻課程 Oracle性能優(yōu)化 http:///product/601 海量數(shù)據(jù)庫和高級(jí)SQL優(yōu)化 http:///product/602 本課內(nèi)容屬于Oracle性能調(diào)優(yōu)部分的初級(jí)內(nèi)容，為常規(guī)的工作內(nèi)容，包括 索引的使用和 表分析。  環(huán)境準(zhǔn)備 概述 針對(duì)前面章節(jié)中用到的數(shù)據(jù)庫表teachers： 1.建立一個(gè)1:1的表記錄其銀行賬戶記錄表； 2.再建立一個(gè)1:n的賬戶交易信息表，用來記錄用戶從賬戶中存錢和取錢的交易記錄。 3.編寫一個(gè)程序，生成該交易表的記錄信息，要求至少要達(dá)到100萬以上記錄。 表“教師賬戶信息表（TEACHER_ACCOUNT）” 表“賬戶交易表（ACCOUNT_TRADE）” 存儲(chǔ)過程“P_INIT_ACCOUNT_TRADE” 使用PD設(shè)計(jì)表及初始化賬戶數(shù)據(jù) sql代碼：-
- delete from teacher_account;
- insert into teacher_account(jsbh,yhkh,dqye) values('001','6002780207013762981',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('002','6002780207013762923',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('003','6002780207013760181',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('004','6002780207013739826',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('005','6002780207313762981',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('006','6002780207413762923',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('007','6002780207513760181',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('008','6002780207613739826',15000.00);
- insert into teacher_account(jsbh,yhkh,dqye) values('009','6212780207013762981',25000.00);
存儲(chǔ)空間估算及表空間大小調(diào)整 1.數(shù)據(jù)類型所占最大存儲(chǔ)容量表 類型 | 字節(jié)數(shù) | 平均字節(jié)數(shù) | CHAR(n) | n | n | VARCHAR(n) | n | 已經(jīng)存在數(shù)據(jù)的表，可以通過統(tǒng)計(jì)得出； 剛設(shè)計(jì)的表，一般取值在30%-60%，建議40% | DATE | 7 | 7 | NUMBER(p,s) | 最低(p+1)/2+1 | 一般采用最低 | NUMBER | 19 | |
2.估算結(jié)果表(ACCOUNT_TRADE) 列名 | 類型 | 最大 大小 | 平均 大小 | 列名 | 類型 | 最大 大小 | 平均 大小 | YHKH | CHAR(19) | 19 | 19 | ND | INTEGER | 19 | 4 | JYSJ | CHAR(19) | 19 | 19 | YD | CHAR(2) | 2 | 2 | JYJY | NUMBER(16,2) | 9 | 9 | RQ | CHAR(2) | 2 | 2 | JYQYE | NUMBER(16,2) | 9 | 9 | XSS | CHAR(2) | 2 | 2 | JYHYE | NUMBER(16,2) | 9 | 9 | FZS | CHAR(2) | 2 | 2 | XM | VARCHAR2(10) | 10 | 4 | MS | CHAR(2) | 2 | 2 | XB | CHAR(1) | 1 | 1 | DRJYCS | INTEGER | 19 | 8 | XL | VARCHAR2(10) | 10 | 4 | XRSJ | DATE | 7 | 7 | SSXB | CHAR(3) | 3 | 3 | | | | | 每行最大大?。?44 bytes | 每行平均大?。?04 bytes |
3.表空間容量估算 按最大容量估算，100萬行記錄存儲(chǔ)空間為： (144*1000000)/(1024*1024) ≈ 140M 按平均容量估算，100萬行記錄存儲(chǔ)空間為： (106*1000000)/(1024*1024) ≈ 100M 考慮建立索引占存儲(chǔ)容量的20%(此為最大)： 最大為 30M，平均為20M 按200萬行最大記錄算，考慮還要備份表，則為： (140+30) * 2 * 2 = 680M ≈ 700M 1.請(qǐng)通過OEM調(diào)整用戶默認(rèn)表空間和索引表空間大小分別為500M和200M 2.調(diào)整方法本處不再贅述 3.從上一頁表格可以看出，存儲(chǔ)尚有優(yōu)化空間，請(qǐng)各位思考哪兒可以再優(yōu)化？ 交易數(shù)據(jù)準(zhǔn)備 方法一：通過程序生成： 1).假定24個(gè)老師，共22年每天產(chǎn)生10次交易，則交易數(shù)據(jù)量為： 24*22*36*10 = 1,927,200萬行； 2).每日交易的時(shí)間和金額隨機(jī)生成，但日存錢的最大金額為10萬， 取錢的最大金額為余額 – 1；至于存錢還是取錢也是隨機(jī)生 成； 3).由于一次寫入百萬行級(jí)的數(shù)據(jù)，會(huì)產(chǎn)生很大的回滾段，易造成 數(shù)據(jù)庫崩潰，故每寫完2000行記錄提交一次； 4).附件的程序故意留有取錢不能超過余額的BUG，請(qǐng)學(xué)有余力的 學(xué)員修改之； 5).警告：根據(jù)Oracle服務(wù)器性能和資源不同，此程序的執(zhí)行可能 需要長達(dá)3天以上的時(shí)間才能完成！ 方法二：導(dǎo)入數(shù)據(jù)： 1).在數(shù)據(jù)庫中刪除表ACCOUNT_TRADE； 2).將日志模式調(diào)整為非歸檔模式，方法參考前面的課程，原因是： a.一次導(dǎo)入的數(shù)據(jù)量非常大，同樣可能產(chǎn)生重做日志和回滾段空 間不足導(dǎo)致的數(shù)據(jù)庫崩潰問題；如果不調(diào)整，需要修改系統(tǒng)參 數(shù) db_recovery_file_dest_size參數(shù)值，一般是加大到至少要 4G，從而要求更多的磁盤空間； b.此時(shí)歸檔日志模式導(dǎo)入會(huì)比非歸檔日志模式導(dǎo)入慢很多。 3).使用IMP命令導(dǎo)入數(shù)據(jù)，方法也請(qǐng)參考前面的課程內(nèi)容。 為什么需要索引(優(yōu)點(diǎn)和缺點(diǎn))  索引的概念 在關(guān)系數(shù)據(jù)庫中，行存儲(chǔ)的物理位置是無關(guān)緊要的，但當(dāng)需要找到該行時(shí) 行位置卻又是至關(guān)重要的。Oracle對(duì)每一行數(shù)據(jù)使用一個(gè)RowID來標(biāo)示，其中存 儲(chǔ)了行的準(zhǔn)確位置（行所在的文件、在該文件中的塊、以及塊中的行地址）。 索引是一種提供在表中快速定位某一行數(shù)據(jù)的一種Oracle內(nèi)部結(jié)構(gòu)。 索引是Oracle內(nèi)部的一種數(shù)據(jù)結(jié)構(gòu)，其中存放多行 數(shù)據(jù)（包括 一個(gè)索引的列的拷貝和在 被索引的表的相應(yīng)的RowID ），可以理解為Oracle表的一個(gè)小型化拷貝； 索引的目的是為了加快數(shù)據(jù)檢索速度。 索引的創(chuàng)建、修改和刪除 1.索引的創(chuàng)建 CREATE [UNIQUE | BITMAP] INDEX index_name ON table_name ( column_name[ ASC | DESC][,c olumn_name [ASC | DESC]] … ) [CLUSTER cluster_name ] [INITRANS n] [MAXTRANS n] [PCTFREE n] [STORGE storage] [TABLESPACE t ablespace_name ] [NO sort] l 其中UNIQUE指定索引所基于的列(或多列)值必須唯一； l 默認(rèn)的索引是非唯一索引; l BITMAP指定建立位圖索引而不是b-tree索引； l index_name表示創(chuàng)建的索引名字； l table_name指要?jiǎng)?chuàng)建索引的表； l cluster_name指創(chuàng)建索引的簇； l n可以為任意正整數(shù)值； l tablespace_name表示要用于該索引的表空間; l No sort告訴Oracle該表已經(jīng)排序因此不需要再重新排序。 必須具有CREATE ANY INDEX系統(tǒng)權(quán)限 存儲(chǔ)參數(shù)選項(xiàng)建議使用默認(rèn)，不要隨便修改或征求DBA的意見 l 除使用語句、CASE工具外，還可以使用OEM創(chuàng)建索引 l 一般建議使用PD創(chuàng)建索引 l 具體的舉例請(qǐng)參考老師的現(xiàn)場(chǎng)講解 2.索引的修改 常用語法為： ALTER INDEX index_name REBUILD; ALTER INDEX index_name REBUILD ONLINE; l 此語句代表對(duì)某個(gè)索引進(jìn)行重構(gòu)，必須具有ALTER ANY INDEX系統(tǒng)權(quán)限 l 不帶ONLINE表示只掃描現(xiàn)有的索引塊來實(shí)現(xiàn)索引的重建 l 帶ONLINE表示掃描表而不是掃描現(xiàn)有的索引塊來實(shí)現(xiàn)索引的重建 l 一般的， 推薦 每隔一個(gè)長時(shí)期對(duì)索引進(jìn)行重建，即先刪除后建立;在此長時(shí)期范圍內(nèi)， 可以多次進(jìn)行索引重構(gòu) l 修改索引建議使用語句，此為最快的途徑，而不是OEM工具 3.索引的刪除 常用語法為： DROP INDEX index_name ; l 此語句代表對(duì)某個(gè)索引進(jìn)行刪除，刪除后則相應(yīng)的索引存儲(chǔ)空間會(huì)被釋放 l 刪除索引必須有DROP ANY INDEX系統(tǒng)權(quán)限 l 一般建議使用語句刪除索引，當(dāng)然也可以使用PL/SQL Developer、Toad、OEM等 工具，但顯然語句語法簡單，操作效率也更高 索引的類型 1.b-tree索引 b-tree索引，即平衡樹索引（balanced tree）,是最常見的一種數(shù)據(jù)索引形式，是通過采用 帶有值的順序的列表范圍來組織數(shù)據(jù)。其中包括三種組件： 1.葉子節(jié)點(diǎn)（Leaf node）:包括數(shù)據(jù)行的鍵值、鍵值對(duì)應(yīng)數(shù)據(jù)行的 ROWID。 2.分支節(jié)點(diǎn)（Branch node）:最小的鍵值前綴，用于在（本塊的）兩個(gè)鍵值之間做出分支選擇，指向包含所查找鍵值的子塊的指針?biāo)械逆I值-ROWID 對(duì)都與其左右的兄弟節(jié)點(diǎn)向鏈接，并按照的順序排序。 3.根節(jié)點(diǎn)（Root node）:一個(gè)B樹索引只有一個(gè)根節(jié)點(diǎn)，它實(shí)際就是位于樹的最頂端的分支節(jié)點(diǎn)。 (1).兩種節(jié)點(diǎn)（塊） A.分節(jié)點(diǎn)用來搜索，葉子節(jié)點(diǎn)用來存儲(chǔ)數(shù)據(jù)。根節(jié)點(diǎn)存儲(chǔ)索引的低層分支節(jié)點(diǎn)的數(shù)據(jù)。 由于所有的葉子節(jié)點(diǎn)均會(huì)自動(dòng)的存儲(chǔ)成相同的深度，所以稱為“平 衡樹索引”， 故此，從任何葉子處檢索數(shù)據(jù)消耗的時(shí)間都是相同的。 B.平衡樹的高度，指的是從根節(jié)點(diǎn)都葉子節(jié)點(diǎn)所經(jīng)過的節(jié)點(diǎn)數(shù)；分支節(jié)點(diǎn)的高度等于平衡樹的高度減1。 C.對(duì)于分支節(jié)點(diǎn)塊（包括根節(jié)點(diǎn)塊）來說，其所包含的索引條目都是按照順序排列的（缺省是升序排列，也可以在創(chuàng)建索引時(shí)指定為降序排列）。每個(gè)索引條目（也可以叫做每條記錄）都具有兩個(gè)字段。第一個(gè)字段表示當(dāng)前該分支節(jié)點(diǎn)塊下面所鏈接的索引塊中所包含的最小鍵值；第二個(gè)字段為四個(gè)字節(jié)，表示所鏈接的索引塊的地址，該地址指向下面一個(gè)索引塊。 在一個(gè)分支節(jié)點(diǎn)塊中 所能容納的記錄 行數(shù)由數(shù)據(jù)塊大小以及索引鍵值的長度決定。例如上圖的根節(jié)點(diǎn)解釋如下：  根節(jié)點(diǎn)包含5個(gè)索引條目(記錄），0、201…801為這5個(gè)分支節(jié)點(diǎn)所鏈接的節(jié)點(diǎn)的最小值，B1、 B2…B5為5個(gè)分支節(jié)點(diǎn)的地址。 D.對(duì)于葉子節(jié)點(diǎn)塊來說，其所包含的索引條目與分支節(jié)點(diǎn)一樣，都是按照順序排列的（缺省是升序排列，也可以在創(chuàng)建索引時(shí)指定為降序排列）。每個(gè)索引條目（也可以叫做每條記錄）也具有兩個(gè)字段。第一個(gè)字段表示索引的鍵值，對(duì)于單列索引來說是一個(gè)值；而對(duì)于多列索引來說則是多個(gè)值組合在一起的。第二個(gè)字段表示鍵值所對(duì)應(yīng)的記錄行的ROWID，該ROWID是記錄行在表里的物理地址。 如果索引是創(chuàng)建在非分區(qū)表上或者索引是分區(qū)表上的本地索引的話，則該ROWID占用6個(gè)字 節(jié)；如果索引是創(chuàng)建在分區(qū)表上的全局索引的話，則該ROWID占用10個(gè)字節(jié)。 (2).索引大小的計(jì)算 默認(rèn)的PCTFREE=10%，則代表可用空間為90%；從9i開始，此90%也最多只能使用87%，則不同大小數(shù)據(jù)塊能實(shí)際用來存放索引的數(shù)據(jù)的空間為： 8k 8*1024*90%*87% ≈ 6414個(gè)字節(jié) 16k 16*1024*90%*87% ≈ 12828個(gè)字節(jié) 32k 16*1024*90%*87% ≈ 25657個(gè)字節(jié) 64k 64*1024*90%*87% ≈ 51314個(gè)字節(jié) 以上幾種塊是常用的幾種塊 A.葉子節(jié)點(diǎn)。 葉子節(jié)點(diǎn)中每個(gè)索引條目（記錄）都會(huì)在數(shù)據(jù)塊中占一行空間。 B.分支節(jié)點(diǎn)。分支節(jié)點(diǎn)的一行中所存放的所鏈接的最小鍵值所需空間與上面所描述的葉子節(jié)點(diǎn)相同。 C.舉例 表account_trade的記錄數(shù)為：1,546,240條，塊的大小為64k，如果yhkh上建立一個(gè)非分區(qū)的索引，則： a).一個(gè)數(shù)據(jù)塊可使用的字節(jié)數(shù)為： 64*1024*90%*87% ≈ 51314個(gè)字節(jié) b).葉子節(jié)點(diǎn)的大小為：3+1+19+1+6=30 個(gè)字節(jié) c).分支節(jié)點(diǎn)的大小為： 3+1+19+1+4=28 個(gè)字節(jié) d).一個(gè)葉子節(jié)點(diǎn)塊可存放的索引條目數(shù)(記錄數(shù))：51314/30 ≈ 1710 e). 1,546,240條記錄需要葉子節(jié)點(diǎn)塊數(shù)： 1546240/1710 ≈ 904 f).一個(gè)分支節(jié)點(diǎn)塊可存放的索引條目數(shù)(記錄數(shù))：51314/28 ≈ 1832 g).904個(gè)葉子節(jié)點(diǎn)需要的分支節(jié)點(diǎn)數(shù)為：904/1832 = 1個(gè) 如果塊的大小為8k，則： a).一個(gè)數(shù)據(jù)塊可使用的字節(jié)數(shù)為： 8*1024*90%*87% ≈ 6414個(gè)字節(jié) b).葉子節(jié)點(diǎn)的大小為：3+1+19+1+6=30 個(gè)字節(jié) c).分支節(jié)點(diǎn)的大小為： 3+1+19+1+4=28 個(gè)字節(jié) d).一個(gè)葉子節(jié)點(diǎn)塊可存放的索引條目數(shù)(記錄數(shù))：6414/30 ≈ 213 e). 1,546,240條記錄需要葉子節(jié)點(diǎn)塊數(shù)： 1546240/213 ≈ 7260 f).一個(gè)分支節(jié)點(diǎn)塊可存放的索引條目數(shù)(記錄數(shù))：6414/28 ≈ 229 g).7260個(gè)葉子節(jié)點(diǎn)需要的分支節(jié)點(diǎn)數(shù)為：7260/229 = 32個(gè) l結(jié)果：索引有兩層，第一層有一個(gè)根節(jié)點(diǎn)和32個(gè)分支節(jié)點(diǎn)，葉子節(jié)點(diǎn)7260個(gè) l注意：在 oracle 的索引中，層級(jí)號(hào)是倒過來的，也就是說假設(shè)某個(gè)索引有 N 層，則根節(jié)點(diǎn)的層級(jí)號(hào)為 N ，而根節(jié)點(diǎn)下一層的分支節(jié)點(diǎn)的層級(jí)號(hào)為 N-1 ，依此類推。對(duì)本例來說， 32 個(gè)分支節(jié)點(diǎn)所在的層級(jí)號(hào)為 1 ，而根節(jié)點(diǎn)所在的層級(jí)號(hào)為 2 。 (3).索引掃描 Oracle檢索一行數(shù)據(jù)時(shí)，通過索引來驅(qū)動(dòng)，稱為索引掃描。數(shù)據(jù)掃描索引值時(shí)，所發(fā)生的I/O的次數(shù)與b-tree樹索引的深度數(shù)相同。 如果檢索的列僅僅是包含在索引中的列，則Oracle會(huì)直接從索引中讀取，而不會(huì)從表中讀?。蝗绻麢z索的列包含除了索引中以外的列，則Oracle先從索引中讀取，然后使用從索引中拿到的rowid從表中讀取數(shù)據(jù)。因此， 典型的掃描方 式是從索引數(shù)據(jù)塊和表數(shù)據(jù)塊中交替讀取。 A.全索引掃描(full index scan、FIS) 全索引掃描只有在CBO(cost-based SQL optimizer)模式下才有效，當(dāng)優(yōu)化器認(rèn)為全索引掃描比全表掃描更有效時(shí)，才使用全索引掃描。此時(shí)，Oracle會(huì)讀取全部的索引。一般地，在WHERE子句中含有索引列時(shí)可能才會(huì)發(fā)生全索引掃描，當(dāng)然，有時(shí)在某些特殊情況下，沒有上述WHERE子句，也可能發(fā)生全索引掃描。 全索引掃描中會(huì)忽略掉排序，因?yàn)榇藭r(shí)讀取的數(shù)據(jù)已經(jīng)是按照索引鍵值排序了。 A.全索引掃描(FIS) 全表掃描： select jsbh from teachers where gzje > 3000 全索引掃描： select jsbh from teachers where gzje > 3000 order by jsbh B.快速全索引掃描(fast full index scan、FFIS) 快速全索引掃描僅僅檢索索引而不檢索表的一種全索引掃描方式，并且Oracle讀取索引塊時(shí)也不再具有特定的排序。 當(dāng)同時(shí)滿足下列條件是，Oracle用FFIS替代FIS: *.查詢的所有列均包含在索引中 *.查詢的結(jié)果集中沒有任何null值(一般是在WHERE子句中使用is not null或者使用not null約束來實(shí)現(xiàn)) B.快速全索引掃描(fast full index scan、FFIS) 在表account_trade上建立一個(gè)b-tree索引，包含yhkh,jysj，則： FFIS： select a.yhkh,a.jysj from account_trade a order by a.yhkh; 全表掃描： select a.yhkh,a.jysj from account_trade a where a.jyje > 2000 order by a.yhkh; C. 索引范圍掃描(index range scan) 索引范圍掃描是按順序的對(duì)某個(gè)索引進(jìn)行掃描。當(dāng)滿足下列條件時(shí)，會(huì)發(fā)生索引范圍掃描: *.在唯一索引上使用范圍操作符(>、<、>=、<=、<>、BETWEEN) select a.ckh,a.CKMC from course a where a.ckh > 'C005' *.在組合索引上使用部分列進(jìn)行查詢，導(dǎo)致查處多行 在表account_trade上建立一個(gè)b-tree索引，包含yhkh,jysj，jyje。 select a.yhkh,a.jysj from account_trade a where a.yhkh = '6002780207313762981' C. 索引范圍掃描(index range scan) *.在非唯一索引列上進(jìn)行的任何查詢 在表account_trade上建立一個(gè)b-tree索引，包含nd。 select * from account_trade a where a.nd = 2012; select * from account_trade a where a.nd > 2012; D. 索引唯一掃描 (index unique scan) D. 索引唯一掃描 (index unique scan) 區(qū)別索引范圍掃描的是索引唯一掃描要么返回0行數(shù)據(jù)要么返回1行數(shù)據(jù)，不會(huì)返回多行數(shù)據(jù)。Oracle使用索引唯一掃描時(shí)，一旦發(fā)現(xiàn)時(shí)數(shù)據(jù)，則停止繼續(xù)掃描，因此效率很高。如果唯一索引是組合索引，則至少需要參與索引的列作為引導(dǎo)列。 select * from productsale_singnle a where a.sale_date = '20120103' and a.provice_name = '上海' and a.city_name = ‘徐匯’ and a.product_name = ‘iPhone 4’ --索引唯一掃描 select * from productsale_singnle a where a.sale_date = '20120103' and a.provice_name = '上海' and a.city_name = ‘徐匯’ --索引范圍掃描 select a.ckh,a.CKMC from course a where a.ckh = 'C005‘--索引唯一掃描 E.索引跳躍式掃描 (index skip scan) 當(dāng)表有一個(gè)復(fù)合索引，而在查詢中有除了索引中第一列的其他列作為條件，并且優(yōu)化器模式為CBO，這時(shí)候查詢計(jì)劃就有可能使用到索引跳躍式掃描 。此外，還可以通過使用提示index_ss(CBO下)來強(qiáng)制使用索引跳躍式掃描。 索引跳躍式掃描是從9i開始新增的，但Oracle沒有公布更多的官方的技術(shù) 細(xì)節(jié)，模擬此種索引跟多個(gè)條件相關(guān)。 (4).反轉(zhuǎn)鍵索引 反轉(zhuǎn)鍵索引就是將索引鍵的值反轉(zhuǎn)過來進(jìn)行索引，索引的結(jié)構(gòu)沒什么變化，只是索引值的存儲(chǔ)方式相反的一種索引。例如一個(gè)索引的鍵是20，假如在標(biāo)準(zhǔn)的b-tree中存儲(chǔ)在該鍵中的兩個(gè)十六進(jìn)制字節(jié)為C1、15，則反轉(zhuǎn)鍵存儲(chǔ)的是15、C1。 Oracle中，對(duì)于長事務(wù)的大表、存在對(duì)相同的磁盤塊區(qū)重復(fù)讀寫的問題的，隨著時(shí)間的推移，此種I/O競爭會(huì)帶來越來越低的效率，如果數(shù)據(jù)以反轉(zhuǎn)鍵索引存儲(chǔ)，這些數(shù)據(jù)的值就會(huì)與原先存儲(chǔ)的數(shù)值相反，會(huì)有效改善這一問題。 反轉(zhuǎn)鍵索引不能對(duì)位圖索引和索引組織表進(jìn)行反轉(zhuǎn)鍵處理,詳細(xì)情況請(qǐng)參閱Oracle官方文檔。 (5).升序及降序索引 Oracle默認(rèn)的是升序索引，如果在列名后跟上DESC，則是對(duì)該列進(jìn)行降序索引，降序索引在葉子節(jié)點(diǎn)中的存儲(chǔ)從左到右是按照從大到小排序的。按列指定降序和升序索引，在復(fù)合索引中往往比較有用。 例如將表account_trade上建立的一個(gè)b-tree索引修改成，包含yhkh ASC,jyje ASC,jysj DESC。 由于我們經(jīng)常需要訪問的是最新的數(shù)據(jù)，故按交易 時(shí)間降序排列是有意義的。 2.bitmap索引 位圖索引適用于低基數(shù)(low-cardinality)列,所謂低基數(shù)列就是指這個(gè)列只有很少的可取值。不同于b-tree索引，位圖索引的一個(gè)鍵指向多行，可能數(shù)以百計(jì)甚至更多（b-tree索引一個(gè)鍵指向一行）。如果更新一個(gè)位圖索引鍵，那么這個(gè)鍵指向的數(shù)百條記錄會(huì)與你實(shí)際更新的那一行一同被有效地鎖定。 位圖索引的適用場(chǎng)景:
*. 位圖索引是針對(duì)那些值不經(jīng)常改變的字段的 *. 如果某個(gè)字段的值需要頻繁更新，那么就不適合在它上面創(chuàng)建位圖索引。 (1).單表位圖索引 在列XB上建立一個(gè)位圖索引，其結(jié)構(gòu)圖如下： 值 | Row1 | Row2 | Row3 | Row4 | … | Row23 | Row24 | 男 | 1 | 0 | 0 | 1 | | 1 | 0 | 女 | 0 | 1 | 1 | 0 | | 0 | 1 |
(2). 位圖連接索引 位圖連接索引是基于兩個(gè)或兩個(gè)以上數(shù)據(jù)表建立的一種位圖索引。對(duì)以一個(gè)表中某個(gè)列的每一個(gè)值，索引存儲(chǔ)了索引相應(yīng)行的rowid在索引表中，而標(biāo)準(zhǔn)位圖索引是建立在單表上的。 位圖連接索引比位圖索引更進(jìn)了一步。這些索引將位圖化的列完全從表數(shù)據(jù)中抽取出來，并將其存儲(chǔ)在索引中。其假定條件是這些列集合必須一起查詢。同樣的，這 也是為數(shù)據(jù)倉庫數(shù)據(jù)庫而設(shè)計(jì)的。除了在句法最后有一個(gè)WHERE子句之外，位圖連接索引的創(chuàng)建指令就像創(chuàng)建位圖索引的CREATE BITMAP INDEX一樣。 位圖連接索引一般用于數(shù)據(jù)倉庫，請(qǐng)參閱oracle官方文檔。 3.基于函數(shù)的索引 如果對(duì)一個(gè)表的經(jīng)常查詢的WHERE子句中包含基于列的函數(shù)或者表達(dá)式計(jì)算，則可以對(duì)該列建立基于函數(shù)的索引。函數(shù)索引能夠計(jì)算出函數(shù)或表達(dá)式的值，并將其保存在索引中。用戶創(chuàng)建的函數(shù)索引既可以是b-tree類型的，也可以是bitmap 類型的。 只有當(dāng)查詢語句包含該函數(shù)或者表達(dá)式時(shí)，基于函數(shù)的索引才會(huì)被調(diào)用。建立了基于函數(shù)的索引索引以后，執(zhí)行INSERT或者UPDATE語句，數(shù)據(jù)庫同步也需要執(zhí)行該索引的計(jì)算工作。 建設(shè)建立索引如下： drop index idx_teachers_ngzje; create index idx_teachers_ngzje on teachers(12 * gzje); 3.基于函數(shù)的索引 則： select * from teachers where 12 * gzje < 20000 --調(diào)用基于函數(shù)的索引 select * from teachers where 6 * gzje < 10000 --全表掃描 select avg(12*gzje) from teachers --調(diào)用基于函數(shù)的索引 select avg(6*gzje) from teachers --全表掃描 可見Oracle優(yōu)化器在SELECT子句或者WHERE子句中包含該函數(shù)或者表達(dá)式時(shí)會(huì)使用范圍索引掃描來調(diào)用該索引。 怎樣選擇索引（8建議） 1.由于唯一索引掃描效率最高，因此能使用唯一索引，盡量使用唯一索引； 2.對(duì)于取值基數(shù)較少的字段，如性別、狀態(tài)、Check項(xiàng)，盡量使用位圖索引； 3.由于NULL值在索引中沒有定義，因此建議盡量不要在允許NULL值的列上建立索引，雖然Oracle宣稱在允許NULL值的列上位圖索引，也是有效的，但我們依然強(qiáng)烈建議可以將null指定為一個(gè)默認(rèn)的值后再建立索引； 4.根據(jù)實(shí)際情況，一般對(duì)于列不超過10個(gè)，數(shù)據(jù)不超過1萬行的表不要建立索引，此種情況在應(yīng)用中往往能占40%以上； 5.一個(gè)表中不是建立的索引越多越好，相反過多的索引可能影響整體的性能，至少影響INSERT、DELETE和UPDATE的性能； 6.對(duì)于查詢中的WHERE子句經(jīng)常需要使用多個(gè)AND條件一起使用時(shí)，應(yīng)建立聯(lián)合索引，一般情況下，此種聯(lián)合索引是標(biāo)示一行唯一記錄的，而主鍵使用代理主鍵，并且盡量將值少的列放在前面； 7.對(duì)于多表聯(lián)合查詢，及表連接的問題，對(duì)于連接的字段，需要建立索引； 8.平衡索引與更新的關(guān)系。如果一個(gè)大表的更新操作多于查詢操作，建議可以少建立索引；如果一個(gè)大表的查詢操作多于更新操作則可以考慮多使用索引。 索引之性能體驗(yàn)和監(jiān)測(cè)案例 1.Oracle Explain Plan結(jié)果中幾個(gè)重要名次解釋 （1）COST： Oracle依據(jù)其查詢優(yōu)化器選擇的執(zhí)行路徑估算的操作開銷（也稱為成本、耗費(fèi)）。開銷并不決定表的訪問操作，其值不具備任何特定的度量單位，僅僅代表該執(zhí)行計(jì)劃的加權(quán)值，是通過IO_COST和CPU_COST計(jì)算得出的(oracle文檔中未公布算法）。 （2）CPU_COST： 查詢優(yōu)化器需要的CPU操作開銷估算值，其值與操作所需的機(jī)器運(yùn)轉(zhuǎn)周期成正比。 （3）IO_COST：查詢優(yōu)化器需要的IO操作開銷估算值，其值與讀取的數(shù)據(jù)塊成正比。 1.Oracle Explain Plan結(jié)果中幾個(gè)重要名次解釋 （4）CARDINALITY： 查詢優(yōu)化器訪問的數(shù)據(jù)行的估算基數(shù)。 （5）TIME：查詢優(yōu)化器消耗的時(shí)間估算值（單位為秒）。 2.刪除索引 將表account_trade上的所有索引均刪除掉 3.性能監(jiān)測(cè)體驗(yàn) 使用SQL語句 3.性能監(jiān)測(cè)體驗(yàn)--使用SQL語句  l開銷降低47%、執(zhí)行時(shí)間提高31% l此外還有多種查看方式：SQL窗口查詢、PL/SQL DEVELPOER 解釋計(jì)劃窗口、通過toad查看等 何時(shí)需要重建索引 1.葉子節(jié)點(diǎn)中浪費(fèi)的空間大于20%時(shí) lanalyze index IDX_ACCOUNT_TRADE_YHKH validate structure; lselect a.name,(a.del_lf_rows_len/a.lf_rows_len) * 100 from index_stats a 其中del_lf_rows_len是葉子節(jié)點(diǎn)中被刪除的行數(shù); lf_rows_len是葉子節(jié)點(diǎn)的行數(shù) lalter index IDX_ACCOUNT_TRADE_YHKH rebuild或者刪除該索引再創(chuàng)建 2.監(jiān)視索引的使用情況，對(duì)從來沒使用的索引進(jìn)行刪除 性能優(yōu)化更多的高級(jí)內(nèi)容超出了本課范圍，請(qǐng)參見oracle官方文檔 要點(diǎn)及習(xí)題 1.使用索引的優(yōu)點(diǎn)和缺點(diǎn)分別包括哪些？ 2.分別繪圖說明b-tree索引和bitmap索引，并解釋其存儲(chǔ)結(jié)構(gòu)，重點(diǎn)要求闡述清楚b-tree索引兩種節(jié)點(diǎn)的存儲(chǔ)單元和構(gòu)成以及bitmap索引適用的情況。 3.結(jié)合第2題的理論，舉例說明表productsale_star如果有10萬行記錄，其索引PK_productsale_start的存儲(chǔ)大小估算是多少？ 4.解釋oracle查詢數(shù)據(jù)時(shí)，一般有幾種掃描方式，為什么在大表中要避免全表掃描，而在小表中盡量少使用索引？ 5.降序索引和基于函數(shù)的函數(shù)索引各有什么好處，分別在什么情況下才適合使用？ 6.推薦的索引使用方式主要包括哪些（要求至少要回答出5種情況）。 7.在實(shí)踐中，對(duì)索引的監(jiān)測(cè)主要使用的是EXPLAIN PLAN，那么監(jiān)測(cè)的重 |
