索引，索引的建立、修改、删除
2007-10-05 13:29 來源: 作者： 網友評論 0 條 浏覽次數 2986
索引
索引是關系資料庫中用于存放每一條記錄的一種對象，主要目的是加快資料的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在資料庫設計階段的與資料庫結構一道考慮。應用系統的性能直接與索引的合理直接有關。下面給出建立索引的方法和要點。
§3.5.1 建立索引
1. CREATE INDEX指令文法:
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced
其中：
schema ORACLE模式，預設即為目前帳戶
index 索引名
table 建立索引的基表名
column 基表中的列名，一個索引最多有16列，long列、long raw
列不能建索引列
DESC、ASC 預設為ASC即升序排序
CLUSTER 指定一個聚簇（Hash cluster不能建索引）
INITRANS、MAXTRANS 指定初始和最大事務入口數
Tablespace 表空間名
STORAGE 存儲參數，同create table 中的storage.
PCTFREE 索引資料塊空閑空間的百分比(不能指定pctused)
NOSORT 不（能）排序（存儲時就已按升序，是以指出不再排序）
2.建立索引的目的：
建立索引的目的是：
l 提高對表的查詢速度；
l 對表有關列的取值進行檢查。
但是，對表進行insert,update,delete處理時，由于要表的存放位置記錄到索引項中而會降低一些速度。
注意：一個基表不能建太多的索引；
空值不能被索引
隻有唯一索引才真正提高速度,一般的索引隻能提高30%左右。
Create index ename_in on emp (ename,sal);
例1：商場的商品庫表結構如下，我們為該表的商品代碼建立一唯一索引，使得在前台POS收款時提高查詢速度。
Create table good(good_id number(8) not null,/* 商品條碼 */
Good_desc varchar2(40), /* 商品描述 */
Unit_cost number(10,2) /* 單價 */
Good_unit varchar2(6), /* 機關 */
Unit_pric number(10,2) /* 零售價 */
)；
注：提高查詢速度的方法還有在表上建立主鍵，主鍵與唯一索引的差别
在于唯一索引可以空，主鍵為非空，比如：
Create table good(good_id number(8) primary key,
Good_desc Varchar2(40),
Unit_cost number(10,2),
Good_unit char(6),
Unit_pric number(10,2)
);
§3.5.2 修改索引
對于較早的Oracle版本，修改索引的主要任務是修改已存在索引的存儲參數适應增長的需要或者重建立立索引。而Oracle8I及以後的版本，可以對無用的空間進行合并。這些的工作主要是由管理者來完成。
簡要文法結構如下,更詳細的文法圖見電子文檔《Oracle8i Reference 》 中的 Alter index.
ALTER [UNIQUE] INDEX [user.]index
[MAXTRANS n]
REBUILD
[STORAGE n]
REBUILD 是 根據原來的索引結構重建立立索引，實際是删除原來的索引後再重建立立。
提示：DBA經常用 REBUILD 來重建索引可以減少硬碟碎片和提高應用系統的性能。
例：
alter index pk_detno rebuild storage(initial 1m next 512k);
ALTER INDEX emp_ix REBUILD REVERSE;
Oracle8i 的新功能可以對索引的無用空間進行合并，它由下面指令完成：
ALTER INDEX . . . COALESCE;
例如：
ALTER INDEX ename_idx COALESCE;
§3.5.3 删除索引
當不需要時可以将索引删除以釋放出硬碟空間。指令如下：
DROP INDEX [schema.]indexname
sql> drop index pk_dept;
注：當表結構被删除時，有其相關的所有索引也随之被删除。
§3.6 新索引類型
Oracle8i為了性能優化而提供新的建立新類型的索引。這些新索引在下面介紹：
§3.6.1 基于函數的索引
基于函數的索引就是存儲預先計算好的函數或表達式值的索引。這些表達式可以是算術運算表達式、SQL或PL/SQL函數、C調用等。值得注意的是，一般使用者要建立函數索引，必須具有GLOBAL QUERY REWRITE和CREATE ANY INDEX權限。否則不能建立函數索引，看下面例子：
例1：為EMP表的ename 列建立大寫轉換函數的索引idx ：
CREATE INDEX idx ON emp ( UPPER(ename));
這樣就可以在查詢語句來使用：
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;
例2：為emp 的工資和獎金之和建立索引：
1) 檢視emp 的表結構：
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2)沒有授權就建立函數索引的提示：
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
create index sal_comm on emp ( (sal+comm)*12, sal,comm)
*
ERROR at line 1:
ORA-01031: insufficient privileges
3) 連接配接到DBA帳戶并授權：
SQL> connect sys/sys@ora816
Connected.
SQL> grant GLOBAL QUERY REWRITE to scott;
Grant succeeded.
SQL> grant CREATE ANY INDEX to scott;
4）在連接配接到scott帳戶，建立基于函數的索引：
SQL> connect scott/tiger@ora816
Index created.
1)在查詢中使用函數索引：
SQL> select ename,sal,comm from emp where (sal+comm)*12 >5000;
ENAME SAL COMM
---------------------- ---------------- ----------------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
趙元傑 1234.5 54321
§3.6.2 反向鍵索引
反向鍵索引通過反向鍵保持索引的所有葉子鍵上的插入分布。有時，可用反向鍵索引來避免不平衡的索引。對于反向鍵索引可以進行下面操作：
l 通過在ALTER INDEX指令後加REBUILD NOREVERSE或REBUILD REVERSE子句來使索引邊為反向鍵索引或普通索引；
l 采用範圍掃描的查詢不能使用反向鍵索引；
l 位圖索引不能反向；
l 索引編排表不能反向。
例1：建立一個反向鍵索引：
CREATE INDEX i ON t (a,b,c) REVERSE;
例2：使一個索引變為反向鍵索引：
ALTER INDEX i REBUILD NOREVERSE;
§3.6.3 索引組織表
與普通的索引不一樣，索引組織表（Index_Organized Table）是根據表來存儲資料，即将索引和表存儲在一起。這樣的索引結構表（Index_organized table—IOT）的特點是：對表資料的改變，如插入一新行、删除某行都引起索引的更新。
索引組織表就象帶一個或多個列所有的普通表一樣，但索引組織表在B-樹索引結構的葉節點上存儲行資料。通過在索引結構中存儲資料，索引組織表減少了總的存儲量，此外，索引組織表也改善通路性能。
由于表中的行與B_樹索引存放在一起，每個行都沒有ROWID，而是用主鍵來辨別。但是Oracle會“猜”這些行的位置并為每個行配置設定邏輯的ROWID。此外，你可以為這樣的表建立第二個索引。
建立索引結構表也是用CREATE TABLE 指令加ORGANIZATION INDEX關鍵字來實作。但是，這樣的表在建立完後，你還必須為該表建立一個主鍵。
例子：
CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );
索引組織表有些限制：
l 不能使用唯一限制；
l 必須具有一個主鍵；
l 不能建立簇；
l 不能包含LONG類型列；
l 不支援分布和複制。
提示：如果建立了索引組織表，則會在DBA_TABLES中的IOT_TYPE和IOT_NAME列上記錄有索引組織表的資訊。
例1．修改索引結構表 docindex 的索引段的INITRANS參數：
ALTER TABLE docindex INITRANS 4;
例2．下面語句加一個的溢出資料段到索引組織表 docindex中：
ALTER TABLE docindex ADD OVERFLOW;
例3．下面語句為索引組織表 docindex的溢出資料段修改INITRANS參數：
ALTER TABLE docindex OVERFLOW INITRANS 4;
============================================================================================================
适當的使用索引可以提高資料檢索速度，可以給經常需要進行查詢的字段建立索引
oracle的索引分為5種:唯一索引，組合索引，反向鍵索引，位圖索引，基于函數的索引
建立索引的标準文法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空間名;
建立唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
建立組合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
建立反向鍵索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
檢視文章
oracle 檢視索引類别以及檢視索引字段被引用的字段方法2008年01月04日 星期五 13:20檢視索引個數和類别
select * from user_indexes where table='表名' ;
檢視索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
PS:
檢視某表的限制條件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
檢視視圖的名稱
SQL>select view_name from user_views;