一 ROWID的概念
創(chuàng)新互聯(lián)建站是創(chuàng)新、創(chuàng)意、研發(fā)型一體的綜合型網(wǎng)站建設(shè)公司，自成立以來公司不斷探索創(chuàng)新，始終堅持為客戶提供滿意周到的服務(wù)，在本地打下了良好的口碑，在過去的十載時間我們累計服務(wù)了上千家以及全國政企客戶，如成都門窗定制等企業(yè)單位，完善的項目管理流程，嚴(yán)格把控項目進(jìn)度與質(zhì)量監(jiān)控加上過硬的技術(shù)實力獲得客戶的一致贊揚(yáng)。
存儲了row在數(shù)據(jù)文件中的具 *** 置 位編碼的數(shù)據(jù) A Z a z + 和 /
row在數(shù)據(jù)塊中的存儲方式
SELECT ROWID last_name FROM hr employees WHERE department_id = ;
比如 OOOOOOFFFBBBBBBRRR
OOOOOO data object number 對應(yīng)dba_objects data_object_id
FFF file# 對應(yīng)v$datafile file#
BBBBBB block#
RRR row#
Dbms_rowid包
SELECT dbms_rowid rowid_block_number( AAAGFqAABAAAIWEAAA ) from dual;
具體到特定的物理文件
二 索引的概念
類似書的目錄結(jié)構(gòu)
Oracle 的 索引 對象 與表關(guān)聯(lián)的可選對象 提高SQL查詢語句的速度
索引直接指向包含所查詢值的行的位置 減少磁盤I/O
與所索引的表是相互獨(dú)立的物理結(jié)構(gòu)
Oracle 自動使用并維護(hù)索引 插入 刪除 更新表后 自動更新索引
語法 CREATE INDEX index ON table (column[ column] );
B tree結(jié)構(gòu)(非bitmap)
[一]了解索引的工作原理
表 emp
目標(biāo) 查詢Frank的工資salary
建立索引 create index emp_name_idx on emp(name);
[試驗]測試索引的作用
運(yùn)行/rdbms/admin/utlxplan 腳本
建立測試表
create table t as select * from dba_objects;
insert into t select * from t;
create table indextable
as select rownum id owner object_name subobject_name
object_id data_object_id object_type created
from t;
set autotrace trace explain
set timing on
分析表 可以得到cost
查詢 object_name= DBA_INDEXES
在object_name列上建立索引
再查詢
[思考]索引的代價
插入 更新
三 唯一索引
何時創(chuàng)建 當(dāng)某列任意兩行的值都不相同
當(dāng)建立Primary Key(主鍵)或者Unique constraint(唯一約束)時 唯一索引將被自動建立
語法 CREATE UNIQUE INDEX index ON table (column);
演示
四 組合索引
何時創(chuàng)建 當(dāng)兩個或多個列經(jīng)常一起出現(xiàn)在where條件中時 則在這些列上同時創(chuàng)建組合索引
組合索引中列的順序是任意的 也無需相鄰 但是建議將最頻繁訪問的列放在列表的最前面
演示(組合列 單獨(dú)列)
五 位圖索引
何時創(chuàng)建
列中有非常多的重復(fù)的值時候 例如某列保存了 性別 信息
Where 條件中包含了很多OR操作符
較少的update操作 因為要相應(yīng)的跟新所有的bitmap
結(jié)構(gòu) 位圖索引使用位圖作為鍵值 對于表中的每一數(shù)據(jù)行位圖包含了TRUE( ) FALSE( ) 或NULL值
優(yōu)點(diǎn) 位圖以一種壓縮格式存放 因此占用的磁盤空間比標(biāo)準(zhǔn)索引要小得多
語法 CREATE BITMAP INDEX index ON table (column[ column] );
掩飾
create table bitmaptable as select * from indextable where owner in( SYS PUBLIC );
分析 查找 建立索引 查找
六 基于函數(shù)的索引
何時創(chuàng)建 在WHERE條件語句中包含函數(shù)或者表達(dá)式時
函數(shù)包括 算數(shù)表達(dá)式 PL/SQL函數(shù) 程序包函數(shù) SQL函數(shù) 用戶自定義函數(shù)
語法 CREATE INDEX index ON table (FUNCTION(column));
演示
必須要分析表 并且query_rewrite_enabled=TRUE
或者使用提示/*+ INDEX(ic_index)*/
七 反向鍵索引
目的 比如索引值是一個自動增長的列
多個用戶對集中在少數(shù)塊上的索引行進(jìn)行修改 容易引起資源的爭用 比如對數(shù)據(jù)塊的等待 此時建立反向索引
性能問題
語法
重建為標(biāo)準(zhǔn)索引 反之不行
八 鍵壓縮索引
比如表landscp的數(shù)據(jù)如下
site feature job
Britten Park Rose Bed Prune
Britten Park Rose Bed Mulch
Britten Park Rose Bed Spray
Britten Park Shrub Bed Mulch
Britten Park Shrub Bed Weed
Britten Park Shrub Bed Hoe
……
查詢時 以上 列均在where條件中同時出現(xiàn) 所以建立基于以上 列的組合索引 但是發(fā)現(xiàn)重復(fù)值很多 所以考慮壓縮特性
Create index zip_idx
on landscp(site feature job)
press ;
將索引項分成前綴(prefix)和后綴(postfix)兩部分 前兩項被放置到前綴部分
Prefix : Britten Park Rose Bed
Prefix : Britten Park Shrub Bed
實際所以的結(jié)構(gòu)為
Prune
Mulch
Spray
Mulch
Weed
Hoe
特點(diǎn) 組合索引的前綴部分具有非選擇性時 考慮使用壓縮 減少I/O 增加性能
九 索引組織表(IOT)
將表中的數(shù)據(jù)按照索引的結(jié)構(gòu)存儲在索引中 提高查詢速度
犧牲插入更新的性能 換取查詢性能 通常用于數(shù)據(jù)倉庫 提供大量的查詢 極少的插入修改工作
必須指定主鍵 插入數(shù)據(jù)時 會根據(jù)主鍵列進(jìn)行B樹索引排序 寫入磁盤
十 分區(qū)索引
簇:
A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together
lishixinzhi/Article/program/Oracle/201311/17769
Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4
oracle 索引什么時候重建和重建方法討論
分類：數(shù)據(jù)庫技術(shù) 字號： 大大中中小小 索引什么時候需要重建和重建的方法
一提到索引，大家都知道，但是怎樣建索引，什么時候重建索引，重建索引用什么方法，可能有的就不太清楚了，我根據(jù)一些資料簡單的整理一點(diǎn)，如果哪里不對或是不妥請大家指點(diǎn)，希望大家有更好經(jīng)驗也share出來。
索引的目的是為了加快尋找數(shù)據(jù)的速度，但是如果對表經(jīng)常做改動，則索引也會相應(yīng)改動，時間長了，查詢速度的效率就會降低，就有可能要重建索引，那么什么時候需要重建索引和用什么方法重建索引可能是大家關(guān)心的。
一. 索引在內(nèi)部進(jìn)行自身的管理以確保對數(shù)據(jù)行的快速訪問。但是數(shù)據(jù)表中大量的活動會導(dǎo)致oracle索引動態(tài)地對自身的進(jìn)行重新配置，這些配置包括三個方面：
1．索引分割
當(dāng)新數(shù)據(jù)行產(chǎn)生的索引節(jié)點(diǎn)要建立在現(xiàn)有級別上時，出現(xiàn)此動作。
2．索引生成
在某些位置，索引達(dá)到此級索引的最大容量的時候，就會生成更深一級的索引結(jié)構(gòu)。
3．索引節(jié)點(diǎn)的刪除
你可能了解到，刪除表中的數(shù)據(jù)行后，索引中相應(yīng)的節(jié)點(diǎn)不會從物理意義上刪除，也沒有從索引中刪除此項目。而是從邏輯上刪除此索引項目，并在索引樹中留下了一個“死“節(jié)點(diǎn)，當(dāng)索引刪除了葉節(jié)點(diǎn)或是生成了過深的的級別層次后，就需要進(jìn)行重建。
二 索引的種類：
a.B-tree（B樹）索引
b.壓縮B樹索引
c.Bitmap（位圖）索引
d.函數(shù)索引
e.Reverse Key Index（反向鍵索引）
f.Index Organized Table（索引組織表）
三 下面分別對各種索引進(jìn)行說明
在進(jìn)行介紹前先說明幾個術(shù)語：
高基數(shù)：簡單理解就是表中列的不同值多
低基數(shù)：建單理解就是表中的列的不同值少
以刪除的葉節(jié)點(diǎn)數(shù)量：指得是數(shù)據(jù)行的delete操作從邏輯上刪除的索引節(jié)點(diǎn)的數(shù)量，要記住oracle在刪除數(shù)據(jù)行后，將“死“節(jié)點(diǎn)保留在索引中，這樣做可以加快sql刪除操作的速度，因此oracle刪除數(shù)據(jù)行后可以不必重新平衡索引。
索引高度：索引高度是指由于數(shù)據(jù)行的插入操作而產(chǎn)生的索引層數(shù)，當(dāng)表中添加大量數(shù)據(jù)時，oracle將生成索引的新層次以適應(yīng)加入的數(shù)據(jù)行，因此， oracle索引可能有4層，但是這只會出現(xiàn)在索引數(shù)中產(chǎn)生大量插入操作的區(qū)域。Oracle索引的三層結(jié)構(gòu)可以支持?jǐn)?shù)百萬的項目，而具備4層或是更多層的需要重建。
每次索引訪問的讀取數(shù)：是指利用索引讀取一數(shù)據(jù)行時所需要的邏輯I/O操作數(shù)，邏輯讀取不必是物理讀取，因為索引的許多內(nèi)容已經(jīng)保存在數(shù)據(jù)緩沖區(qū)，然而，任何數(shù)據(jù)大于10的索引都需要重建。
1. B-tree（B樹）索引
是現(xiàn)代關(guān)系型數(shù)據(jù)庫中最常用的索引。除了存儲索引數(shù)據(jù)外，還存儲一個行ID，用來指出該行其余數(shù)據(jù)存儲在這個被索引表中的什么地方。該索引以一種數(shù)結(jié)構(gòu)格式存儲這些值。
Oracle建議如果表經(jīng)過排序，當(dāng)返回40%一下的數(shù)據(jù)時使用索引，如果高于40%則使用全表掃描，如果沒有經(jīng)過排序，則當(dāng)返回7%以下時，使用索引?？幢硎欠衽判?，可以看dba_indexes字典中的CLUSTERING_FACTOR列，如果與表占用的數(shù)據(jù)塊數(shù)相近，則經(jīng)過了排序，如果與行數(shù)相近，則沒有排序。那么什么時候重建呢？我們可以利用analyze index …….. compute statistics 對表進(jìn)行分析。然后察看dba_indexes中的blevel。這列是說明索引從根塊到葉快的級別，或是深度。如果級別大于等于4。則需要重建，如下：
Select index_name,blevel from dba_indexeswhere blevel=4.
另一個從重建中受益的指標(biāo)顯然是當(dāng)該索引中的被刪除項占總的項數(shù)的百分比。如果在20%以上時，也應(yīng)當(dāng)重建，如下
SQLanlyze index ------ validatestructure
SQLselect(del_lf_rows_len/lf_rows_len)*100 from index_stats where 刪除并從頭開始建立索引。
b. 使用alter index -------- rebuild 命令重建索引
c. 使用alter index -------- coalesce命令重建索引。
下面討論一下這三種方法的優(yōu)缺點(diǎn)：
1).刪除并從頭開始建索引：方法是最慢的，最耗時的。一般不建議。
2).Alter index ---- rebuild 快速重建索引的一種有效的辦法，因為使用現(xiàn)有索引項來重建新索引，如果客戶操作時有其他用戶在對這個表操作，盡量使用帶online參數(shù)來最大限度的減少索引重建時將會出現(xiàn)的任何加鎖問題，alter index ------- rebuild online.但是,由于新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁盤空間可臨時使用，當(dāng)索引建完后把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引以到新的表空間。
Alter index ------ rebuild tablespace -----。
這個命令的執(zhí)行步驟如下：
首先，逐一讀取現(xiàn)有索引，以獲取索引的關(guān)鍵字。
其次，按新的結(jié)構(gòu)填寫臨時數(shù)據(jù)段。
最后，一旦操作成功，刪除原有索引樹，降臨時數(shù)據(jù)段重命名為新的索引。
需要注意的是alterindex ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現(xiàn)有索引相同的表空間進(jìn)行。
3).alter index ----- coalesce 使用帶有coalesce參數(shù)時重建期間不需要額外空間，它只是在重建索引時將處于同一個索引分支內(nèi)的葉塊拼合起來，這最大限度的減少了與查詢過程中相關(guān)的潛在的加鎖問題，但是，coalesce選項不能用來講一個索引轉(zhuǎn)移到其他表空間。
2．壓縮B樹索引
當(dāng)B樹索引基于大表時，尤其是當(dāng)基于數(shù)據(jù)倉庫或決策支持系統(tǒng)中的大表時，這些索引會耗費(fèi)大量的存儲空間，壓縮（compressed）B樹索引用來最大限度的減少某些類型的B樹索引使用的空間。當(dāng)一個B樹索引得到壓縮時，被索引的獵的重復(fù)出現(xiàn)就被消除掉，進(jìn)而減少了存儲索引的總的存儲空間。例如：
壓縮前：smith每次出現(xiàn)還要存儲它的相關(guān)的rowid.
姓 關(guān)聯(lián)rowid
smith AAABSOAAEAAAABTAAB
smith AAABSOAAEAAAABTAAC
smith AAABSOAAEAAAABTAAD
壓縮后:smith項和rowid指存儲一次。
smith AAABSOAAEAAAABTAAB,AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB
創(chuàng)建方法：
SQLcreate index index_name ontable_name(column_name)
tablespace tablespace_name
compress;
另一種方法：
SQLalter index index_name rebuildcompress;
3． itmap(位圖)索引。
B樹索引在數(shù)據(jù)具有高基數(shù)的列工作的最好，對于低基數(shù)的列，位圖索引可能是更有效的選擇。位圖索引創(chuàng)建表行的一個二進(jìn)制映像，并把映像存儲在索引塊中，這種類型的索引的DML操作少，長度大并且含有極少不同的值得列特別有用。位圖索引不應(yīng)當(dāng)用在頻繁發(fā)生insert,update,delete操作的表上，這些dml操作在性能方面的代價很高，因為，他們會引起位圖級的加鎖發(fā)生，而且要求動態(tài)的重建所有可能值的位圖。為圖索引最適合數(shù)據(jù)倉庫和決策支持系統(tǒng)。
4．基于函數(shù)的索引
當(dāng)把一個函數(shù)運(yùn)用于被索引的列上時，該列德索引都變得無效，基于函數(shù)的索引就是為了解決這個問題。
5．反向鍵索引
是一種特殊類型的B樹索引，在索引基于含有序數(shù)的列時使非常有用的，如果一個傳統(tǒng)的B樹索引基于一個含有這種數(shù)據(jù)的列，往往會產(chǎn)生許多級，由于B樹索引有 4級以上的深度會降低性能，因此反向鍵索引更適合這種類型，反向鍵索引通過簡單的煩象被索引的列中的數(shù)據(jù)來解決問題，他首先反向每個列鍵值的字節(jié)，然后在反向后的新數(shù)據(jù)上進(jìn)行索引，而新數(shù)據(jù)在值的范圍上的分布通常比原來的有序數(shù)更均勻。
6．索引組織表
由于B樹、位圖、反向鍵索引的使用而引起的性能將會導(dǎo)致這樣的事實，這些索引中的項目直接指向索引基表中對應(yīng)數(shù)據(jù)的行ID,這是從表行沒有按任何特定的順序來物理地存儲表中檢索表行的一種有效方法，這種表叫做堆表，oracle大多數(shù)表中以一種堆疊方式存儲行數(shù)據(jù)，因為行以一種或多或少的隨機(jī)方式被分配給表內(nèi)的塊，之所以出現(xiàn)這種隨機(jī)性，是因為oracle在決定把一個行存儲在何處時并不考慮改行的內(nèi)容，oracle只是把該行存儲在它從該表的freelist 上所發(fā)現(xiàn)的第一個塊中。
如果希望按一種指定順序來存儲一個表的數(shù)據(jù)，就不能使用堆表，為此oracle提供了索引組織表，索引組織表不是存儲一個指向行數(shù)據(jù)的其余部分存儲在了何處的行的ID指針，而是把行數(shù)據(jù)全部存儲在索引本身內(nèi)，這產(chǎn)生了兩個性能好處：
n 表行按索引順序來存儲。
n 使用B樹索引時引起的先讀取索引后讀取表鎖使用的額外I/O操作得到消除。
例如：
sqlcreate table emp
(last_name varchar2(9) primary key,
first_name varchar2(9),
hire_date date)
organization index tablespace users
pctthreshold 25
including first name
overflow tablespace qyl
mapping table;
所有索引組織表在將要作為索引基礎(chǔ)的那一列上都必須有一個主鍵約束，索引組織表不能含有唯一性約束或是被聚簇。
下面說明各個參數(shù)的含義：
organization index：說明該表是索引組織表
pctthreshold :指定整個數(shù)據(jù)塊的什么百分比要保持打開，以便存儲一個與主鍵值相關(guān)聯(lián)的行數(shù)據(jù)，其中主鍵值必須在0到50之間（50是默認(rèn)值）
including : 指定在行長度超過pctthershold中所設(shè)置的大小時按那一列 把行分解成兩段
overflow tablespace ：指定在行長度超過pctthreshold中設(shè)置的大小時行數(shù)的的另一部分存儲到的表空間。
Mapping table:致使在創(chuàng)建索引組織表的位圖索引時所必需的一個關(guān)聯(lián)映像表的創(chuàng)建。
以上是我根據(jù)一些資料對索引的一個簡單闡述，大家可能有不同的見解，希望對大家有幫助，那些不妥的地方還希望大家提出來。
參考資料：ocp困惑racle9i性能調(diào)整
oracle statspack 高性能調(diào)整技術(shù)
[@more@]
analyze index t_id_ind validate structure
select (del_lf_rows_len/lf_rows_len)*100 from index_stats
20%
b. 使用alter index t_id_ind rebuild 命令重建索引
c. 使用alter index t_id_ind coalesce命令重建索引。
alter indext_id_ind rebuild online.
但是,由于新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁盤空間可臨時使用，當(dāng)索引建完后把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引以到新的表空間。
Alter index ------ rebuild tablespace -----。
這個命令的執(zhí)行步驟如下：
首先，逐一讀取現(xiàn)有索引，以獲取索引的關(guān)鍵字。
其次，按新的結(jié)構(gòu)填寫臨時數(shù)據(jù)段。
最后，一旦操作成功，刪除原有索引樹，降臨時數(shù)據(jù)段重命名為新的索引。
需要注意的是alter index ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現(xiàn)有索引相同的表空間進(jìn)行
alter index ----- coalesce 使用帶有coalesce參數(shù)時重建期間不需要額外空間，它只是在重建索引時將處于同一個索引分支內(nèi)的葉塊拼合起來，這最大限度的減少了與查詢過程中相關(guān)的潛在的加鎖問題，但是，coalesce選項不能用來講一個索引轉(zhuǎn)移到其他表空間
這是不行的，你說的那個2是前綴列，只能是從頭往后數(shù)，不能是中間
對于唯一索引，取值范圍是1-索引關(guān)鍵字減數(shù)量1，缺省是索引關(guān)鍵字減數(shù)量1
對于非唯一索引，取值范圍是1-索引關(guān)鍵字減數(shù)量，缺省是索引關(guān)鍵字減數(shù)量
就是壓縮兩列的意思，你那個例子就是壓縮ename,esex這兩例
使用Oracle索引壓縮技術(shù),減少空間占用,并提高大數(shù)據(jù)量訪問情況下的速度.
索引壓縮僅用于復(fù)合索引,即多個字段建立一個索引的情況,通過compress參數(shù)指定壓縮哪些字段.
雖然壓縮后的索引,相對來說需要花費(fèi)更多的CPU時間來處理,
但是,這樣做后,可以在高速緩沖區(qū)中緩存更多的索引塊,當(dāng)大范圍的掃描時,能夠減少物理IO的數(shù)量.
重慶分公司
重慶分公司
