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