PL/SQL之索引和分区

一、索引 --index      

        数据库中的索引和目录的概念类似，如果某个列出现在查询的条件中，而该列的数据是无序的，那么查询时只能一行一行去扫描。

        创建索引就是对某些特定列中的数据进行排序，生成独立的索引表， 当在某个列上建立索引之后，如果这个列出现在查询条件中，oracle执行引擎会比较全表扫描和索引扫描的代价，如果索引扫描的代价小，就会自定使用该索引。先从索引表中找出符合记录的rowid，然后根据rowid回去到具体的数据 ，表中数据非常多的时候，使用索引带来的效率非常可观 。

1、什么时候应该创建索引 

（1）、表中的某些字段经常作为查询条件出现时，可以在该字段建立索引 。
（2）、经常作为关联条件的字段也可以建立索引，有一个基本准则：当任何当个查询的行数少于会等于全表行数的10% 索引就很有用 。

2、索引的缺陷 

        数据在进行增删改时需要更新索引  所以索引对数据修改有负面的影响。

3、创建索引

        oracle会为表的主键和包含唯一约束的列自动建立唯一索引，唯一索引不允许有重复值。

语法：
CREATE [UNIQUE] INDEX INDEX_NAME ON TABLE_NAME(列。。。。)
1、UNIQUE 建立唯一索引
2、index_name   索引的名字
3、table_name   建立索引的表

CREATE TABLE EMP_1 AS SELECT * FROM EMP;--创建一个表，数据同EMP
SELECT * FROM EMP_1; 

--在emp_1上建立唯一索引
CREATE UNIQUE INDEX UN_INDEX_EMP_1 ON EMP_1(EMPNO);
--建立普通索引
CREATE INDEX INDEX_EMP_1 ON EMP_1(DEPTNO);

4、查看 代码的执行计划

--查看执行计划 
1、选中代码  按f5（plsqldeveloper）
SELECT * 
FROM EMP_1 
WHERE EMPNO=7369;

2、命令行中：1)EXPLAIN PLAN FOR 查询语句
             2)SELECT PLAN_TABLE_OUTPUT 
               FROM TABLE(DBMS_XPLAN.display('PLAN_TABLE'));
             或 SELECT * FROM TABLE(DBMS_XPLAN.display);

5、组合索引、删除索引

        创建组合索引 
1、何时创建：当两个或多个列经常一起出现在where条件中，则可以建立组合索引 。
2、组合索引中列的顺序是任意的 也无需相邻 但是建议将最频繁访问的列放在最前面 。
注：组合索引的第一个为引导列（可以把经常使用的作为引导列）。

--删除索引的语法：
DROP INDEX 索引名
DROP INDEX INDEX_EMP_1;

--建立组合索引
CREATE INDEX IND_J_D ON EMP_1(JOB,DEPTNO);
SELECT * 
FROM EMP_1
WHERE JOB='CLERK'，DEPTNO=20;

6、几种情况下的索引失效 

1、隐式转换导致索引失效 （现实中常犯的错误）
SELECT * 
FROM EMP_1 
WHERE JOB='12345';

SELECT * 
FROM EMP_1
where JOB=12345; --索引失效

2、对索引列使用运算会导致索引失效（+-*/）
错误：SELECT * FROM EMP WHERE SAL+500>3000;
正确：SELECT * FROM EMP WHERE SAL>3000-500;

3、使用数据库内部函数导致索引失效（可以建立函数索引）
SELECT * 
FROM EMP_1