关于oracle数据库索引的一些学习记录

原创

owenzou 2015-06-22 23:10:13 ©著作权

©著作权归作者所有：来自51CTO博客作者owenzou的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、索引的特点
        a、对具有只读我或较少插入、更新或删除操作的大表可以提高查询速度。
        b、可以对表的一列或多列建立索引。
        c、建立索引的数量没有限制。
        d、索引需要磁盘存储，需要oracle自动维护。
        e、索引对用户透明，是否使用索引是由oracle决定的。

2、RDBMS访问数据的基本方法
        a、全表扫描
        b、通过ROWID
        c、使用索引

3、增加索引会带来insert语句性能的下降。

4、索引的扫描类型
        a、索引唯一扫描（INDEX UNIQUE SCAN）
        b、索引范围扫描（INDEX RANGE SCAN）
        c、索引全扫描（INDEX FULL SCAN）
        d、索引快速扫描（INDEX FAST FULL SCAN）

5、限制索引使用的情况
        1）、使用不等于运算符（<>、!=)，在where中使用不等于条件会使索引失效。
        2）、使用IS NULL或IS NOT NULL
        3)、使用函数，一些常见的函数如trunc substr to_date to_char instr等。
        4）、比较不匹配的数据类型。

6、查看执行计划示例
        SQL>explain plan for select * from emp;
        SQL>select * from table(dbms_xplan.display);

7、建立索引示例
        1）、创建索引表空间
        SQL> create tablespace index_tbs datafile '/u1/index/index_tbs1.dbf'
size 100M autoextend on;
        2)、建立索引
        SQL> create index emp_ename_sal_idx
                on emp(ename,sal)
                tablespace index_tbs;
        3)、建立索引全语句
        SQL>create [unique | bitmap] index [schema.]index_name
                on [schema.]table_name
                (column_name [desc | asc],column_name[desc | asc])
                [reverse]    ==>创建反向索引
[tablespace tablespace_name]
                [pctfree n] ==>索引块中预先保留的空间比例
                [initrans n] ==>每个索引块中分配的事务数
                [maxtrans n] ==>每个索引块中最多的事务数
                [instorage state] ==>索引中区段EXTENT如何分配
                [logging | nologging] ==>要记录｜不要记录索引的相关操作
                [nosort] ==>不要在创建索引时再按键值进行排序

8、查看用户索引
        SQL>select * from user_indexes;
        SQL>select * from user_idx_columns;

9、监控索引的使用
        1）、打开索引使用监控
        SQL>alter index PK_EMP monitoring usage;
        2)、关闭索引使用监控
        SQL>alter index PK_EMP nomonitoring usage;
        3、查看索引使用情况
        SQL>select * from v$object_usage;

10、重建索引并迁移表空间示例
        SQL>alter index PK_EMP
                rebuild
                tablespace index_tbs1;
        重建地索引不影响用户使用索引，但不能使用DEL操作和DML操作。

11、维护索引
        1)、手工增加索引磁盘空间
        SQL>alter index PK_EMP
                allocate extent;
        2)、合并索引碎片
        SQL>alter index PK_EMP coalesce;