Oracle索引語句整理_ZenDei技術網路在線

轉載：http://www.cnblogs.com/djcsch2001/articles/1823459.html 索引，索引的建立、修改、刪除索引索引是關係資料庫中用於存放每一條記錄的一種對象，主要目的是加快數據的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在資料庫設計階段的與 ...

轉載：http://www.cnblogs.com/djcsch2001/articles/1823459.html

索引，索引的建立、修改、刪除
索引
索引是關係資料庫中用於存放每一條記錄的一種對象，主要目的是加快數據的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在資料庫設計階段的與資料庫結構一道考慮。應用系統的性能直接與索引的合理直接有關。下麵給出建立索引的方法和要點。
§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
[INITRANS n]
[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;

Grant succeeded.

4）在連接到scott帳戶，創建基於函數的索引：

SQL> connect scott/tiger@ora816
Connected.
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);

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 表名 (列名)

TABLESPACE 表空間名;

創建組合索引:

CREATE INDEX 索引名 ON 表名 (列名1,列名2)

TABLESPACE 表空間名;

創建反向鍵索引:

CREATE INDEX 索引名 ON 表名 (列名) reverse

TABLESPACE 表空間名;

查看文章
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;