索引的相關知識 ①.創建準則
?
??? 今天學習索引的知識。所謂索引，恩，這個可以再我以前的文章《索引的概念及創建》看到，多的也不說了，反正是看著教材，再重新學習一遍。這里介紹的索引的內容還是比較詳細的，一些很細小的東西都講到了，可以增進對索引的理解。
?
一、管理準則
?
??? Oracle的索引可以更快的定位信息，提供訪問表的數據的更快路徑。索引的種類有：
?
??? ● B-Tree索引：默認的最常用索引
??? ● B-Tree簇索引：特意為簇定義的索引
??? ● 散列值索引：特意為散列簇定義的索引
??? ● 全局和局部索引：相應于分區表的索引
??? ● 逆轉值索引：特別適用于Oracle真實應用簇的應用
??? ● 位圖索引：緊湊的，特別適用于少量值集的列
??? ● 基于函數的索引：包含函數/表達式的預先計算的值
??? ● 域索引：特別針對應用或插件
?
??? 索引的管理準則包括：
?
??? ① 在表中插入數據后創建索引
?
??? ② 索引正確的表和列
?
??? * 如需經常檢索大表中少于15%的行，則創建索引(百分比值與掃描速度有關)
??? * 改善多個表聯結性能創建索引。特別是外鍵需要創建索引。
??? * 小表不需要索引
??? * 列中的值比較唯一適合創建所以
??? * 取值范圍大(適合作常規索引)
??? * 取值范圍小(適合作位圖索引)
??? * 列中有許多空值，但經常查詢所有具有值的行適合創建索引
??? * 列中有許多空值，但又不查詢非空值適合創建索引
??? 注：查詢非空值，用 WHERE COL > -9.99 * power(10,125) 效果要比 WHERE COL IS NOT NULL 好，因為可以使用索引。
?
??? ③ 調整索引列位置(
常用列放在最前面)
?
??? ④ 限制每個表的索引數量
?
??? ⑤ 刪除不需要的索引(重建索引前必須先刪除索引)
?
??? ⑥ 指定索引數據塊空間使用
?
??? ⑦ 估計索引大小，設置存儲參數
?
??? ⑧ 為每個索引指定表空間
?
??? ⑨ 考慮并行創建索引
?
??? ⑩ 考慮用NOLOGGING創建索引
?
??? ⑾ 考慮合并或重建索引時的損益
?
??? ⑿ 停用或刪除約束之前考慮開銷
?
?
二、創建索引
?
1、明確地創建索引
?
??? CREATE INDEX emp_ename ON emp(ename)
??? TABLESPACE users
??? STORAGE (INITIAL 20K
??? NEXT 20K
??? PCTINCREASE 75)
??? PCTFREE 0;
?
??? 注：索引創建時不能指定PCTUSED參數。
?
2、明確得創建唯一索引
?
??? 唯一索引可以保證鍵列上表不會有兩行重復的值。
?
??? CREATE UNIQUE INDEX dept_unique_index ON dept(dname)
??? TABLESPACE indx;
?
3、創建與約束相關的索引
?
??? 在創建UNIQUE KEY或PRIMARY KEY時，會自動創建一個唯一索引，以確保數據的完整性。在使用CREATE TABLE或ALTER TABLE時不需要用戶進行操作，但是用戶可以使用USING INDEX子句來對創建實施控制。
?
??? CREATE TABLE emp (
??? empno NUMBER(5) PRIMARY KEY,
??? age INTEGER)
??? ENABLE PRIMARY KEY USING INDEX
??? TABLESPACE users
??? PCTFREE 0;
?
??? 當要對UNIQUE和PRIMARY KEY約束相關索引進行更明確的控制時：
?
??? 例1：
??? CREATE TABLE a (
??? a1 INT PRIMARY KEY USING INDEX (create index ai on a(a1)));
?
??? 例2：
??? CREATE TABLE b (
??? b1 INT,
??? b2 INT,
??? CONSTRAINT bu1 UNIQUE (b1,b2)
??? USING INDEX (create unique index bi on b(b1,b2)),
??? CONSTRAINT bu2 UNIQUE (b2,b1) USING INDEX bi);
?
??? 例3：
??? CREATE TABLE c(c1 INT, c2 INT);
??? CREATE INDEX ci ON c(c1,c2);
??? ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
?
4、采集創建索引時伴隨的統計數據
?
??? CREATE INDEX emp_ename ON emp(ename)
??? COMPUTE STATISTICS;
?
5、創建大索引
?
??? ① 創建一個新的臨時表空間
??? ② 將用戶默認臨時表空間設置為新建的表空間
??? ③ 創建索引
??? ④ 刪除表空間，并將用戶的臨時表空間更改過來
?
6、聯機創建索引
?
??? 可以用聯機創建索引的辦法，使得創建時仍可更改基礎表
?
??? CREATE INDEX emp_name ON emp (mgr,emp1,emp2,emp3) ONLINE;
?
??? 注：可是使用DML，但不能使用DDL，而且不支持并行執行。
?
7、創建基于函數的索引
?
??? 特點：
??? ● 創建更強有力的分類
??? ● 預先計算出計算密集的函數值，并在索引中分類
??? ● 則加優化器執行范圍掃描而不是全表掃描的數量
??? ● 真正的降序索引成為可能
??? ● 在對象列和REF列上創建索引
?
??? 注：在創建基于函數的索引之后，必須ANALYZE分析表。
?
??? CREATE INDEX empi ON emp
??? UPPER((ename),NLSSORT(ename));
?
8、創建鍵壓縮索引
?
??? CREATE INDEX emp_name ON emp(ename)
??? TABLESPACE users
??? COMPRESS 1;
?
??? 可以使用以下語句停用壓縮：
??? ALTER INDEX emp_ename REBUILD NOCOMPRESS;
?
?
?
?
?