管理索引

1、索引和表的關系：書目錄和書的關系。在數(shù)據(jù)庫中只有索引和表才能申請空間分配。是獨立存在的物理結構。實現(xiàn)數(shù)據(jù)的唯一性。

2、索引：獨立于表的指針（物理結構）。指向表數(shù)據(jù)：rowid。通過索引可以定位到表數(shù)據(jù)所在行的行號，行號唯一標識這一行數(shù)據(jù)所在的物理位置。優(yōu)點：加速查找速度缺點：數(shù)據(jù)改變是有負載的。并非所有環(huán)境索引越多越好。數(shù)據(jù)面向讀，索引多；數(shù)據(jù)面向寫，索引少。索引結構是b-tree結構的（b-tree結構兩層：非頁級nonleaf和頁級leaf<指針所在的位置>）。

3、索引類型和他們的用途
索引分類：按邏輯來分類、按物理來分類
a.按邏輯來分類：單行索引和復合索引（concatenated）、唯一索引和非唯一索引、基于函數(shù)的索引、Domain域
b.按物理來分類：分區(qū)視圖或非分區(qū)視圖（放在不同段分區(qū)中，加速數(shù)據(jù)訪問的速度）、B-Tree索引(Normal常規(guī)索引or reverse key)、位圖索引（Bitmap）

4、創(chuàng)建各種不同類型的索引
B-Tree Index:B樹索引(適用于取值唯一性很高的欄位，多個候選鍵，數(shù)據(jù)更新代價相對比較低，用or查詢效率低，用于OLTP)
Root(根)>Branch(子目錄)>Leaf（樹葉）->指針pointer指向Table表。
Root是非頁級，Branch、Leaf是頁級。

sql>select * from kong.stores where stor_id like '7%';

Bitmap Index:位圖索引（適用于取值唯一性很低的欄位中，比如說0\1，數(shù)據(jù)更新系統(tǒng)負擔貴，用or查詢效率高，用于數(shù)據(jù)倉庫OLAP）
sql>create index testindex1 on kong.orders(orderid) tablespace users;

sql>conn kong/kxf_001@fox
sql>create bitmap index testbit1 on sales(payterms) tablespace users;

sql>create index testindex2
    on stores(stor_name)
    tablespace users
    pctfree 20
    pctused 40
    storage(initial 100k next 100k);   //注意：pctused是不能用的。
sql>del 5
sql>run

創(chuàng)建索引時應考慮以下因素：
a、平衡query查詢和DML（數(shù)據(jù)改變）需要
b、索引放在一個獨立的表空間中，把索引和數(shù)據(jù)分開，有利于并發(fā)讀寫
c、使用統(tǒng)一的分區(qū)大?。?個塊大小的整數(shù)倍或者最小的表空間分區(qū)大小
d、建立索引的過程對于大數(shù)量的索引不要做日志。(大數(shù)量的索引不要做日志)
e、建立索引的initrans 大于等于表所制定的initrans

sql>show parameter create_bit  //create_bitmap_area_size 創(chuàng)建bitmap的區(qū)域尺寸 默認8388608（8MB）
sql>select * from order_details; //orderid是順序的序列號（10490、10491、10492。。。），不利于查詢
sql>create index testindex3 on orders（orderid）reverse; //做反轉索引（09401、19401、29401，。。。）

5、組織索引
(1)改變索引的存儲參數(shù)
sql>alter index testindex3
    pctfree 20
    storage(next 200k pctincrease 20);

(2)手工強制分配空間或清除無用的索引空間
ALTER INDEX orders_region_id_idx ALLOCATE EXTENT(SIZE 200K DATAFILE '/DISK6/indx01.dbf'); //注意DATAFILE是原來索引所在的表空間
ALTER INDEX orders_id_idx DEALLOCATE UNUSED;

sql>alter index testindex3 allocate extent(size 100k datafile 'd:\oracle\oradata\fox\users01.dbf');
sql>alter index testindex3 deallocate unused;

(3)索引重建:ALTER INDEX ...REBUILD...
   a.可以移動索引到一個不同的表空間
   b.改善空間的應用,把被刪除（邏輯刪除）的實體移除，把空間釋放出來
   c.改變一個反轉鍵和一個正常的B樹索引之間進行轉換，但不可以在B樹索引和位圖索引之間進行轉換。
sql>alter index testindex3 rebuild tablespace indx;
sql>alter indext testindex3 rebuild  reverse;  //反轉和B樹索引轉換
sql>alter indext testindex3 rebuild bitmap; //錯，注意：位圖索引和B樹索引不可以轉換。

(4)ONLINE Rebuild of Indexes 在線重建
在舊有的索引的基礎上建立索引，對表不加以鎖定。在運行過程中為了保持系統(tǒng)的并發(fā)性而引入的。
sql>alter index testindex3 rebuild online;

(5)索引碎片整理：
ALTER INDEX orders_id_idx COALESCE;
sql>alter index testindex3 coalesce;

(6)檢查索引有效性：
ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;
sql>analyze index testindex3 validate structure;  //分析索引事實上是更新索引統(tǒng)計信息的過程
sql>desc index_stats  //index_stats指標進行更新

6、刪除索引
DROP INDEX hr.deptartments_name_idx;
建立的索引在數(shù)據(jù)查詢的時候用不到，在數(shù)據(jù)更新的時候又是負擔，就一定要刪除這類沒用的索引。有些索引平時用的比較少，在進行數(shù)據(jù)裝入時，從其他系統(tǒng)中把大批量的數(shù)據(jù)加載進來時，可以考慮把索引刪除，再把數(shù)據(jù)load進來，再重新建索引。因為在表上建的索引越多，在load數(shù)據(jù)時系統(tǒng)負擔越重。

sql>select * from user_indexes;//列出全部用戶索引
sql>drop index testbit1;
sql>drop index testindex2;

7、如何標識沒有用到的索引
ALTER INDEX summit.orders_id_idx MONITORING USEAGE  //監(jiān)視他的應用來判定索引在系統(tǒng)中到底有沒有被用到
ALTER INDEX summit.orders_id_idx NOMONTITORING USAGE  //取消監(jiān)視
sql>alter index testindex3
     monitoring usage;
sql>l2
sql>c /monitoring/nomonitoring/
sql>run                              

8、從數(shù)據(jù)字典中獲取索引信息
DBA_INDEXES、DBA_IND_COLUMNS、DBA_IND_EXPRESSIONS、V$OBJECT_USAGE

sql>select * from user_indexes;
sql>select * from all_indexes;
sql>select * from dba_indexes;

sql>desc dba_ind_columns  //列出索引欄位參數(shù)
sql>select index_name,table_name,column_name from dba_ind_columns
    where table_name='ORDERS' AND table_owner='KONG';
sql>desc orders  //列出orders表中的欄位
sql>create index testindex4 on orders(orderdate-requireddate); //創(chuàng)建表達式時間差值的索引進行數(shù)據(jù)查詢以加速數(shù)據(jù)訪問