ORACLE_OCP之索引INDEX

ORACLE_OCP之索引INDEX

一、什么是索引？

• 索引他主要的作用就是用来加速对行的查询。索引可以显式创建–我们执行索引创建命令时创建。
  索引也可以隐式创建-比如说建立主键时

二、索引具有以下特征

• 1 索引是模式对象SCHEMA.OBJECT它高于数据对象。和表一样索引也有自己的段结构
• 2 ORACL服务器使用索引来进行行检索加速
• 3 可以通过使用快速路径访问的方法来快速查找数据，从而减少磁盘I/O操作
• 4 索引和索引所在的表无关
• 5 有ORACLE自身进行自动使用和维护

三、索引操作

• 如何创建索引

  • 隐式创建（自动创建）：定义了主键和唯一键约束，系统会自动创建一个唯一索引。
  • 显式创建（手动创建）: 用户可以在列上创建非唯一索引，加速对数据的检索访问。

• 我们默认创建的索引叫做B*TREE索引 – 平衡树索引。

• 创建索引

CREATE INDEX DEPTS_DEPARTMENT_ID_IDX ON DEPTS(DEPARTMENT_ID);
//DEPTS_DEPARTMENT_ID_IDX ：索引名称
//DEPARTMENT_ID：创建索引的表(列)

• 在下列情况下应该创建索引

  • 1 列包含较大范围的值
  • 2 列包含了大量的值
  • 3 在where子句或联结条件中频繁使用一个列或者多个列
  • 4 表很大 但是语句多数查询检索的行不到表总数的百分之二至百分之四

• 在下列情况下不应该创建索引，通常不值得创建索引

  • 1 表比较小
  • 2 在查询中不经常使用列作为条件
  • 3 语句多数查询检索的数据行要超过表总数的百分之二至百分之四
  • 4 表更新比较频繁 因为索引页跟着一起进行频繁更新
  • 5 被索引的列将作为表达式的一部分进行引用 比如说对这个列进行函数操作 函数会导致索引出问题
  • 当表比较小的时候，全表扫描的速度会高于索引参与查询的速度。

• 确认索引

  • USER_INDEXES 数据字典视图包含索引的名称以及唯一性
  • USER_IND_COLUMNS 该数据字典视图包含索引名 表名 和 列名

  //查看栗子：
  SELECT IC.INDEX_NAME,IC.COLUMN_NAME,IC.COLUMN_POSITION COL_POS,ix.uniqueness
  FROM user_indexes ix ,user_ind_columns ic
  WHERE IC.INDEX_NAME=IX.INDEX_NAME
  AND IC.TABLE_NAME=IX.TABLE_NAME;

• 基于函数的索引

CREATE INDEX upper_dept_name_idx on depts(UPPER(DEPARTMENT_NAME));
SELECT * FROM DEPTS WHERE UPPER(DEPARTMENT_NAME)=‘SALES’;

• 删除索引

DROP INDEX upper_dept_name_idx;