Oracle學習筆記(10)

管理索引

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

2、索引：獨立于表的指針(物理結構)。指向表數據：rowid。通過索引可以定位到表數據所在行的行號，行號唯一標識這一行數據所在的物理位置。優點：加速查找速度

缺點：數據改變是有負載的。并非所有環境索引越多越好。數據面向讀，索引多；數據面向寫，索引少。索引結構是b-tree結構的(b-tree結構兩層：非頁級nonleaf和頁級leaf)。

3、索引類型和他們的用途

索引分類：按邏輯來分類、按物理來分類

a.按邏輯來分類：單行索引和復合索引(concatenated)、唯一索引和非唯一索引、基于函數的索引、Domain域

b.按物理來分類：分區視圖或非分區視圖(放在不同段分區中，加速數據訪問的速度)、B-Tree索引(Normal常規索引or

reverse key)、位圖索引(Bitmap)

4、創建各種不同類型的索引

B-Tree

Index:B樹索引(適用于取值唯一性很高的欄位，多個候選鍵，數據更新代價相對比較低，用or查詢效率低，用于OLTP)

Root(根)>Branch(子目錄)>Leaf(樹葉)->指針pointer指向Table表。

Root是非頁級，Branch、Leaf是頁級。

sql>select * from kong.stores where stor_id like

'7%';

Bitmap

Index:位圖索引(適用于取值唯一性很低的欄位中，比如說0\1，數據更新系統負擔貴，用or查詢效率高，用于數據倉庫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

創建索引時應考慮以下因素：a、平衡query查詢和DML(數據改變)需要

b、索引放在一個獨立的表空間中，把索引和數據分開，有利于并發讀寫

c、使用統一的分區大小：5個塊大小的整數倍或者最小的表空間分區大小

d、建立索引的過程對于大數量的索引不要做日志。(大數量的索引不要做日志)

e、建立索引的initrans 大于等于表所制定的initrans

sql>show parameter create_bit?//create_bitmap_area_size 創建bitmap的區域尺寸 默認8388608(8MB)

sql>select * from order_details;

//orderid是順序的序列號(10490、10491、10492。。。)，不利于查詢

sql>create index testindex3 on

orders(orderid)reverse; //做反轉索引(09401、19401、29401，。。。)

5、組織索引

(1)改變索引的存儲參數

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 在線重建

在舊有的索引的基礎上建立索引，對表不加以鎖定。在運行過程中為了保持系統的并發性而引入的。

sql>alter index testindex3 rebuild

online;

注意：在線重建索引中，位圖索引和B樹索引同樣不可以轉換。

(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;?//分析索引事實上是更新索引統計信息的過程

sql>desc index_stats?//index_stats指標進行更新

6、刪除索引

DROP INDEX hr.deptartments_name_idx;

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

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

sql>drop index testbit1;

sql>drop index testindex2;

7、如何標識沒有用到的索引

ALTER INDEX summit.orders_id_idx

MONITORING USEAGE?//監視他的應用來判定索引在系統中到底有沒有被用到

ALTER INDEX summit.orders_id_idx

NOMONTITORING USAGE?//取消監視

sql>alter index testindex3

monitoring usage;

sql>l2

sql>c /monitoring/nomonitoring/

sql>run

8、從數據字典中獲取索引信息

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?//列出索引欄位參數

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); //創建表達式時間差值的索引進行數據查詢以加速數據訪問

總結

以上是生活随笔為你收集整理的oracle normal索引类型,Oracle学习笔记(10)管理索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。