Oracle 10g11g索引創(chuàng)建-使用-問題分析(針對編程人員).ppt

oracle 10g/11g 索引創(chuàng)建-使用-問題分析 (針對編程人員) 趙元杰 中程在線(北京)科技有限公司 2009.8 *1 內(nèi)容提要 |編程與索引 |各類索引適應(yīng)場合 |索引的訪問方法 |sql語句有關(guān)的索引調(diào)整 |索引參數(shù)與創(chuàng)建索引緩沖 |索引問題分析 |對開發(fā)者的建議 |附錄:oracle 索引指標(biāo) |參考資料 date2oracle 數(shù)據(jù)庫設(shè)計與性能 編程與索引 |關(guān)于索引是什么? | 在列上定義 | 避免全表掃描 |索引如何工作或不工作 |在insert/update 與 select 之間交替使用 |主鍵自動變?yōu)樗饕齬owid (hidden ) banne r id nameagetel 1b001chris2023232 2b005bates2123232 3b003hugh2923902 4b010mark2823902 5b008martin3323920 6b002jones2232902 banner idrow id b0011 b0026 b0033 b0052 b0085 b0104 date3oracle 數(shù)據(jù)庫設(shè)計與性能 |應(yīng)用系統(tǒng)設(shè)計人員： | 按照業(yè)務(wù)要求建立主鍵，可形成索引； | 可建立唯一索引。 |程序編碼人員： | 按照業(yè)務(wù)查詢特點創(chuàng)建各類索引； | 只要有create index權(quán)限即可； | 在編寫查詢程序時，要知道所訪問表中 哪些列已經(jīng)創(chuàng)建了哪些索引； | 在編程中運用這些索引才能達到優(yōu)化的 目的。 編程與索引 date4oracle 數(shù)據(jù)庫設(shè)計與性能 |應(yīng)用系統(tǒng)設(shè)計人員： | 在查詢表數(shù)據(jù)前，要了解鎖訪問的表有 哪些索引； | 每個索引的列的順序； | 在where 條件中使用索引。 |索引信息的獲得： | 訪問表的索引信息可從數(shù)據(jù)字典查詢； | 可通過圖形界面查看索引的列名詞； | 可通過dbms_metadata.get_ddl導(dǎo) 出索引的創(chuàng)建信息。 編程與索引-索引信息 date5oracle 數(shù)據(jù)庫設(shè)計與性能 |從sql developer 瀏覽索引信息： | 啟動sql developer,連接到某個用戶； | 選擇“index” 了列出索引信息 編程與索引-索引信息 索引列 表名 date6oracle 數(shù)據(jù)庫設(shè)計與性能 |索引有多少？可查詢： |dba_constraints 限制性的信息； | dba_indexes 表的主鍵，外部鍵及創(chuàng)建的 索引的信息； | dba_cons_columns 表中限制的列信息 ； | dba_ind_columns 表中的索引列信息。 dba_constraints描述約束的信息，包括primary ket、 foreign key、unique key、not null及check。 列constraint_type的含義如下: c (表中的check約束) p (主鍵) u (惟一鍵) r (引用完整性,外鍵) v (視圖中的check檢查) o (視圖中的只讀=read only) 編程與索引-信息查詢 date7oracle 數(shù)據(jù)庫設(shè)計與性能 |查詢索引列有關(guān)的數(shù)據(jù)字典： | user_ind_columns -索引列的次 序column_position ； sql select table_name,index_name,column_position 2 from user_ind_columns 3* where table_name=emp; table_name index_name column_position - - - emp pk_emp 1 編程與索引-信息查詢 date8oracle 數(shù)據(jù)庫設(shè)計與性能 |索引信息查詢的必要性： | 程序開發(fā)人員了解訪問表的索引列信息， 才能在where 條件句使用它。 sql-查詢表所包含的索引名稱: sqlselect index_name, table_name from user_indexes where table_name=xxxxx; sql- 使用dbms_metadata.get_ddl包獲得索引信息： sqlselect ( index,index_name) from dual; 編程與索引-信息查詢 date9oracle 數(shù)據(jù)庫設(shè)計與性能 內(nèi)容提要 |編程與索引 |各類索引適應(yīng)場合 |索引的訪問方法 |sql語句有關(guān)的索引調(diào)整 |索引參數(shù)與創(chuàng)建索引緩沖 |索引問題分析 |對開發(fā)者的建議 |參考資料 date10oracle 數(shù)據(jù)庫設(shè)計與性能 oracle的各種索引 |oracle可創(chuàng)建下面索引: |b-tree索引默認(rèn)的通用索引 |b-tree cluster索引為 cluster創(chuàng)建的索引* |逆鍵索引-索引列有規(guī)律產(chǎn)生的情景 |hash索引-為 hash cluster創(chuàng)建的索引* |bitmap索引-索引列的值變化可枚舉的情景 |iot表(索引結(jié)構(gòu)表)-獨立的以主鍵查詢的表的情 景 date11oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree 索引- b*tree 存儲示義圖: adamsadams birdbird binderbinder chenchen dunnedunne earlearl figeroafigeroa georgegeorge hanlyhanly jonesjones kingking legrandlegrand lokerloker mitchellmitchell sanchezsanchez yamadayamada root block:root block:=h=h =d=d=lo=lo branchbranch blocks:blocks: leaf:leaf: 員工名字: adams, bird, binder, chen, dunne, earl, figeroa, george, hanly, jones date12oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree 索引- b*tree 存儲與使用示義圖: date13oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-索引的塊葉 |每個索引塊至少包含一個葉塊 |每個葉塊包含0個或多個數(shù)據(jù)行 |每個數(shù)據(jù)行一個鍵值與數(shù)據(jù) |索引可以是唯一的或非唯一 |唯一的和非唯一索引的葉行格式不同 date14oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-葉塊內(nèi)部結(jié)構(gòu) 20 bytes 72 bytes 16 bytes 16 bytes 2 bytes per row 4 bytes block common header transaction header index header index leaf header index leaf rows tail free space slot array block size2 bytes date15oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-塊的分枝 |索引可以包含分枝塊 |分枝塊指向其它的分枝塊或葉塊 |分枝塊包含 0 行或多個行 |每個行有一個后綴鍵和指針指向下一個塊 |壓縮行以 0xfe 字節(jié)結(jié)束 date16oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-分枝塊結(jié)構(gòu) 20 bytesblock common header transaction header index header index branch header index branch rows tail free space slot array 48 bytes 16 bytes 24 bytes 2 bytes per row 4 bytes block size2 bytes date17oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-分枝塊 |每個塊有一個指針指向樹的后面. 這是頭 的部分 |一個分枝快包含 n 行指向 n+1 塊. s deu denengsco spa usaaus bel can branch blocks root block leaf blockslevel 0 level 1 level 2 date18oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*treee-創(chuàng)建 |創(chuàng)建b*tree索引： create index emp_ename on emp(ename) tablespace users storage (initial 5m next 2m pctincrease 0 ) pctfree 0; |創(chuàng)建唯一索引： create unique index dept_unique_index on dept (dname) tablespace indx; date19oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-主鍵(=唯一索引) |語法 create unique index dept_name_unq on department(name) ; | 建議加表空間等描述 alter table department add constraint pk_dept_id primary key (dept_id) ; date20oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree主鍵與唯一索引 |主鍵要求 | 如果是一個列的話，該列不能為null | 如果多個列構(gòu)成主鍵，至少有一個列是 非空 |唯一索引 | 可以空，但有值必須唯一： date21oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-nosort |如果創(chuàng)建索引時，表的列的值是有規(guī)律按照由 小到大排列，則創(chuàng)建索引時不需要對列的值進 行排序，可用 nosort子句描述，如： create index index1 on table1 (column1) nosort |如果列沒有按照由小到大排列，則返回錯 誤: ora-01409: nosort option may not be used; rows are not in ascending order date22oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree主鍵與唯一索引 |練習(xí)1： | 創(chuàng)建表后加主鍵與索引： | 可加表空間存儲描述等； create table dept ( dept_id varchar2(5), dept_name varchar2(30) ) tablespace users; add a primary key constraint add a unique constraint - select constraint_name from user_constraints ; select index_name, table_name from user_indexes ; - drop index (index name) ; date23oracle 數(shù)據(jù)庫設(shè)計與性能 1.b*tree-唯一索引例子 create unique index test_rsidx on test(b) reverse pctfree 30 storage(initial 200k next 200k pctincrease 0 maxextents 50) tablespace indx; 索引已創(chuàng)建。 |練習(xí)2： 創(chuàng)建唯一索引，如： date24oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap索引概念 r o w 1 2 3 4 name joe jane john james m_status single married divorced married state pa ca ca pa gender m f m m m_status-idx single married divorced state-idx ca pa gender-idx m f 1 0 0 0 0 1 0 0 0 0 1 0 0 1 1 0 1 0 0 1 1 0 1 1 0 1 0 0 select count(*) from customers where m_status = married and state = ca and gender = m date25oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap適合的情況 |bitmap索引特點： |當(dāng)表很大（如數(shù)百萬行），且關(guān)鍵字列的基數(shù)低（如性別列 ）時，即該列有非常少的獨特值例 |當(dāng)查詢經(jīng)常使用涉及or 運算符的多個where 條件組合時 |當(dāng)關(guān)鍵字列上存在只讀或很少的更新操作時； |bitmap索引空間： |比一般的索引占用更大空間 ； |可專門分析存儲再設(shè)置存儲參數(shù)； |見另外資料； date26oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap索引創(chuàng)建 create bitmap index schema. index on schema. table (column asc | desc , column asc | desc .) tablespace tablespace pctfree integer initrans integer maxtrans integer storage-clause logging| nologging nosort 注意，bitmap 不能是 unique 的索引 |創(chuàng)建bitmap索引語句的語法 |加bitmap 關(guān)鍵字即可： date27oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap索引的例子 sql create bitmap index test_bm_idx 2 on test(c) 3 pctfree 30 4 storage(initial 200k next 200k 5 pctincrease 0 maxextents 50) 6 tablespace indx; 索引已創(chuàng)建。 sql |創(chuàng)建bitmap索引例子 |建議注意存儲初始分配： date28oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap索引例子-電影級別 #1#1 #2#2 #3#3 #4#4 #5#5 . . * * * * * * * * * * 1 1 2 2 4 4 3 3 5 5 rowidrowid ratingrating star wars i, 2000.star wars i, 2000. exorcist, 1975, .exorcist, 1975, . castaway, 2000castaway, 2000 ghostbusters, 1983ghostbusters, 1983 harry potter, 2001harry potter, 2001 . . table datatable data 將電影分級：將電影分級：star wars-star wars-星球大戰(zhàn)星球大戰(zhàn) 5 5級級;ghostbusters ;ghostbusters 幽靈幽靈 為為2 2級級； date29oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap索引 |位圖占空間特點： |每個bit 只包含一個位； |如果有 10萬部電影，則： 100,000 movies * 5 ratings / movie = 500,000 possible ratings (bits) or (approx.) 50k bytes - create bitmap index movie$bitmap_rating_idx on movie(rating); date30oracle 數(shù)據(jù)庫設(shè)計與性能 2.bitmap與b *tree索引 |b*tree索引： |適合高基數(shù)的列 |更新關(guān)鍵字列的費用相對較低 |使用or 謂詞的查詢效率低 |對oltp 有用 |bitmap索引： |適合低基數(shù)的列 |更新關(guān)鍵字列的費用非常昂貴 |使用or 謂詞的查詢效率高 |對數(shù)據(jù)倉庫有用 date31oracle 數(shù)據(jù)庫設(shè)計與性能 3.函數(shù)索引 |函數(shù)索引概念： |基于函數(shù)的索引就是存儲預(yù)先計算好的函 數(shù)或表達式值的索引； |這些表達式可以是算術(shù)運算表達式、sql 或pl/sql函數(shù)、c調(diào)用等； |創(chuàng)建函數(shù)索引主要是依據(jù)where子句來確 定，比如： |select * from t1 where col_a + col_b create index sal_comm on emp ( (sal+comm)*12, sal,comm) 2 tablespace users storage(initial 64k next 64k pctincrease 0); create index sal_comm on emp ( (sal+comm)*12, sal,comm) * error at line 1: ora-01031: insufficient privileges sql connect sys/sysora816 connected. sql grant global query rewrite , create any index to scott; grant succeeded. -在連接到scott帳戶，創(chuàng)建基于函數(shù)的索引： sql connect scott/tigerora816 connected. sql create index sal_comm on emp ( (sal+comm)*12, sal,comm) 2 tablespace users storage(initial 64k next 64k pctincrease 0); index created. date34oracle 數(shù)據(jù)庫設(shè)計與性能 4.逆鍵索引 |逆鍵索引概念： |逆鍵索引也是b*tree索引的一種，它通過反向鍵保持 索引的所有葉子鍵上的插入分布； |可用逆鍵索引來避免不平衡的索引。對于逆鍵索引可 以進行下面操作： |逆鍵索引使用： |通過在alter index命令后加rebuild noreverse或rebuild reverse子句來使索引邊為反向 鍵索引或普通索引； |采用范圍掃描的查詢不能使用反向鍵索引； |位圖索引不能反向； |索引編排表不能反向。 date35oracle 數(shù)據(jù)庫設(shè)計與性能 key rowid empno (block# row# file#) - - 1257 0000000f.0002.0001 2877 0000000f.0006.0001 4567 0000000f.0004.0001 6657 0000000f.0003.0001 8967 0000000f.0005.0001 9637 0000000f.0001.0001 9947 0000000f.0000.0001 4.逆向鍵值索引 index on emp (empno)index on emp (empno) emp tableemp table empno ename job . - - - 7499 allen salesman 7369 smith clerk 7521 ward salesman . 7566 jones manager 7654 martin salesman 7698 blake manager 7782 clark manager |逆鍵索引示意圖： |將數(shù)據(jù)反轉(zhuǎn)后存儲： date36oracle 數(shù)據(jù)庫設(shè)計與性能 4.逆鍵索引 |逆鍵索引創(chuàng)建語法： |在create index最后面加 reverse 子句： create unique index schema. index on schema. table (column asc | desc , column asc | desc .) tablespace tablespace pctfree integer initrans integer maxtrans integer storage-clause logging| nologging reverse （注意，這里不能使用 nosort） date37oracle 數(shù)據(jù)庫設(shè)計與性能 4.逆鍵索引-例子 |創(chuàng)建逆鍵索引： |創(chuàng)建簡單逆鍵索引 例1：創(chuàng)建一個反向鍵索引： create index i on t (a,b,c) reverse; 例2：使一個索引變?yōu)榉聪蜴I索引： alter index i rebuild noreverse; date38oracle 數(shù)據(jù)庫設(shè)計與性能 4.逆鍵索引-例子 |逆鍵索引用途： |大規(guī)模的直接數(shù)據(jù)插入可采用逆鍵索引； |關(guān)鍵字前面一部分變化不大,oracle會將reverse 關(guān)鍵字指定給b-tree前把reverse字符串簡化，逆鍵 關(guān)鍵字索引是好方法。 |逆鍵索引可能更平衡，搜索也更快。 date39oracle 數(shù)據(jù)庫設(shè)計與性能 5.其他類型索引 |hash索引： |在cluster上創(chuàng)建的hash索引 |如果創(chuàng)建了hash cluster，則也要建立集群鍵 |詳細(xì)參見cluster與iot |iot-索引結(jié)構(gòu)表： |是一種將表數(shù)據(jù)和索引數(shù)據(jù)項存儲在一起的對象 |詳細(xì)參見cluster與iot |分區(qū)的索引： |對大索引按照分區(qū)分方法進行分區(qū) |詳細(xì)參見oracle分區(qū)設(shè)計 |位圖連接索引： |在數(shù)據(jù)倉庫中使用 |詳細(xì)參見oracle 數(shù)據(jù)倉庫設(shè)計 date40oracle 數(shù)據(jù)庫設(shè)計與性能 內(nèi)容提要 |編程與索引 |各類索引適應(yīng)場合 |索引的訪問方法 |sql語句有關(guān)的索引調(diào)整 |索引參數(shù)與創(chuàng)建索引緩沖 |索引問題分析 |對開發(fā)者的建議 |參考資料 date41oracle 數(shù)據(jù)庫設(shè)計與性能 |創(chuàng)建索引主要目的就使用： |無論索引是由主鍵構(gòu)成或是為了提高查詢速度而創(chuàng)建 索引，都建議開發(fā)人員在select 語句使用索引 ； |索引由單個列構(gòu)成，則在where中直接使用； |如果索引是由主鍵構(gòu)成，要將該索引寫在where的 更靠左邊列判斷中 |少數(shù)情況可不用索引： |當(dāng)表的數(shù)據(jù)量只有幾千行時，不用索引可能更快； |不用索引可在where子句中不指定索引列即可； |可通過提示來避免使用索引。 where條件中使用索引 date42oracle 數(shù)據(jù)庫設(shè)計與性能 |索引列的使用： | where 從句指定索引列； | 不要再索引做任何的表達式，如 where條件中使用索引 sqlselect * from emp where empno=7788; date43oracle 數(shù)據(jù)庫設(shè)計與性能 |復(fù)合索引的選擇： |多列索引叫復(fù)合索引，復(fù)合索引有時比單列索引有 更好的性能。如果在建立索引時采用了幾個列作為索引 。則在使用時也要按照建立時的順序來描述，例如： |對于這樣的復(fù)合索引，下面幾種可自動使用索引： 復(fù)合索引與where子句 create index comp_ind on tab1(x, y, z); 這里的復(fù)合列變?yōu)?x,xy 和xyz 幾個部分。如果索引被建立成復(fù)合型， 則應(yīng)該在查詢語句中帶有： where . . . and 來使用復(fù)合鍵。 select col1,col2, from tab1 where x=xxx and y=yyy and z=xxx; 或 select col1,col2, from tab1 where x=xxx and y=yyy ; 或 select col1,col2, from tab1 where x=xxx ; date44oracle 數(shù)據(jù)庫設(shè)計與性能 |復(fù)合索引是否被使用： |如果column1 is null 或者column1 is not null時，即使在 column1列創(chuàng)建索引，優(yōu)化器也不會用該索引 ； |如果復(fù)合索引由兩個以上列構(gòu)成，在where子句中沒 有出現(xiàn)第1個列，優(yōu)化器也不會用該索引 ，如： 復(fù)合索引與where子句 create index skip1 on emp5(job,empno); index created. select count(*) from emp5 where empno=7900; elapsed:00:00:03.13 execution plan 0 select statement optimizer=choose(cost=4 card=1 bytes=5) 1 0 sort(aggregate) 2 1 index(fast full scan) of skip1(non-unique) statistics 6826 consistent gets 6819 physical reads 優(yōu)化器執(zhí)行的快速 全索引掃描 date45oracle 數(shù)據(jù)庫設(shè)計與性能 |復(fù)合索引與索引跳躍式掃描： | oracle 9i開始支持對，組合(concatented)索引 采用跳躍式掃描，即where子句中沒有出現(xiàn)第1 個列，優(yōu)化器也用該索引 ，利用前面例子： 復(fù)合索引與索引跳躍式掃描 select /*+ index(emp5 skip1) */ count(*) from emp5 where empno=7900; elapsed:00:00:00.56 execution plan 0 select statement optimizer=choose(cost=6 card=1 bytes=5) 1 0 sort(aggregate) 2 1 index( skip scan ) of skip1(non-unique) statistics 21 consistent gets 17 physical reads 優(yōu)化器執(zhí)行: 索引跳躍式掃描 date46oracle 數(shù)據(jù)庫設(shè)計與性能 |必要的情況下(如月報數(shù)據(jù)的統(tǒng)計) 可以允許使 用全表掃描外，一般都盡量避免涉及全表掃描； |下面情況oracle可能使用全表掃描： |所查詢的表沒有索引； |需要返回所有的行； |對索引主列有條件限制，但是使用了函數(shù)，則 oracle 使用全表掃描，如：where upper(city)=tokyo; |這樣的語句不會使用索引方法。所以就只能全表掃 描。 |帶有 is null 和is not null 及 != 等子句。如： |. . . where city is null ; |. . . where city is not null; |. . . where city != tokyo; |帶like 并使用%這樣的語句就使用全表掃描； 索引與全表掃描 date47oracle 數(shù)據(jù)庫設(shè)計與性能 |有時，使用索引進行查詢可能速度更慢，如果 用戶希望避免使用類似的訪問路徑。則采用全 表掃描方法； |下面是強制優(yōu)化程序使用全表掃描的方法 ： |提示no_index能夠提供cbo最大的靈活性， 并且禁止對某些索引的使用； |提示null 能夠強制優(yōu)化程序選擇全表掃描， 而不是索引掃描； |提示index,index_cobine或and-equal能 夠強制優(yōu)化程序使用某種索引，或者利用某種組合 索引。 避免使用索引的語句 date48oracle 數(shù)據(jù)庫設(shè)計與性能 索引謂詞 |創(chuàng)建索引目的是： | 提高從數(shù)據(jù)庫表中檢索少量數(shù)據(jù)的速度 ； | 預(yù)先排序結(jié)果集； | 下面語句不使用索引： select * from emp where empno=123; select * from emp where sal*2 10 ; select * from emp where to_char(hiredate,yyyy)=1980; date49oracle 數(shù)據(jù)庫設(shè)計與性能 內(nèi)容提要 |編程與索引 |各類索引適應(yīng)場合 |索引的訪問方法 |sql語句有關(guān)的索引調(diào)整 |索引參數(shù)與創(chuàng)建索引緩沖 |索引問題分析 |對開發(fā)者的建議 |參考資料 date50oracle 數(shù)據(jù)庫設(shè)計與性能 |索引不是由主鍵構(gòu)成，可考慮： | 在有規(guī)律的成批的數(shù)據(jù)dml時，可先 刪除有關(guān)的非主鍵的索引； | 成批dml處理完成后還是要重創(chuàng)建索 引； | 提示：不影響業(yè)務(wù)處理下才能考慮這樣 的方法。 索引有關(guān)的調(diào)整 date51oracle 數(shù)據(jù)庫設(shè)計與性能 |重新排序表記錄以減少i/o： | i/o 是反應(yīng)時間中最重要的組成部分； | oracle從數(shù)據(jù)文件讀取數(shù)據(jù)塊時，必須 等待物理i/o的完成，而物理操作要比數(shù)據(jù) 緩沖區(qū)操作慢1400倍； | 為減少i/o導(dǎo)致的沖突，采用ctas方法 進行重組也是一個辦法； | 與磁盤的平衡原理一樣，這樣的操作由 dba合理地進行。 索引有關(guān)的調(diào)整 date52oracle 數(shù)據(jù)庫設(shè)計與性能 |調(diào)整索引存儲塊參數(shù)調(diào)整索引存儲塊參數(shù)pctfreepctfree： | pctfree 參數(shù)用于控制塊，默認(rèn)10 ； | 索引的塊pctfree過低-導(dǎo)致表的更新后的 索引項數(shù)據(jù)也更新，可能產(chǎn)生塊空間的不 足； | 索引的塊pctfree過高-導(dǎo)致空間的浪費； | oltp可設(shè)置pctfree 高些，對于 dw/olap可設(shè)置為 0 。 索引有關(guān)的調(diào)整- date53oracle 數(shù)據(jù)庫設(shè)計與性能 內(nèi)容提要 |編程與索引 |各類索引適應(yīng)場合 |索引的訪問方法 |sql語句有關(guān)的索引調(diào)整 |索引參數(shù)與創(chuàng)建索引緩沖 |索引問題分析 |對開發(fā)者的建議 |參考資料 date54oracle 數(shù)據(jù)庫設(shè)計與性能 |optimizer_index_cost_adjoptimizer_index_cost_adj參數(shù)：參數(shù)： | 用來調(diào)整用來調(diào)整“ “基于代價的優(yōu)化基于代價的優(yōu)化” ”相對于全表相對于全表 掃描訪問掃描訪問 | 代表一個百分比代表一個百分比, ,取值范圍在取值范圍在1 1到到1000010000之之 間；可理解為間；可理解為oracleoracle執(zhí)行多塊（執(zhí)行多塊（multiblockmultiblock ） i/oi/o（比如全表掃描）的代價（比如全表掃描）的代價與與執(zhí)行單塊執(zhí)行單塊 （single-blocksingle-block）i/oi/o代價的相對比例代價的相對比例 | 缺省值缺省值100100表示索引掃描成本等價轉(zhuǎn)換與表示索引掃描成本等價轉(zhuǎn)換與 全表掃描成本全表掃描成本 | 若減小，則若減小，則cbocbo傾向于使用索引（即單傾向于使用索引（即單 塊塊i/oi/o），反之則傾向于全表掃描（多塊），反之則傾向于全表掃描（多塊i/oi/o ） 索引參數(shù)-optimizer_index_cost_adj date55oracle 數(shù)據(jù)庫設(shè)計與性能 |optimizer_index_cost_adjoptimizer_index_cost_adj參數(shù)調(diào)整：參數(shù)調(diào)整： | 多數(shù)多數(shù)oltpoltp系統(tǒng)，系統(tǒng)， optimizer_index_cost_adjoptimizer_index_cost_adj可以設(shè)置可以設(shè)置 在在1010到到5050之間之間 | 對于數(shù)據(jù)倉庫和對于數(shù)據(jù)倉庫和dssdss系統(tǒng)，可能不能簡系統(tǒng)，可能不能簡 單的把單的把optimizer_index_cost_adjoptimizer_index_cost_adj設(shè)設(shè) 置為置為5050，通常我們需要反復(fù)調(diào)整取得一個合，通常我們需要反復(fù)調(diào)整取得一個合 理值理值. . | 更為具體的可以根據(jù)統(tǒng)計信息更為具體的可以根據(jù)統(tǒng)計信息,db file ,db file scattered reads/db file sequential readsscattered reads/db file sequential reads來計算來計算 索引參數(shù)-optimizer_index_cost_adj date56oracle 數(shù)據(jù)庫設(shè)計與性能 |optimizer_index_cachingoptimizer_index_caching參數(shù)：參數(shù)： | 告訴告訴oracleoracle你的索引在內(nèi)存的數(shù)據(jù)緩沖你的索引在內(nèi)存的數(shù)據(jù)緩沖 區(qū)中的可能性有多大區(qū)中的可能性有多大，即向優(yōu)化器暗示位于，即向優(yōu)化器暗示位于 oracleoracle數(shù)據(jù)數(shù)據(jù) 緩沖區(qū)的索引的數(shù)量緩沖區(qū)的索引的數(shù)量 | 參數(shù)設(shè)置將會影響到參數(shù)設(shè)置將會影響到“ “基于代價的優(yōu)化基于代價的優(yōu)化” ” 做出的對一個表連接（嵌套循環(huán)）使用索引做出的對一個表連接（嵌套循環(huán)）使用索引 還是使用全表掃描選擇還是使用全表掃描選擇 | 參數(shù)參數(shù)optimizer_index_cachingoptimizer_index_caching是一個百分是一個百分 比參數(shù)，有效取值范圍是比參數(shù)，有效取值范圍是0 0到到100100 索引參數(shù)-optimizer_index_caching date57oracle 數(shù)據(jù)庫設(shè)計與性能 |optimizer_index_cachingoptimizer_index_caching參數(shù)調(diào)整：參數(shù)調(diào)整： | 這個參數(shù)使得我們能夠調(diào)節(jié)這個參數(shù)使得我們能夠調(diào)節(jié)“ “基于代價的基于代價的 sqlsql優(yōu)化器優(yōu)化器” ”的行為，從而選擇最好的方式來訪的行為，從而選擇最好的方式來訪 問想要的問想要的sqlsql查詢結(jié)果：查詢結(jié)果： |嵌套循環(huán)連接嵌套循環(huán)連接 |哈希連接訪問哈希連接訪問 |全索引掃描全索引掃描 |全表掃描訪問全表掃描訪問 | optimizer_index_cachingoptimizer_index_caching設(shè)置為較大的百分設(shè)置為較大的百分 比，可以使嵌套循環(huán)連接在優(yōu)化器看起來更便比，可以使嵌套循環(huán)連接在優(yōu)化器看起來更便 宜，從而優(yōu)化器更有可能選擇進行嵌套循環(huán)連宜，從而優(yōu)化器更有可能選擇進行嵌套循環(huán)連 接而不是哈希連接或者歸并排序連接接而不是哈希連接或者歸并排序連接 索引參數(shù)-optimizer_index_caching date58oracle 數(shù)據(jù)庫設(shè)計與性能 |db_file_multiblock_read_countdb_file_multiblock_read_count參數(shù)：參數(shù)： | 這個參數(shù)表示每次從數(shù)據(jù)文件讀取的塊數(shù)這個參數(shù)表示每次從數(shù)據(jù)文件讀取的塊數(shù) | 當(dāng)使用更大的服務(wù)器時可設(shè)置大些當(dāng)使用更大的服務(wù)器時可設(shè)置大些 | 基于基于cbocbo，優(yōu)化器會識別出分散的（多塊，優(yōu)化器會識別出分散的（多塊 ）讀操作的代價或許比識別順序讀操作的代價）讀操作的代價或許比識別順序讀操作的代價 更小一些。這就使得更小一些。這就使得cbocbo更加傾向于全表掃描更加傾向于全表掃描 參數(shù)-db_file_multiblock_read_count date59oracle 數(shù)據(jù)庫設(shè)計與性能 |db_file_multiblock_read_countdb_file_multiblock_read_count參數(shù)調(diào)整：參數(shù)調(diào)整： |10g版本為自動調(diào)整(不設(shè)置就表示自動管理 ) | 默認(rèn)為i/o可執(zhí)行的最大值 | 與平臺有關(guān)，不能超過緩沖區(qū)的10% | 通常相當(dāng)于1mb | 對數(shù)據(jù)倉庫/dss可能不合適 參數(shù)-db_file_multiblock_read_count date60oracle 數(shù)據(jù)庫設(shè)計與性能 參數(shù)-db_file_multiblock_read_count |oracle r2 版本與這個參數(shù)相關(guān)的功能有： |v$process_memory 輔助監(jiān)視自動pga管理與確定 pga_aggregate_target參數(shù)的最佳設(shè)置 |如果沒有明確設(shè)置 db_file_multiblock_read_count參數(shù)，則表示為 自動調(diào)整 |大小與平臺有關(guān)，但文檔經(jīng)常說明為1mb sysorcl select isdefault, descriptionsysorcl select isdefault, description 2 from v$parameter2 from v$parameter 3 where name = db_file_multiblock_read_count;3 where name = db_file_multiblock_read_count; isdefault descriptionisdefault description - - - false db block to be read each iofalse db block to be read each io date61oracle 數(shù)據(jù)庫設(shè)計與性能 參數(shù)-skip_unusable_indexes |skip_unusable_indexes參數(shù)： | 在會話中設(shè)置，表示要跳過無效的索引 | 在exp中表示要跳過無效的索引 (不導(dǎo)出) | 在imp中表示要跳過無效的索引 (不維護) |索引為什么無效： | 當(dāng)將表從一個表空間遷移到另外新表空間 后，原來的索引自動變?yōu)闊o效 | 可用alter index idxt unusable;命令使索引失 效 | 無效的索引的狀態(tài)在user_indexes. status列 的值為“unusable” date62oracle 數(shù)據(jù)庫設(shè)計與性能 參數(shù)-skip_unusable_indexes |索引無效(unusable )的處理： | 可設(shè)置skip_unusable_indexes跳過失效的索 引 | 這個參數(shù)可在系統(tǒng)或會話級上設(shè)置，如： sql select * from a where id=1;sql select * from a where id=1; select * from a where id=1select * from a where id=1 * * 第第 1 1 行出現(xiàn)錯誤行出現(xiàn)錯誤: : ora-01502: ora-01502: 索引索引 test.idx_a_id test.idx_a_id 或這類索引的分區(qū)處于不或這類索引的分區(qū)處于不 可用狀態(tài)可用狀態(tài) sql alter system set sql alter system set skip_unusable_indexesskip_unusable_indexes=true =true scope=memory;scope=memory; 系統(tǒng)已更改。系統(tǒng)已更改。 sql select * from a where id=1;sql select * from a where id=1; date63oracle 數(shù)據(jù)庫設(shè)計與性能 參數(shù)-skip_unusable_indexes |索引無效處理建議： | 經(jīng)常檢查關(guān)鍵索引是否失效 | 直接查詢dba_indexes的status列是否為 “unusable”即可 | 對于處于無效的索引，建議找時間要重建 ，而不是一味的跳過，跳過只是一種臨時的辦 法 date64oracle 數(shù)據(jù)庫設(shè)計與性能 |oracle 9i /10g/11goracle 9i /10g/11g的變化：的變化： | 從從oracle 9ioracle 9i開始，運行配置多種塊大?。洪_始，運行配置多種塊大?。?|創(chuàng)建表空間的塊大小為創(chuàng)建表空間的塊大小為2k,4k,8k,16k2k,4k,8k,16k和和32k32k |并使用相似大小的表和索引來匹配表空間并使用相似大小的表和索引來匹配表空間 | 我們應(yīng)該理解這些新變化：我們應(yīng)該理解這些新變化： |大數(shù)據(jù)塊的輸入輸出操作的邊緣開銷可忽略大數(shù)據(jù)塊的輸入輸出操作的邊緣開銷可忽略 不計不計 |取一個取一個32k32k大小的數(shù)據(jù)塊的開銷僅比取一個大小的數(shù)據(jù)塊的開銷僅比取一個 2k2k大小的數(shù)據(jù)塊的開銷多大小的數(shù)據(jù)塊的開銷多1 1，因為，因為9999的磁盤輸入的磁盤輸入 輸出操作都是用來把磁盤讀寫頭的移動到相應(yīng)的磁盤輸出操作都是用來把磁盤讀寫頭的移動到相應(yīng)的磁盤 柱面和軌道柱面和軌道 創(chuàng)建索引緩沖區(qū) date65oracle 數(shù)據(jù)庫設(shè)計與性能 |索引與大塊的表空間：索引與大塊的表空間： | 當(dāng)索引被存儲在大塊表空間中時使用起來當(dāng)索引被存儲在大塊表空間中時使用起來 更有效。更深層的原因是更有效。更深層的原因是： |b-b-樹可以更好地被平衡樹可以更好地被平衡 |并且對順序索引節(jié)點訪問而言有更小的磁盤并且對順序索引節(jié)點訪問而言有更小的磁盤 開銷開銷 | 著名作家著名作家robin schumacherrobin schumacher的研究表明的研究表明32k32k 塊大小的塊大小的oracleoracle索引在執(zhí)行索引范圍掃描時需索引在執(zhí)行索引范圍掃描時需 要更少的邏輯輸入輸出，并且性能更高要更少的邏輯輸入輸出，并且性能更高 創(chuàng)建索引緩沖區(qū) date66oracle 數(shù)據(jù)庫設(shè)計與性能 |創(chuàng)建索引緩沖：創(chuàng)建索引緩沖： | 可可在在數(shù)據(jù)庫處于活動狀態(tài)數(shù)據(jù)庫處于活動狀態(tài)創(chuàng)建索引緩沖創(chuàng)建索引緩沖 | 首先，把所有索引都移至某個單獨的表空首先，把所有索引都移至某個單獨的表空 間，定義成一個單獨的數(shù)據(jù)緩沖區(qū)間，定義成一個單獨的數(shù)據(jù)緩沖區(qū) | 然后將參數(shù)然后將參數(shù)optimizer_index_cachingoptimizer_index_caching設(shè)置成設(shè)置成 正確的值正確的值 | 分配一個分配一個32k32k的緩沖區(qū)空間的緩沖區(qū)空間 |alter system set db_32k_cache_size = 100m;alter system set db_32k_cache_size = 100m; | 創(chuàng)建塊大小創(chuàng)建塊大小32k32k的表空間的表空間 |create create tablespacetablespace index_ts_32k index_ts_32k blocksizeblocksize 32k;32k; 創(chuàng)建索引緩沖區(qū) date67oracle 數(shù)據(jù)庫設(shè)計與性能 |創(chuàng)建索引緩沖創(chuàng)建索引緩沖( (續(xù)續(xù)) )： | 把所有索引移至這個把所有索引移至這個32k32k的表空間中的表空間中 |該命令把所有索引移至這個該命令把所有索引移至這個32k32k的表空間而的表空間而 無需中斷當(dāng)