oracle建立索引 例子,关于oracle数据库索引的一些学习记录

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;

12、删除索引

SQL>drop index PK_EMP