一、 ROWID的概念

存儲(chǔ) 了row在數(shù)據(jù)文件中的具體位置：64位編碼的數(shù)據(jù)，A-Z, a-z, 0-9, +, 和 /，

　　row在數(shù)據(jù)塊中的 存儲(chǔ) 方式

　　SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;

　　比如：OOOOOOFFFBBBBBBRRR

　　OOOOOO：data object number, 對(duì)應(yīng)dba_objects.data_object_id

　　FFF：file#, 對(duì)應(yīng)v$datafile.file#

　　BBBBBB：block#

　　RRR：row#

　　Dbms_rowid包

　　SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;

　　具體到特定的物理文件

二、 索引的概念

　　1、 類似書的目錄結(jié)構(gòu)

　　2、 Oracle 的“索引”對(duì)象，與表關(guān)聯(lián)的可選對(duì)象，提高SQL查詢語(yǔ)句的速度

　　3、 索引直接指向包含所查詢值的行的位置，減少磁盤I/O

　　4、 與所索引的表是相互獨(dú)立的物理結(jié)構(gòu)

　　5、 Oracle 自動(dòng)使用并維護(hù)索引，插入、刪除、更新表后，自動(dòng)更新索引

--工作感覺中 所謂oracle自動(dòng)維護(hù)在插入后，其實(shí)好像并非如此！不是oracle 獨(dú)立的進(jìn)程在 插入后開始維護(hù)

而是在插入這個(gè)SESSION中維護(hù)，在insert into commit; 語(yǔ)句后維護(hù)。

　　6、 語(yǔ)法：CREATE INDEX index ON table (column[, column]...);

　　7、 B-tree結(jié)構(gòu)(非bitmap)：

　　[一]了解索引的工作原理：

　　表：emp

　　目標(biāo)：查詢Frank的工資salary

　　建立索引：create index emp_name_idx on emp(name);

以下圖說明節(jié)點(diǎn)和葉節(jié)點(diǎn)的關(guān)系，節(jié)點(diǎn)負(fù)責(zé)二叉樹的路徑，如同書目錄左邊的目錄列表 ；

葉節(jié)點(diǎn)就是目錄列表右邊的頁(yè)碼

索引也會(huì)很大化 因?yàn)樗惨鎯?chǔ)些字段和字段的值

三、 唯一索引

　　1、 何時(shí)創(chuàng)建：當(dāng)某列任意兩行的值都不相同

　　2、 當(dāng)建立Primary Key(主鍵)或者Unique constraint(唯一約束)時(shí)，唯一索引將被自動(dòng)建立

　　3、 語(yǔ)法：CREATE UNIQUE INDEX index ON table (column);

　　4、 演示

四、 組合索引

　　1、 何時(shí)創(chuàng)建：當(dāng)兩個(gè)或多個(gè)列經(jīng)常一起出現(xiàn)在where條件中時(shí)，則在這些列上同時(shí)創(chuàng)建組合索引

　　2、 組合索引中列的順序是任意的，也無(wú)需相鄰。但是建議將最頻繁訪問的列放在列表的最前面

　　3、 演示(組合列，單獨(dú)列)

　五、 位圖索引

　　1、 何時(shí)創(chuàng)建：

　　列中有非常多的重復(fù)的值時(shí)候。例如某列保存了 “性別”信息。

　　Where 條件中包含了很多OR操作符。

　　較少的update操作，因?yàn)橐鄳?yīng)的跟新所有的bitmap

　　2、 結(jié)構(gòu)：位圖索引使用位圖作為鍵值，對(duì)于表中的每一數(shù)據(jù)行位圖包含了TRUE(1)、FALSE(0)、或NULL值。

　　3、 優(yōu)點(diǎn)：位圖以一種壓縮格式存放，因此占用的磁盤空間比標(biāo)準(zhǔn)索引要小得多

　　4、 語(yǔ)法：CREATE BITMAP INDEX index ON table (column[, column]...);

　　5、 掩飾：

　　create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');

　　分析，查找，建立索引，查找

　六、 基于函數(shù)的索引

　　1、 何時(shí)創(chuàng)建：在WHERE條件語(yǔ)句中包含函數(shù)或者表達(dá)式時(shí)

　　2、 函數(shù)包括：算數(shù)表達(dá)式、PL/SQL函數(shù)、程序包函數(shù)、SQL函數(shù)、用戶自定義函數(shù)。

　　3、 語(yǔ)法：CREATE INDEX index ON table (FUNCTION(column));

　　4、 演示

　　必須要分析表，并且query_rewrite_enabled=TRUE

　　或者使用提示/*+ INDEX(ic_index)*/

七、 反向鍵索引

　　目的：比如索引值是一個(gè)自動(dòng)增長(zhǎng)的列：

　　多個(gè)用戶對(duì)集中在少數(shù)塊上的索引行進(jìn)行修改，容易引起資源的爭(zhēng)用，比如對(duì)數(shù)據(jù)塊的等待。此時(shí)建立反向索引。

　　性能問題：

　　語(yǔ)法：

　　重建為標(biāo)準(zhǔn)索引：反之不行

　八、 鍵壓縮索引

　　比如表landscp的數(shù)據(jù)如下：

　　site feature job

　　Britten Park, Rose Bed 1, Prune

　　Britten Park, Rose Bed 1, Mulch

　　Britten Park, Rose Bed 1,Spray

　　Britten Park, Shrub Bed 1, Mulch

　　Britten Park, Shrub Bed 1, Weed

　　Britten Park, Shrub Bed 1, Hoe

　　……

　　查詢時(shí)，以上3列均在where條件中同時(shí)出現(xiàn)，所以建立基于以上3列的組合索引。但是發(fā)現(xiàn)重復(fù)值很多，所以考慮壓縮特性。

　　Create index zip_idx

　　on landscp(site, feature, job)

　　compress 2;

　　將索引項(xiàng)分成前綴(prefix)和后綴(postfix)兩部分。前兩項(xiàng)被放置到前綴部分。

　　Prefix 0: Britten Park, Rose Bed 1

　　Prefix 1: Britten Park, Shrub Bed 1

　　實(shí)際所以的結(jié)構(gòu)為：

　　0 Prune

　　0 Mulch

　　0 Spray

　　1 Mulch

　　1 Weed

　　1 Hoe

　　特點(diǎn)：組合索引的前綴部分具有非選擇性時(shí)，考慮使用壓縮。減少I/O,增加性能。

　　九、 索引組織表(IOT)

　　將表中的數(shù)據(jù)按照索引的結(jié)構(gòu)存儲(chǔ)在索引中，提高查詢速度。

　　犧牲插入更新的性能，換取查詢性能。通常用于數(shù)據(jù)倉(cāng)庫(kù)，提供大量的查詢，極少的插入修改工作。

　　必須指定主鍵。插入數(shù)據(jù)時(shí)，會(huì)根據(jù)主鍵列進(jìn)行B樹索引排序，寫入磁盤。

　十、 分區(qū)索引

語(yǔ)法：

    
      Table Index
      

      

    
    

    
   CREATE [UNIQUE|BITMAP] INDEX [schema.]
    
      index_name
    
    

    

    

    
ON [schema.]
    
      table_name
    
    

    
 [tbl_alias]
    

    

    
(
    
      col
    
    

    
 [ASC | DESC]) 
    
      
        
          index_clause
        
        

      
      

      
 index_attribs
      

      

    
    

  

index_clauses:
分以下兩種情況

1. Local Index

       就是索引信息的存放位置依賴于父表的Partition信息，換句話說創(chuàng)建這樣的索引必須保證父表是Partition
  

    1.1 索引信息存放在父表的分區(qū)所在的表空間。但是僅可以創(chuàng)建在父表為HashTable或者composite分區(qū)表的。
  

        LOCAL STORE IN (
    
      tablespace
    
    

    
)
  

    1.2 僅可以創(chuàng)建在父表為HashTable或者composite分區(qū)表的。并且指定的分區(qū)數(shù)目要與父表的分區(qū)數(shù)目要一致
    

    

    
      LOCAL STORE IN
    
    

    
 (tablespace)
    

    

    
 (
    
      PARTITION
    
    

    
 [partition
    

    

    
 [
    
      LOGGING|NOLOGGING
    
    

    
]
    

    

    
 [
    
      TABLESPACE
    
    

    
 {tablespace|DEFAULT}]
    

    

    
 [
    
      PCTFREE
    
    

    
 int]
    

    

    
 [
    
      PCTUSED
    
    

    
 int]
    

    

    
 [
    
      INITRANS
    
    

    
 int]
    

    

    
 [
    
      MAXTRANS
    
    

    
 int]
    

    

    
 [
    
      STORAGE
    
    

    
 storage_clause]
    

    

    
 [
    
      STORE IN
    
    

    
 {tablespace_name|
    
      DEFAULT
    
    

    
]
    

    

    
 [
    
      SUBPARTITION
    
    

    
 [subpartition [
    
      TABLESPACE
    
    

    
 tablespace]]]])
  

  
    1.3 索引信息存放在父表的分區(qū)所在的表空間，這種語(yǔ)法最簡(jiǎn)單，也是最常用的分區(qū)索引創(chuàng)建方式。
  
  
    
      Local
      

      

    
    

    

    

    
1.4 并且指定的Partition 數(shù)目要與父表的Partition要一致
    

    

    
      LOCAL (PARTITION
    
    

    
 [
    
      partition
    
    

    

    

    
[
    
      LOGGING|NOLOGGING
    
    

    
]
    

    

    
[
    
      TABLESPACE
    
    

    
 {
    
      tablespace
    
    

    
|DEFAULT}]
    

    

    
[
    
      PCTFREE
    
    

    
      int
    
    

    
]
    

    

    
[
    
      PCTUSED
    
    

    
      int
    
    

    
]
    

    

    
[
    
      INITRANS
    
    

    
      int
    
    

    
]
    

    

    
[
    
      MAXTRANS
    
    

    
      int
    
    

    
]
    

    

    
[
    
      STORAGE
    
    

    
      storage_clause
    
    

    
]
    

    

    
[
    
      STORE IN
    
    

    
 {
    
      tablespace_name
    
    

    
|
    
      DEFAULT
    
    

    
]
    

    

    
[
    
      SUBPARTITION
    
    

    
 [
    
      subpartition
    
    

    
 [TABLESPACE 
    
      tablespace
    
    

    
]]]])
    

    

  
  

    Global Index
  
  
      索引信息的存放位置與父表的Partition信息完全不相干。甚至父表是不是分區(qū)表都無(wú)所謂的。語(yǔ)法如下：
    
      

      

    
    

    
GLOBAL PARTITION BY RANGE (col_list)
    

    

    
( PARTITION 
    
      partition
    
    

    
 VALUES LESS THAN (
    
      value_list
    
    

    
)
    

    

    
[LOGGING|NOLOGGING]
    

    

    
[TABLESPACE {
    
      tablespace
    
    

    
|DEFAULT}]
    

    

    
[PCTFREE 
    
      int
    
    

    
]
    

    

    
[PCTUSED 
    
      int
    
    

    
]
    

    

    
[INITRANS 
    
      int
    
    

    
]
    

    

    
[MAXTRANS 
    
      int
    
    

    
]
    

    

    
[STORAGE 
    
      storage_clause
    
    

    
] )
  
  
    但是在這種情況下，如果父表是分區(qū)表，要?jiǎng)h除父表的一個(gè)分區(qū)都必須要更新Global Index ,否則索引信息不正確
    

    

    
      
        ALTER TABLE
      
      

      
        
          TableName
        
        

      
      

      
        
          DROP PARTITION
        
        

        
 PartitionName
      
      

      
      
      

      
        Update Global Indexes
      
      

    
    

    
分區(qū)表、分區(qū)索引和全局索引：
    

  
  

    　　在一個(gè)表的數(shù)據(jù)超過過2000萬(wàn)條或占用2G空間時(shí)，建議建立分區(qū)表。
  
  

    

      

        

           createtableta(c1int,c2varchar2(16),c3varchar2(64),c4intconstraintpk_taprimarykey(c1))partitionbyrange(c1)(partitionp1valueslessthan(10000000),partitionp2valueslessthan(20000000),partitionp3valueslessthan(30000000),partitionp4valueslessthan(maxvalue));
        
      
    
  
  

    　　分區(qū)索引和全局索引：
  
  

    　　分區(qū)索引就是在所有每個(gè)區(qū)上單獨(dú)創(chuàng)建索引，它能自動(dòng)維護(hù)，在drop或truncate某個(gè)分區(qū)時(shí)不影響該索引的其他分區(qū)索引的使用，也就是索引不會(huì)失效，維護(hù)起來比較方便，但是在查詢性能稍微有點(diǎn)影響。
  
  

    

      

        

           createindexidx_ta_c2onta(c2)local(partitionp1,partitionp2,partitionp3,partitionp4);或 者createindexidx_ta_c2onta(c2)local;
        
      
    
  
  

    　　另外在create unique index idx_ta_c2 on ta(c2) local ;系統(tǒng)會(huì)報(bào)ORA-14039錯(cuò)誤，這是因?yàn)閠a表的分區(qū)列是c1，oracle不支持在分區(qū)表上創(chuàng)建PK主鍵時(shí)主鍵列不包含分區(qū)列，創(chuàng)建另外的約束 (unique)也不可以。
  
  

    　　全局索引就是在全表上創(chuàng)建索引，它可以創(chuàng)建自己的分區(qū)，可以和分區(qū)表的分區(qū)不一樣，也就是它是獨(dú)立的索引。在drop或truncate某個(gè)分 區(qū)時(shí)需要?jiǎng)?chuàng)建索引alter index idx_xx rebuild，也可以alter table table_name drop partition partition_name update global indexes;實(shí)現(xiàn)，但是要花很長(zhǎng)時(shí)間在重建索引上。可以通過查詢user_indexes、user_part_indexes和 user_ind_partitions視圖來查看索引是否有效。
  
  

    

      

        

           createindexidx_ta_c3onta(c3);
        
      
    
  
  

    　　或者把全局索引分成多個(gè)區(qū)(注意和分區(qū)表的分區(qū)不一樣)：
  
  

    

      

        

           createindexidx_ta_c4onta(c4)globalpartitionbyrange(c4)(partitionip1valueslessthan(10000),partitionip2valueslessthan(20000),partitionip3valueslessthan(maxvalue));
        
      
    
  
  

    　　注意索引上的引導(dǎo)列要和range后列一致，否則會(huì)有ORA-14038錯(cuò)誤。
  
  

    　　oracle會(huì)對(duì)主鍵自動(dòng)創(chuàng)建全局索引
  
  

    　　如果想在主鍵的列上創(chuàng)建分區(qū)索引，除非主鍵包括分區(qū)鍵，還有就是主鍵建在兩個(gè)或以上列上。
  
  

    　　在頻繁刪除表的分區(qū)且數(shù)據(jù)更新比較頻繁時(shí)為了維護(hù)方便避免使用全局索引。
  
  
  

    
      Oracle數(shù)據(jù)庫(kù)中的索引詳解