index

/*创建唯一索引(unique index)*/

SQL> create table tab_idx(id int,name varchar2(24));

Table created.

SQL> desc tab_idx;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(24)

SQL> create unique index unidx_name on tab_idx (name);

Index created.

SQL> insert into tab_idx values(0,'arcerzhang');

1 row created.

SQL> insert into tab_idx values(1,'arcerzhang');
insert into tab_idx values(1,'arcerzhang')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIDX_NAME) violated


SQL> 

/*创建非唯一索引(nounqiue index)*/

SQL> create table tab_idx2 (id int,name varchar2(24));

Table created.
SQL> create index unidx_name_2 on tab_idx2(name);

Index created.

SQL> insert into tab_idx2 values(0,'arcerzhang');

1 row created.

SQL> insert into tab_idx2 values(1,'arcerzhang');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab_idx2;

        ID NAME
---------- ------------------------
         0 arcerzhang
         1 arcerzhang

SQL>           

/*创建逆序索引*/ 

CREATE INDEX i ON t (a,bc,) REVERSE;

/*将逆序索引转换为常规索引*/

用户可以在REBUILD子句后添加NOREVERSE关键字讲一个逆序键索引(reverse key index)转换为常规的索引:

ALTER INDEX i REBUILD NOREVERSE;

如果REBUILD子句后没有使用NOREVERSE关键字，那么逆序键索引(reverse key index)被重建后仍将保持逆序.

位图索引bitmap index 

bitmap index -> 适合DSS系统.(特点:不会经常地、频繁地更新);对于bitmap index对应的列数据，经常的更新，导致cpu负荷加大，锁竞争激烈.

b-tree index -> 适合OLTP系统.(特点:经常、频繁地更新;)

索引的目标是为用户提供指向包含特定键值(key value)的数据行的指针.在常规的索引中，Oracle将各行的键值及与此键值对应的一组ROWID存储在一起，从而实现了上述目标.而在位图索引(bitmap index)中，只需要存储每个键值的位图(bitmap)，而非一组ROWID.

位图中的每一位对应一个可能ROWID.如果某一位被置位,则表明这与此对应的ROWID所指向的行中包含此位所代表的的键值.oracle通过一个映射函数(mapping function)将位信息转化为实际的ROWID,因此虽然位图索引内部的存储结构与常规索引不同，但她同样能实现常规索引的功能.当不同值的索引键的数量较少时，位图索引的存储效率相当高.(男\女).

如果在WHERE子句内引用的多个列上都建有位图索引(bitmap index)，那么进行位图索引扫描时(bitmap indexing)可以将各个位图索引融合咋一起.不满足全部条件的行可以被预先过滤掉.因此位图索引能够极大地提高查询的响应时间.

数据仓库应用中位图索引的优势

数据仓库应用(data warehousing application) 的特点是数据量巨大，执行的多为自定义查询(ad hoc query)，且并发事务较少.这种环境下使用位图索引(bitmap index)具备如下优势:

• 能够减少大数据量自定义查询的响应时间
• 与其他索引技术相比能节省大量存储空间
• 即使硬件配置较低也能显著提高性能
• 有利于并行DML和并行加载

为一个大表建立传统的平衡树索引(B-tree index)可能占用极大的存储空间，索引有可能比数据表还要大数倍.而建立一个位图索引(bitmap index)所占的空间比被索引数据还要小得多.

位图索引(bitmap index)不适用与OLTP系统,因为这样的系统中存在大量对数据进行修改的并发事务.位图索引主要用于数据仓库系统中(data warehourcing)的决策支持功能，在这种环境下用户对数据的操作主要是查询而非修改.

主要进行大于(greater than)或小于(less than)比较的列,不适宜使用位图索引(bitmapt index)。例如，WHERE子句中常会将salary列和一个值进行比较，此时更适合使用平衡树索引(B-tree index)。位图索引适用等值查询，尤其是存在AND，OR,NOT等逻辑操作符的组合时.

bitmap index适合基数较小的列，b-tree index适合基数较大的列.

举例说明:一张表包含100w条数据，其中的一列包含一万个不相同的值,就可以考虑在此列上创建位图索引.此列上位图索引的查询性能将超过 b-tree index，当此列与其他列作为组合条件时效果尤为明显.假如一张表中包含100w条数据，其中一列中存放是的身份账号吗，那么此列千万不要创建bitmap index，而是创建b-tree index.

位图索引可以包含键值为NULL的行. 

用户可以在分区表上创建位图索引的前提是分区表必须是本地的，只有非分区表才能使用全局位图索引.

位图示例 

客户编号	婚姻状况	地区	性别	收入水平
101	单身	东部	男性	一级
102	已婚	中部	女性	四级
103	已婚	西部	女性	二级
104	离异	西部	男性	四级
105	单身	中部	女性	二级
106	已婚	中部	女性	三级

 

 

 

 

 

 

 

地区=‘东部’	地区='中部'	地区='西部'
1	0	0
0	1	0
0	0	1
0	0	1
0	1	0
0	1	0

 

 

 

 

 

 

 

业务分析员在统计公司客户的地区分布趋势时，需要知道"住在中部或西部地区的已婚客户有多少"?查询SQL如下:

SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');