索引例子

  1. -- 索引
  2. -- 索引的概念：索引就是在表的列上构建一个二叉树
  3. -- 大幅度提高查询效率，但是索引会影响增删下的效率
  4. -- 单列索引
  5. -- 创建单列索引
  6. create index idx_ename on emp(ename);
  7. -- 单列索引触发规则，条件必须是索引列中的原始值
  8. -- 单行函数，模糊查询，都会影响索引的触发
  9. select * from emp where ename ='SCOTT' ;
  10. -- 复合索引
  11. -- 创建复合索引
  12. create index idx_enameAndJob on emp(ename,job);
  13. -- 复合索引中第一列为优先检索列
  14. -- 如果要触发复合索引，必须含有优先检索列中的原始值
  15. select * from emp where ename ='SCOTT' and job ='MANAGER'; -- 触发复合索引
  16. select * from emp where ename ='SCOTT' or job ='MANAGER';-- 不触发索引
  17. select * from emp where ename ='SCOTT'; -- 触发单列索引

索引的创建语法

  1. create or replace unique|bitmap index <schema>.<index_name> on <schema>.<table_name>
  2.   (<column_name>|<expression> asc|desc ,
  3.    <column_name>|<expression> asc|desc ...)
  4.     tablespace <tablespace_name>
  5.     storage <storage_settings>
  6.     logging|nologging
  7.     compute statistics
  8.     nocompress|compress<nn>
  9.     nosort|reverse
  10.     partition|global partition<partition_setting>
  • unique|bitmap : unique表示唯一值索引，bitmap表示位图索引，为空则默认为B-tree索引
  • | ASC | DESC : 可以单列索引，也可以多列进行联合索引，当为expression 的时候，为“基于函数的索引”
  • tablespace : 制定存放索引的表空间（当表和索引在不同的表空间的时候，效率更高）
  • storage : 可以设置表空间的存储参数
  • logging|nologging : 是否对索引产生redolog重做日志（对于大表来说，可以设置为nologging从而来减少空间占用，提高效率）
  • compute statistics : 设置为创建索引时，收集统计信息
  • nocompress|compressnn : 是否使用“键压缩”（使用键压缩可以删除一个键列中出现的重复值）
  • nosort|reverse : nosort表示与表中相同的顺序进行创建索引，reverse表示使用与表中相反的顺序进行创建索引
  • partition|nopartition|global partition : 可以在分区表上和未分区表上对创建的索引进行分区

    索引的特点

  1. 大大加快检索数据的速度
  2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
  3. 加速表与表之间的连接
  4. 查询语句汇总含有分组或者排序的语句时，速度更快

  5. 查询的过程中，使用索引，使用优化隐藏器，从而提高系统的性能

    索引的不足

  6. 创建和维护索引，比较耗费时间，随着数据量的增大而增大

  7. 创建索引，占一定的物理空间（聚簇索引，占用空间会更大）

  8. 在对表进行增删改的时候，索引相应的也需要进行动态的更新

    比较适合建立索引的列的特点

  9. 经常需要搜索的列上

  10. 主键，一般建立唯一性索引，保持数据的唯一性
  11. 外键，提高表与表之间连接的速度
  12. 需要排序的列上
  13. where子句后边经常出现的字段

  14. 经常需要根据范围进行搜索的列上，比如日期

    不适合建立索引的列的特点

  15. 很少进行搜索的列上

  16. 列取值比较少的列上
  17. blob类型的列上

  18. 修改频率比较高的列上

    限制索引（失效索引）

  19. 使用不等于<> 、 != ，（不等于操作符一定会进行全表扫描）如:SELECT Col FROM tbl WHERE col ! = 10 应该改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

select cust_Id,cust_name from customers where cust_rating<> ‘aa’;
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
select cust_Id,cust_name from customers where cust_rating < ‘aa’ or cust_rating > ‘aa’;
注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

  1. 使用is null 、 is not null （只要索引中出现一个null，那么这个索引就报废了。所以在建立索引的时候，一定要将准备建立索引的列设置为not null），like语句会使用索引
  2. 使用函数（where子句中含有trunc()、add_months()之类）的时候，sql优化器会自动忽略掉索引
  3. where子句中，进行了数据类型不匹配的比较，比如(where row_num = ‘1’)的时候，生气了优化器会限制索引的使用

也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型,在 account_number字段上有索引。
下面的语句将执行全表扫描：
select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle 可以自动把 where子句变成to_number(account_number)=990354，这样就限
制了索引的使用,改成下面的查询就可以使用索引：
select bank_name,address,city,state,zip from banks where account_number=’990354’;
特别注意： 不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次―全表扫描。

  1. 索引列上不能进行计算 SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效，应该改成SELECT Col FROM tbl WHERE col > 10 * 10

原文链接：https://www.cnblogs.com/wang-junxi/p/8111663.html