播布客視訊-Managing Indexes筆記

zhanglei_itput發表於2010-02-09
Index筆記

 

播布客視訊第48-51講：Chapter 12--Managing Indexes
 
一、index type
1. logical
    single column & concatenated(符合index)
    unique or nonunique
    function-based
    domain index(外部資料,oracle提供介面)
2. physical
    partitioned or nonpartitoned
    B-tree:Normal or reverse key
    Bitmap

二、 B-tree index(Balance-tree index)平衡樹index(1:1)
     樹形結構，平衡樹會導致樹的高度不會太高
     root:the store info of data,一個範圍值
     branch:the store info of data一個範圍值
     leaf:block true data。雙向連結串列
     如：當select * from t where id>23 and id < 45
             根據index最小值，從root節點找到leaf block，然後在leaf連結串列，做一次index range scan。即不再查詢root節點，當找到第一個葉子節點時，進行index range scan
     B-tree特點：index entry與data 一一對應。
     leaf 級：index entry = index entry header + key column length + key column value + rowid
                                         = 控制資訊,多事物槽位控制 + index長度 + index值 + 指標(資料檔案，資料塊)
     對錶進行insert ， delete時，oracle自動維護index，
     index目的為了查詢快，但是會導致insert,delete,update很慢。所以在保證查詢效能達到一定級別的時候，儘可能的少用index。
    
三、 Bitmap index(distinct value very 少)
     每一個葉子節點代表一個鍵值(distinct vale)
     Bitmap index = key + start rowid + end rowid + bitmap
                              = distinct value + blockid.xx.fileid + blockid.xx.fileid + 二進位制數值

四、 B-tree PK Bitmap
     B-tree                                                   Bitmap
     ----------                                               ------------
     high-cardinality                                  low-cardinality
     update on keys inexpensive           update to key columns very expensive
     inefficitet for OR queries                 efficitet for OR queries
     useful for OLTP                                useful for data warehousing or OLAP
    
五、 PCTFREE 30
     block中的剩餘空間<30時候，不可以再插入了，pctfree為了預留空間為了update語句
     pctused在index中是沒有用的。因為index是排序的。
    
六、 建立index的指導方針
           1.需要根據具體業務邏輯，平衡查詢和dml操作的需要
           2.表和表的index需要放到不同的tablespace，因為table和index幾乎是同時查詢同時維護的。
           3.最好使用一致性的extent size，e.g 5個塊的整數倍 一次IO
           4.考慮NOLOGGING for large indexes when create index.
           5.INITRANS,允許對一個塊中最多的併發事務個數，index的initrans>table的initrans值
    
七、 REBUILD INDEX
           delete recode in table -> index標記已刪除，但是不真實刪除，除非100%的index entry都標記為刪除，block才可用。
            update recode in table -> index標記已刪除，insert new index entry
            所以會導致空間浪費，影響效率，需要rebuild，重新建立新的index。
            offline index rebuild原理
                       1. lock the table(不能增刪改)
                       2. create new temp index by reading old index
                       3. drop the old index
                      4. rename the temp index to original index
                      5. remove the table lock.
            online index rebuild原理
                     1. lock the table
                     2. create new temp and empty index and an IOT to stroe on-going DML
                     3. Release the table lock;
                     4. create new temp index by reading old index
                     5. Merge contents of the IOT in with the new index
                     6. lock the table
                     7. final merge from IOT and drop the old index
                     8. rename the temp index to original index
                     9. remove the table lock.
            rebuild index 情況
                    1. move to diff tablespace
                    2. index contains many deleted entries.
                    3. an existing normal index must be converted into a reverse key index.
                    4. the table has been moved to another tablespace,need rebuild index
  
八、 COALESCING INDEX
           ALTER INDEX XXX COALESCE;
           合併空餘空間
           1. Scan alone the base(leaf) of the index.
           2. where adjacent nodes(相鄰的節點) can be combined into a single node.
          通常來說COALESCING INDEX比REBUILD INDEX 要快，但是他們的場合還需要具體問題具體分析。
          1. 如果index中90%的好的，只有10%是碎片，所以可以考慮用COALESCING INDEX
          2. 如果index已經支離破碎了，幾乎每個葉節點的block都有碎片的話，那麼就需要用REBUILD INDEX
    
九、 CHECKING INDEX VALIDITY
           AYALYZE INDEX XXX VALIDATE STRUCTURE;
           分析index的有效性，然後把結果放到INDEX_STATS表中
           select height, name , lf_rows, lf_blks, del_lf_rows from index_stats
           如果DEL_LF_ROWS/LF_ROWS>=15%,可能需要重建index
  
十、 DROP INDEXES
           DROP INDEX XXX
           刪除index的情況：
           1. 往表裡面批量imp資料前，需要先刪除index，然後imp，然後create index
           2. drop 不經常使用的index
           3. drop and re-create invalid index
               index rebulid 的速度快於 drop and re-create index ，因為前者是讀old index，後者是從資料表中讀取資料。
   
十一、 Identifying Unused index
               ALTER INDEX XXX   MONITORING USAGE;--v$object_usage
               ALTER INDEX XXX NOMONITORING USAGE;
               select * from v$object_usage;
    
十二、Getting index information
            1. DBA_INDEXES
            2. DBA_IND_COLUMNS
            3. v$object_usage

參考連結：

 

播布客視訊第48-51講：Chapter 12--Managing Indexes
 
一、index type
1. logical
    single column & concatenated(符合index)
    unique or nonunique
    function-based
    domain index(外部資料,oracle提供介面)
2. physical
    partitioned or nonpartitoned
    B-tree:Normal or reverse key
    Bitmap

二、 B-tree index(Balance-tree index)平衡樹index(1:1)
     樹形結構，平衡樹會導致樹的高度不會太高
     root:the store info of data,一個範圍值
     branch:the store info of data一個範圍值
     leaf:block true data。雙向連結串列
     如：當select * from t where id>23 and id < 45
             根據index最小值，從root節點找到leaf block，然後在leaf連結串列，做一次index range scan。即不再查詢root節點，當找到第一個葉子節點時，進行index range scan
     B-tree特點：index entry與data 一一對應。
     leaf 級：index entry = index entry header + key column length + key column value + rowid
                                         = 控制資訊,多事物槽位控制 + index長度 + index值 + 指標(資料檔案，資料塊)
     對錶進行insert ， delete時，oracle自動維護index，
     index目的為了查詢快，但是會導致insert,delete,update很慢。所以在保證查詢效能達到一定級別的時候，儘可能的少用index。
    
三、 Bitmap index(distinct value very 少)
     每一個葉子節點代表一個鍵值(distinct vale)
     Bitmap index = key + start rowid + end rowid + bitmap
                              = distinct value + blockid.xx.fileid + blockid.xx.fileid + 二進位制數值

四、 B-tree PK Bitmap
     B-tree                                                   Bitmap
     ----------                                               ------------
     high-cardinality                                  low-cardinality
     update on keys inexpensive           update to key columns very expensive
     inefficitet for OR queries                 efficitet for OR queries
     useful for OLTP                                useful for data warehousing or OLAP
    
五、 PCTFREE 30
     block中的剩餘空間<30時候，不可以再插入了，pctfree為了預留空間為了update語句
     pctused在index中是沒有用的。因為index是排序的。
    
六、 建立index的指導方針
           1.需要根據具體業務邏輯，平衡查詢和dml操作的需要
           2.表和表的index需要放到不同的tablespace，因為table和index幾乎是同時查詢同時維護的。
           3.最好使用一致性的extent size，e.g 5個塊的整數倍 一次IO
           4.考慮NOLOGGING for large indexes when create index.
           5.INITRANS,允許對一個塊中最多的併發事務個數，index的initrans>table的initrans值
    
七、 REBUILD INDEX
           delete recode in table -> index標記已刪除，但是不真實刪除，除非100%的index entry都標記為刪除，block才可用。
            update recode in table -> index標記已刪除，insert new index entry
            所以會導致空間浪費，影響效率，需要rebuild，重新建立新的index。
            offline index rebuild原理
                       1. lock the table(不能增刪改)
                       2. create new temp index by reading old index
                       3. drop the old index
                      4. rename the temp index to original index
                      5. remove the table lock.
            online index rebuild原理
                     1. lock the table
                     2. create new temp and empty index and an IOT to stroe on-going DML
                     3. Release the table lock;
                     4. create new temp index by reading old index
                     5. Merge contents of the IOT in with the new index
                     6. lock the table
                     7. final merge from IOT and drop the old index
                     8. rename the temp index to original index
                     9. remove the table lock.
            rebuild index 情況
                    1. move to diff tablespace
                    2. index contains many deleted entries.
                    3. an existing normal index must be converted into a reverse key index.
                    4. the table has been moved to another tablespace,need rebuild index
  
八、 COALESCING INDEX
           ALTER INDEX XXX COALESCE;
           合併空餘空間
           1. Scan alone the base(leaf) of the index.
           2. where adjacent nodes(相鄰的節點) can be combined into a single node.
          通常來說COALESCING INDEX比REBUILD INDEX 要快，但是他們的場合還需要具體問題具體分析。
          1. 如果index中90%的好的，只有10%是碎片，所以可以考慮用COALESCING INDEX
          2. 如果index已經支離破碎了，幾乎每個葉節點的block都有碎片的話，那麼就需要用REBUILD INDEX
    
九、 CHECKING INDEX VALIDITY
           AYALYZE INDEX XXX VALIDATE STRUCTURE;
           分析index的有效性，然後把結果放到INDEX_STATS表中
           select height, name , lf_rows, lf_blks, del_lf_rows from index_stats
           如果DEL_LF_ROWS/LF_ROWS>=15%,可能需要重建index
  
十、 DROP INDEXES
           DROP INDEX XXX
           刪除index的情況：
           1. 往表裡面批量imp資料前，需要先刪除index，然後imp，然後create index
           2. drop 不經常使用的index
           3. drop and re-create invalid index
               index rebulid 的速度快於 drop and re-create index ，因為前者是讀old index，後者是從資料表中讀取資料。
   
十一、 Identifying Unused index
               ALTER INDEX XXX   MONITORING USAGE;--v$object_usage
               ALTER INDEX XXX NOMONITORING USAGE;
               select * from v$object_usage;
    
十二、Getting index information
            1. DBA_INDEXES
            2. DBA_IND_COLUMNS
            3. v$object_usage

參考連結：http://www.boo booke.com/bbs/viewthread.php?tid=7630&extra=page%3D1

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9252210/viewspace-627150/，如需轉載，請註明出處，否則將追究法律責任。

相關文章