Oracle索引規劃設計

管理索引的準則：

l  在表中插入資料後建立索引

l  在正確的表和列上建立索引

l  為效能而排序索引列

l  限制每個表的索引數量

l  刪除不再需要的索引

l  估計索引大小並設定儲存引數

l  為每個索引指定表空間

l  考慮並行建立索引

l  考慮建立具有nologging的索引

l  考慮合併或重建索引時的開銷和優點

l  在停用或刪除約束之前考慮開銷

 

l  B_tree索引：預設和最常用的

l  B_tree索引：為簇定義的索引

l  雜湊簇索引：為雜湊簇定義的索引

l  全域性和區域性索引：涉及到分割槽表和索引

l  反向鍵索引：使用於Oracle Real Application Clusters 應用環境

l  點陣圖索引：緊湊的，特別使用於具有少量值集的列

l  基於函式的索引：包含函式/表示式的預先計算的值

l  域索引：特別針對應用和外掛

1.       索引在邏輯上和物理上都不依賴於相關表的資料。作為獨立的結構，索引需要儲存空間；

2.       標準和點陣圖索引

Create index idx_emp_ename on emp(ename[desc/asc]);

Create bitmap index idx_emp_sex on emp(sex);對錶操作需要較多的空間，首先應用於資料倉儲環境中

3.       複合索引列：

應該將含有最少重複值的列指定為第一列，第二個含有最少重複值的列指定為第二列，依此類推；含有許多重複值或含有較多null值的列不應該包含在索引中；否則需要指定索引的最後列。

4.       全域性和區域性索引

--建立分割槽和非分割槽索引

CREATE TABLE SALES(

PRODUCT_ID VARCHAR2(5),

  SALES_DATE DATE NOT NULL,

  SALES_COST NUMBER(10))

PARTITION BY RANGE (SALES_DATE)(

  PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01', 'YYYY-MM-DD')),

  PARTITION P2 VALUES LESS THAN (TO_DATE('2004-01-01', 'YYYY-MM-DD')),

  PARTITION P3 VALUES LESS THAN (MAXVALUE));

create index idx_sales_sales_date on sales(sales_date)

local

(PARTITION P1,PARTITION P2,PARTITION P3)

當相關表被分割槽時索引也將分割槽，索引的分割槽鍵和表的分割槽鍵是相同的

如果一個表是按照日期進行範圍分割槽，可以在日期上建立索引，並使用表分割槽相同的範圍進行索引分割槽，叫區域性分割槽索引；

create index idx_sales_production_id on sales(product_id);

create index idx_sales_sale_cost on sales(sales_cost)

global partition by hash(sales_cost)

( partition p1,

 partition p2);

也可以不使用和表相同的分割槽模式來建立分割槽索引，這種索引叫非分割槽索引或全域性索引

5.       在正確的表和列上建立索引

如果經常需要檢索大表種少於15%的行，那麼就建立索引。這個百分比的變化取決於表掃描的相對速度和相關的行資料如何分發到索引鍵。

主鍵約束和唯一約束自動建立索引，但應該在外來鍵上建立索引；

小表不需要索引，如果查詢花費時間太長，可能是表增長太快，小表已增長為大表了；

6.       適合建立索引的列

列中的值相對具有唯一性

取值範圍大(適合建立常規索引)

取值範圍小(適合建立點陣圖索引)

列中有許多空置，但經常查詢所有具有值的行。使用{where col_x > -9.99 * power(10,125)}或{where col_x is not null}第一種方法好些，因為第一句使用了col_x列上的索引(假設col_x是一個數值列)

不適合建立索引列：

  列中有許多空值，但又不查詢非空值

  LONG和LONG RAW列不能建索引

7.       為效能而排序索引列

在create index語句中，列的排序會影響查詢的效能；通常，將最常用的列放在最前面；建立多列的索引來提高查詢速度，例如col1,col2,col3，查詢只訪問col1，或col1和col2，這樣的組織可以提高查詢的速度；如果只訪問col2或只訪問col3，或是col2和col3，查詢不會使用索引

8.       限制每個表的索引數量

索引越多，修改表的開銷越大。特別是插入或刪除行時或更改一個列時，表上的所有索引也要被修改；在確定是否建立多個索引時，需要衡量表的讀寫功能，如果一個表主要用於讀，則多一些索引是有好處的，如果經常更好一個表，則索引少一些為好；

9.       刪除不再需要的索引

索引不能加速查詢。表可能很小，或表種有很多行，但幾乎沒有索引項；

應用種的查詢不使用索引；

重建索引之前必須先刪除索引；

10.   估計索引大小和設定儲存引數

 --評估建立一個索引所使用的空間成本

declare

l_used_bytes number;

l_alloc_bytes number;

begin

dbms_space.create_index_cost (

ddl => 'create index idx_emp_dept on scott.emp(deptno) tablespace users',

used_bytes => l_used_bytes,

alloc_bytes => l_alloc_bytes

   );

dbms_output.put_line ('Used Bytes      = '||l_used_bytes);

dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

end;

/

11.   為每個索引指定表空間

如果將表和索引使用相同的表空間，能方便對資料庫進行管理(如表空間或檔案備份),或保證應用的可用性；

如果將表和索引使用不同的表空間(在不同磁碟上)產生的效能，要比放在相同的表空間好，因為減少磁碟競爭；

如果一個包含資料或索引的表空間離線，那麼就不能保證那個表的語句能正常執行；

12.   考慮用nologging建立索引

使用nologging建立索引時不存檔，建立後應該進行備份；

節省了重做日誌檔案的空間；

縮短了建立索引的時間；

改善了並行建立大索引時的效能；

Create index idx_emp_salary on salary(sal) tablespace user2 parallel 5 nologging;

13.   考慮合併或重建索引時的損益

葉資料塊合併：ALTER INDEX IDX_EMP_ENAME COALESCE;結合

14.   建立索引

Create index idx_emp_ename on emp(ename) tablespace user2 storgae(initial 20k next 20k pctincrease 75);

Create index idx_dept_unique_name on dept(dname) tablespace user2;

15.   使用using index建立索引

-- 使用USING INDEX子句建立索引

CREATE TABLE emp (

empno NUMBER(5) PRIMARY KEY,

ename varchar2(20),

sex char(1),

age INTEGER,

address varchar2(100)

)

ENABLE PRIMARY KEY USING INDEX

TABLESPACE users;

--使用不同的USING INDEX方法建立索引

CREATE TABLE a (

a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1))

);

 

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);

 

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;

16.   收集統計資訊

analyze index idx_emp_ename compute statistics;

create index idx_emp_sal on emp(sal) compute statistics;

17.   建立大索引[分配較大的臨時表空間]

使用create tablespace或create temporary tablespace 語句建立新的臨時表空間；

使用alter user 語句的temporary tablespace 選項為該使用者建立新的臨時表空間；

使用create index 語句建立索引；

使用drop tablespace 語句刪除該表空間，然後使用alter user 語句將使用者的臨時表空間重新設定成原始的臨時表空間。

該過程可以避免將普通的、一般共享的臨時表空間擴充套件到一個大小不合理的表空間，從而影響以後的效能；

18.   聯機建立索引

Create index idx_emp_ename on emp(mgr, emp1, emp2, emp3) online;

應當在DML操作不多時聯機建立索引；

聯機建立或重建不支援並行操作

19.   建立基於函式的索引

--建立基於函式的索引

create or replace function fn_sal

(p_a number)

return number

deterministic

as

v_b number;

begin

  v_b:= p_a*1.1;

  return v_b;

end;

/

create index emp_sal on emp(fn_sal(sal));

@/u01/oracle/rdbms/admin/utlxplan.sql；

explain plan for

select empno,ename,fn_sal(sal),sal

from emp

where fn_sal(sal)>3300;

select lpad(' ',4*(level-2))||

        operation||' '||

        options||' '||

        object_name "execution_plan"

from plan_table

start with id = 0

connect by prior id = parent_id;

20.   建立鍵壓縮索引

Create index idx_emp_ename on emp(ename) tablespace user1 compress 1;

Alter index idx_emp_ename rebuild nocompress;

21.   建立不可見的索引

--建立不可見的索引

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE (INITIAL 20k

NEXT 20k

PCTINCREASE 75)

INVISIBLE;

22.   更改索引的儲存特徵

Alter index idx_emp_ename storage(pctincrease 50);不能修改initial和minextents

Alter table emp enable primary key using index;

alter table emp disable primary key;

23.   重建現有的索引

Alter index idx_emp_ename rebuild[online]

24.   標記索引為不可見

Alter index idx_emp_name invisible;

Alter index idx_emp_name visible;

select table_name, index_name, status, visibility from user_indexes;

25.   監視索引使用

Alter index idx_emp_sal monitoring usage;

Alter index idx_emp_sal nomonitoring usage;

select * from v$object_usage;

26.   監視索引的使用空間

Analyze index idx_emp_ename validate structure;

select pct_used from index_stats where name='IDX_EMP_ENAME';

27.   刪除索引

Drop index idx_emp_ename;

28.   資料字典

Dba_indexes，all_indexes，user_indexes;描述索引資訊，某些列包含由DBMS_STATS包或ANALYZE 語句產生的統計資料；

Dba/all/user_ ind_columns描述表上的索引的列，包含由DBMS_STATS包或ANALYZE 語句產生的統計資料；

Dba/all/user_ind_expressions 描述表上基於函式的索引的表示式；

Dba/all/user_ind_statistics 包括適合於索引的優化器統計資料；

Index_stats 儲存最後一條ANALYZE INDEX VALIDATE STRUCTURE語句所產生的資訊；

Index_histogram儲存最後一條ANALYZE INDEX VALIDATE STRUCTURE語句所產生的資訊；

v$object_usage 包含由alter index [index_name] monitoring usage語句所產生的索引使用的資訊；

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