oracle 索引

oracle索引语法： Create[UNIQUE|BITMAP]index 　　[schema.]index_nameon[schema.]table_name(column_name[ASC|DESC],…n,[column_expression])|CLUSTER[schema.]cluster_name 　　[INITRANSinteger] 　　[MAXTRANSinteger] 　　[PCTFREEinteger] 　　[PCTUESDinteger] 　　[TABLESPACEtablespace_name] 　　[STORAGEstorage_clause] 　　[NOSORT] 　　[REVERSE]

通用建立索引： CREATE UNIQUE INDEX indexname ON table (column); 使用以上方法建立的索引是 B-tree结构

唯一索引： 当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立。 CREATE UNIQUE INDEX indexname ON table (column);

位图索引： 当某个字段只有少数的几个取值的时候可以建立位图索引。 位图索引在使用OR语句的时候效果显著： CREATE BITMAP INDEX indexname ON table (column[, column]...);

键压缩索引： 比如表landscp的数据如下： site feature job Britten Park, Rose Bed 1, Prune Britten Park, Rose Bed 1, Mulch Britten Park, Rose Bed 1,Spray Britten Park, Shrub Bed 1, Mulch Britten Park, Shrub Bed 1, Weed Britten Park, Shrub Bed 1, Hoe …… 查询时，以上3列均在where条件中同时出现，所以建立基于以上3列的组合索引。但是发现重复值很多，所以考虑压缩特性。 Create index zip_idx on landscp(site, feature, job) compress 2; 将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。

函数索引： 在索引陷阱中，其中一条是在索引列使用函数的时候，优化器会忽略掉索引的条件。函数索引就是建立索引使得索引列即使使用了函索作为条件，任然能够在走索引。 create index test.ind_fun on test.testindex(upper(a)); 建立以上函数索引之后，再使用upper(a)作为查询条件的时候就会走索引了。

---

索引陷阱：

索引陷阱就是使用了一下条件之后，即使列上有索引，优化器也会忽略掉索引：

使用不等于操作符(<>, !=) 下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描 select * from dept where staff_num <> 1000; 但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？ 有！ 通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。 select * from dept shere staff_num < 1000 or dept_id > 1000;

使用 is null 或 is not null 使 用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图 索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。 解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)

使用函数 如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引： select * from staff where trunc(birthdate) = '01-MAY-82'; 但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。 select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

比较不匹配的数据类型 比较不匹配的数据类型也是难于发现的性能问题之一。 下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。 select * from dept where dept_id = 900198; 这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。 把SQL语句改为如下形式就可以使用索引 select * from dept where dept_id = '900198';