oracle 優(yōu)化--索引

一、事務(wù)

1、簡介

事務(wù)是數(shù)據(jù)處理的核心，是業(yè)務(wù)上的一個邏輯單元，它能夠保證其中對數(shù)據(jù)所有的操作，要么全部成功，要么全部失敗。DBMS通過事務(wù)的管理來協(xié)調(diào)用戶的并發(fā)行為，減少用戶訪問資源的沖突。

1）顯示提交：當(dāng)事務(wù)遇到COMMIT指令時，將結(jié)束事務(wù)并永久保存所有的更改的數(shù)據(jù)。

2）顯示回滾：當(dāng)事務(wù)遇到ROLLBACK指令時，也將結(jié)束事務(wù)的執(zhí)行，但是此時它回滾所有更改的數(shù)據(jù)到事務(wù)開始時的原始值，即取消更改，數(shù)據(jù)沒有變化。

3）DDL語句：一旦用戶執(zhí)行了DDL（數(shù)據(jù)定義語言，如create，drop等）語句，則之前的所有DML（數(shù)據(jù)操作語言）操作作為一個事務(wù)提交，這種提交稱為隱示提交。

4）正常結(jié)束程序：如果oracle數(shù)據(jù)庫應(yīng)用程序正常結(jié)束，如使用sqlplus工具更改了數(shù)據(jù)，而正常退出該程序（exit），則oracle自動提交事務(wù)。

5）非正常地結(jié)束程序：當(dāng)程序崩潰或意外終止時，所有數(shù)據(jù)更改都被回滾，這種回滾成為隱示回滾。

2、事務(wù)的特點

事務(wù)有4個特性，簡寫為ACID特性。

1）原則性：以轉(zhuǎn)賬操作為例，轉(zhuǎn)出賬戶余額減少和轉(zhuǎn)入余額增加是兩個DML語句，但是必須作為一個不可分割的完整操作。要么同時成功，要么同時失敗，只轉(zhuǎn)出而沒有轉(zhuǎn)入顯然是不可接受的。

2）一致性：無論是在事務(wù)前、事務(wù)中、事務(wù)后，數(shù)據(jù)庫始終處于一致的狀態(tài)。例如：轉(zhuǎn)賬前分別是2000和1000，總金額是3000，轉(zhuǎn)賬300后分別是1700和1300，總金額還是3000.這就叫做一致性。不一致就是在某個時間點查詢到的總金額不是3000.

3）隔離性：在某個時間段，肯定有很多人都在轉(zhuǎn)賬，每個人的轉(zhuǎn)賬都是在自己的事務(wù)中，所以在一個數(shù)據(jù)庫中，會有很多事物同時存在。雖然同時存在很多事物，但是事物之間不會相互影響。

4）持久性：如果事物提交成功，則數(shù)據(jù)修改永遠(yuǎn)生效，如果是回滾，則數(shù)據(jù)完全沒有沒修改，就相當(dāng)于沒有這件事情發(fā)生。

二、索引

1.索引是oracle的一個對象，是與表關(guān)聯(lián)的可選結(jié)構(gòu)，提供了一種快速訪問數(shù)據(jù)的途徑，提高了數(shù)據(jù)庫檢索性能。索引使數(shù)據(jù)庫程序無需對整個表進(jìn)行掃描，就可以在其中找到所需要的數(shù)據(jù)。就像書的目錄，可以通過目錄快速查找所需信

息，無需閱讀整本書。

2.、索引的分類

1）B樹索引結(jié)構(gòu)

4、創(chuàng)建索引的語法

create [unique] index 索引名稱 on 表名（列名）[tablespace 表空間名稱]

解釋：

[unique]用于指定唯一索引，默認(rèn)情況下為非唯一索引

[tablespace]為索引指定表空間

1）創(chuàng)建標(biāo)準(zhǔn)索引

SQL> CREATE INDEX index_name ON tablename(columnname)

     TABLESPACE index_tbs;

2）重建索引

SQL> ALTER INDEX index_name REBUILD;

合并索引碎片

SQL>ALTER INDEX index_name COALESCE;

3）刪除索引

SQL> DROP INDEX index_name;

例1：

1）在雇員表（emp）中，為雇員名稱（ename）列創(chuàng)建b樹索引。

5、創(chuàng)建唯一索

n 確保在定義索引的列中沒有重復(fù)值

n Oracle 自動在表的主鍵列上創(chuàng)建唯一索引

n 使用CREATE UNIQUE INDEX語句創(chuàng)建唯一索引

語法如下：

SQL> CREATE UNIQUE INDEX index_name

     ON tablename(columnname);

例：在薪水級別（salgrade）表中，為級別編號grade列創(chuàng)建唯一索引。

6、反向鍵索引

與常規(guī)B樹索引相反，反向鍵索引在保持列順序的同時反轉(zhuǎn)索引列的字節(jié)。反向鍵索引通過反轉(zhuǎn)索引鍵的數(shù)據(jù)值，使得索引的修改平均分布到整個索引樹上。主要應(yīng)用于所多個實例同時訪問一個數(shù)據(jù)庫的場景中

6、位圖索引

位圖索引適合低于基數(shù)的列，即該列的值是有限的幾個。例如：雇員表中的工種（job）列，即便是幾百萬條雇員記錄，工種也是有限的。Job列可以作為位圖索引，類似的還有圖書表中的圖書類別列等。

位圖索引不直接存儲ROWID，而是存儲字節(jié)位到ROWID的映射，減少響應(yīng)時間,節(jié)省空間占用。位圖索引不應(yīng)當(dāng)在頻發(fā)發(fā)生insert、update、delete操作的表上使用，這是因為單個位圖索引指向表的很多數(shù)據(jù)行，當(dāng)修改索引項時需要將其指向的數(shù)據(jù)行全部鎖定，這會嚴(yán)重降低數(shù)據(jù)庫的并發(fā)處理能力。位圖索引適合用于數(shù)據(jù)倉庫和決策支持系統(tǒng)中

例：在雇員emp表中，為工種（job）列創(chuàng)建位圖索引。

基本語法：

CREATE BITMAP  INDEX emp_job_bit_idx ON emp(job);

6、組合索引
類似sqlserver的復(fù)合索引，在表內(nèi)多列上創(chuàng)建索引。索引中的列不必與表中的列順序一致，也不必相互鄰接。

6、基于函數(shù)的索引

需要創(chuàng)建的索引需要使用表中一列或多列的函數(shù)或表達(dá)式，也可以將基于函數(shù)的索引創(chuàng)建為B樹索引或位圖索引。

基本語法：

SQL> CREATE INDEX emp_ename_upper_idx

ON tablename (UPPER(columnname));

：在雇員（emp）表中，為雇員名稱（ename）列創(chuàng)建小寫函數(shù)索引

6、創(chuàng)建索引的原則

n 頻繁搜索的列可以作為索引列

n 經(jīng)常排序，分組的列可以作為索引

n 經(jīng)常用作連接的列（主鍵/外鍵）可以作為索引

n 將索引放在一個單獨的表空間中，不要放在有回退段、臨時段和表的表空間中

n 對于大型索引而言，考慮使用NOLOGIN子句創(chuàng)建大型索引。

n 根據(jù)業(yè)務(wù)數(shù)據(jù)發(fā)生頻率，定期重新生成或重新組織索引，進(jìn)行碎片整理。