oracle 優化--索引

一、事務

1、簡介

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

1）顯示提交：當事務遇到COMMIT指令時，將結束事務并永久保存所有的更改的數據。

2）顯示回滾：當事務遇到ROLLBACK指令時，也將結束事務的執行，但是此時它回滾所有更改的數據到事務開始時的原始值，即取消更改，數據沒有變化。

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

4）正常結束程序：如果oracle數據庫應用程序正常結束，如使用sqlplus工具更改了數據，而正常退出該程序（exit），則oracle自動提交事務。

5）非正常地結束程序：當程序崩潰或意外終止時，所有數據更改都被回滾，這種回滾成為隱示回滾。

2、事務的特點

事務有4個特性，簡寫為ACID特性。

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

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

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

4）持久性：如果事物提交成功，則數據修改永遠生效，如果是回滾，則數據完全沒有沒修改，就相當于沒有這件事情發生。

二、索引

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

息，無需閱讀整本書。

2.、索引的分類

1）B樹索引結構

4、創建索引的語法

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

解釋：

[unique]用于指定唯一索引，默認情況下為非唯一索引

[tablespace]為索引指定表空間

1）創建標準索引

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）列創建b樹索引。

5、創建唯一索

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

n Oracle 自動在表的主鍵列上創建唯一索引

n 使用CREATE UNIQUE INDEX語句創建唯一索引

語法如下：

SQL> CREATE UNIQUE INDEX index_name

     ON tablename(columnname);

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

6、反向鍵索引

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

6、位圖索引

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

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

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

基本語法：

CREATE BITMAP  INDEX emp_job_bit_idx ON emp(job);

6、組合索引
類似sqlserver的復合索引，在表內多列上創建索引。索引中的列不必與表中的列順序一致，也不必相互鄰接。

6、基于函數的索引

需要創建的索引需要使用表中一列或多列的函數或表達式，也可以將基于函數的索引創建為B樹索引或位圖索引。

基本語法：

SQL> CREATE INDEX emp_ename_upper_idx

ON tablename (UPPER(columnname));

：在雇員（emp）表中，為雇員名稱（ename）列創建小寫函數索引

6、創建索引的原則

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

n 經常排序，分組的列可以作為索引

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

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

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

n 根據業務數據發生頻率，定期重新生成或重新組織索引，進行碎片整理。