数据库索引的创建 和 注意事项

PS:索引不是给你使用的，而是数据库本身使用，索引只是为了让你的查询更加快速而已

http://www.w3school.com.cn/sql/sql_create_index.asp
SQL CREATE INDEX 语法
在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

注释：”column_name” 规定需要索引的列。
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE INDEX 实例
本例会创建一个简单的索引，名为 “PersonIndex”，在 Person 表的 LastName 列：

CREATE INDEX PersonIndex
ON Person (LastName) 

如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：

CREATE INDEX PersonIndex
ON Person (LastName DESC) 

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

1、索引不要与表存放在同一个表空间内；
2、一个表的索引不要过多；

3、对于大表而且使用频繁的表，在生产时最好不要去加索引；
如果对大表进行索引，取出的数据大于总量的5%至10%,使用索引扫描会效率下降很多。如果通过索引，取出数据量为总量的50%以上，这个时候还不如全表扫描来得快。
4、数据量小的表，使用不频繁的不要单独另建立索引；

5、在查询条件中几个条件一起使用的，适合建立组合索引，否则不要建立组合索引，当单独引用非第一字段时将发生全表扫描；
在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引

6，在查询的时候，where条件不要对索引的列做处理，而要对后面的条件字段做处理。否则用不上这个列上的索引。
如： score_date 是varchar2类型,该列上有索引。
select
……
where trim(replace(s.SCORE_DATE, ‘-‘, ”)) <= ‘20071201’
……
上面这条sql用不上score_date列上的索引。
下面这条sql就能用上索引。
select
……
where SCORE_DATE <= ‘20071201’
……
如果不是基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
下面的查询不会使用索引（只要它不是基于函数的索引）
select empno,ename,deptno
from emp
where trunc(hiredate)=’01-MAY-81’;
把上面的语句改成下面的语句，这样就可以通过索引进行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date(‘01-MAY-81’)+0.9999);

6.1: 表的某个字段是字符型的, 那么传入数字值不加引号的话,sql不走索引:
例子:
docset表的todocid字段新建了索引,但是这个sql就是不走索引,加hint也没有用.

select /+INDEX(docset IX_DOCSET_TODOCID)/ *
from docset
WHERE REPLYSENDFLAG = 1
and TODOCID in (141429, 141441);
执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=1592 Cardinality=2 Bytes=650
TABLE ACCESS FULL Object wner=SEALDATA Object name=DOCSET Cost=1592 Cardinality=2 Bytes=650
后来发现, todocid这个字段是VARCHAR2(20)类型的, 不是number, 所以传入的值是数字的时候,要加引号,才能走索引, 否则要使用函数索引才能有效.

select /+INDEX(docset IX_DOCSET_TODOCID)/ *
from docset
WHERE REPLYSENDFLAG = 1
and TODOCID in (‘141429’, ‘141441’);
执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=1 Cardinality=2 Bytes=650
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID Object wner=SEALDATA Object name=DOCSET Cost=1 Cardinality=2 Bytes=650
INDEX RANGE SCAN Object wner=SEALDATA Object name=IX_DOCSET_TODOCID Cost=1 Cardinality=2

7.一些SQL的写法会限制索引的使用：1.where子句中如果使用in、or、like、!= <>，均会导致索引不能正常使用，将”<>”换成”>and<”;将”is not null “换成”>=chr(0)”;2.使用函数时，该列就不能使用索引。3.比较不匹配数据类型时，该索引将会被忽略
一些SQL语句优化的写法：1.如果from是双表的查询时，大表放在前面，小表放在后面(基础表)。最后面的表是基础表。(只在基于规则的优化器中有效)2.如果三表查询时，选择交叉表(intersection table)作为基础表.(只在基于规则的优化器中有效)3.写where条件时，有索引字段的判断在前，其它字段的判断在后；如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件；4.查询数量较大时，使用表连接代替IN，EXISTS，NOT IN，NOT EXISTS等。5.ORACLE采用自下而上的顺序解析WHERE子句,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

8，关于位图索引。
怎样使用位图索引？注意点是什么？语句怎样写呢： dba_indexes index_type
OLTP不能使用位图索引，这应该是谨记的！
create bitmap index t_index1 on TEST_ZHP (c1);
当列上的值类型比较少时，适合使用位图索引

olap：online analysis processing 联机分析处理, 适用于dss（decision support system决策支持系统,也就是人们常说的数据仓库系统）
主要面向分析，一般基于数据仓库，数据主要为只读方式，涉及海量查询,select，主要处理历史数据，出报表等。
olap是联机分析处理，它所擅长的是对企业的当前和历史数据进行分析，对企业的状况进行分析，主要是针对企业管理人员

位图索引是oracle的比较引人注目的地方,其主要用在olap(联机数据分析)方面,也就是数据仓库。
位图索引在实际密集型OLTP(数据事务处理)中用得比较少,因为OLTP会对表进行大量的删除、修改 ，建议使用B-tree索引

oltp ：online transaction processing 联机事务处理 适用于mis ，网站等小事务的交易系统！
主要面向事务处理，一般为单行或几行操作。update ， 写密集型
它所擅长的就是处理当前实时的数据，最新的业务数据，实现企业业务的计算机化，主要针对企业的业务人员