oracle索引介绍二

张兴康

接上贴：http://bbs.delit.cn/forum.php?mo ... tid=1780&extra=
删除索引
当不需要时可以将索引删除以释放出硬盘空间。命令如下：
DROP INDEX [schema.]indexname

例如：
sql> drop index pk_dept;

注：当表结构被删除时，有其相关的所有索引也随之被删除。

基于函数的索引
基于函数的索引就是存储预先计算好的函数或表达式值的索引。这些表达式可以是算术运算表达式、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

反向键索引
反向键索引通过反向键保持索引的所有叶子键上的插入分布。有时，可用反向键索引来避免不平衡的索引。对于反向键索引可以进行下面操作：
通过在ALTER INDEX命令后加REBUILD NOREVERSE或REBUILD REVERSE子句来使索引边为反向键索引或普通索引；
采用范围扫描的查询不能使用反向键索引；
位图索引不能反向；
索引编排表不能反向。

例1：创建一个反向键索引：
CREATE INDEX i ON t (a,b,c) REVERSE;

例2：使一个索引变为反向键索引：
ALTER INDEX i REBUILD NOREVERSE;


索引组织表
与普通的索引不一样，索引组织表（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 表空间名;

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;

张兴康

fteair

张兴康

fteair 发表于 2017-3-8 17:45