一、索引 --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 
WHERE EMPNO=7788; SELECT * 
FROM EMP_1
WHERE POWER(EMPNO,1/4)=77; --索引失效CREATE INDEX F_E_EMP_1 ON EMP_1(POWER(EMPNO,1/4));4、以下内容会导致索引失效
a  使用<>、!=、 not in、not exists 
b  使用like百分号在前面 like'%...'  可以在建立索引时使用reverse(列名)处理
c  单独使用组合索引中非第一个位置的索引列 组合索引只有在他的第一个列被where条件使用时 优化器才会选择该索引

7、索引的分类

  
索引分类：
普通索引  CREATE INDEX INDEX_NAME ON TABLE_NAME(列)
唯一索引  CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(列)
组合索引  CREATE INDEX INDEX_NAME ON TABLE_NAME(列1,列2.。。。)
函数索引  CREATE INDEX INDEX_NAME ON TABLE_NAME(函数(列))
位图索引  CREATE BITMAP INDEX INDEX_NAME ON TABLE_NAME(列)

8、创建索引的原则

索引建立的原则 
1、使用主键或约束会自定建立唯一索引 
2、在经常用在过滤条件的列上建立索引 
3、经常用作分组或排序的列上建立索引
4、经常要进行dml操作的列  不适合建立索引
5、避免大数据类型建立索引 BLOB  CLOB 
6、小表不适合建立索引 
7、经常作为关联条件的列可以建立索引 索引的优点：
1、加快检索的速度
2、通过使用索引 在查询过程中使用优化器提高性能 
3、加速表之间的关联 
4、通过唯一索引  保证数据的唯一性 
缺点：
1、额外占用空间 
2、对有dml操作的列建立索引会降低dml的效率
3、索引的建立和维护需要消耗时间  消耗的时间和数据量成正比

9、索引的优缺点

索引的优点：
1、加快检索的速度
2、通过使用索引 在查询过程中使用优化器提高性能 
3、加速表之间的关联 
4、通过唯一索引  保证数据的唯一性 
缺点：
1、额外占用空间 
2、对有dml操作的列建立索引会降低dml的效率
3、索引的建立和维护需要消耗时间  消耗的时间和数据量成正比

二、表分区

        Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

        分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用DML命令访问分区后的表时，无需任何修改。

        表分区又分为范围分区、列表分区、哈希分区和组合分区4个类型。

1、什么时候使用分区表

（1）.表的数据量特别大
（2）.表中包含历史数据，新的数据被增加到新的分区中。

2、表分区的优缺点

        优点：
（1）.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
（2）.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
（3）.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
（4）.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

        缺点：
        分区表相关，已经存在的表没有方法可以直接转化为分区表。需要维护。

3、范围分区

CREATE TABLE 表名(列1  数据类型,列2  数据类型,.....)         --不加分号 
PARTITION BY RANGE(列) --用来分区数据应该放在哪个分区
(PARTITION 分区名1 VALUES LESS THAN(分区字段具体的一个上限值),PARTITION 分区名2 VALUES LESS THAN(分区字段具体的一个上限值),.........PARTITION 分区名N VALUES LESS THAN(MAXVALUE) --最大值（含空值）
);

--创建分区表
create table EMP_F(empno    NUMBER(4) not null,ename    VARCHAR2(10),job      VARCHAR2(9),mgr      NUMBER(4),hiredate DATE,sal      NUMBER(7,2),comm     NUMBER(7,2),deptno   NUMBER(2)
)PARTITION BY RANGE(HIREDATE)
( --入职日期在1982年之前的PARTITION RANGE_1981 VALUES LESS THAN(TO_DATE(19820101,'YYYYMMDD')),--入职日期1982年的PARTITION RANGE_1982 VALUES LESS THAN(TO_DATE(19830101,'YYYYMMDD')),--其余为一起PARTITION RANGE_MAX VALUES LESS THAN(MAXVALUE)
);INSERT INTO EMP_F SELECT * FROM EMP;   --插入数据
SELECT * FROM EMP_F; --查询整张表的数据
SELECT * FROM EMP_F PARTITION(RANGE_1981); --查询某个分区的数据

 4、列表分区

CREATE TABLE 表名( ......)
PARTITION BY LIST(列) 
( --注  分区名不可重复PARTITION 分区名1 VALUES(具体的值),PARTITION 分区名2 VALUES(具体的值),。。。。。。
)

--建立分区表
create table EMP_LIST(empno    NUMBER(4) not null,ename    VARCHAR2(10),job      VARCHAR2(9),mgr      NUMBER(4),hiredate DATE,sal      NUMBER(7,2),comm     NUMBER(7,2),deptno   NUMBER(2)
)PARTITION BY list(DEPTNO)
(PARTITION P10 VALUES(10),PARTITION P20 VALUES(20),PARTITION P30 VALUES(30),PARTITION P40 VALUES(40)
);

5、哈希分区

--语法：
CREATE TABLE 表名(
............
)PARTITION BY HASH(列) 
( PARTITION 分区名1,PARTITION 分区名2。。。。。。。。。
)

--建立hash分区表
create table EMP_HASH(empno    NUMBER(4) not null,ename    VARCHAR2(10),job      VARCHAR2(9),mgr      NUMBER(4),hiredate DATE,sal      NUMBER(7,2),comm     NUMBER(7,2),deptno   NUMBER(2)
)PARTITION BY HASH(empno) 
(PARTITION P0,PARTITION P1,PARTITION P2,PARTITION P3
);
注：hash分区的数量最好是2的整数次幂

6、组合分区

        当前版本为11g  只支持range作为主分区的组合分区

（1）主分区范围 子分区是列表

CREATE TABLE 表名(.......)
PARTITION BY RANGE(主分区字段)---主分区
SUBPARTITION BY LIST(子分区字段) --=子分区
(PARTITION 主分区1 VALUES LESS THAN(主分区1上限值)(--子分区的定义SUBPARTITION  子分区1 VALUES(子分区值1),SUBPARTITION  子分区1 VALUES(子分区值2),SUBPARTITION  子分区1 VALUES(子分区值3),SUBPARTITION  子分区N VALUES(子分区值N), ),PARTITION 主分区2 VALUES LESS THAN(主分区2上限值)(--子分区的定义SUBPARTITION  子分区1 VALUES(子分区值1),SUBPARTITION  子分区1 VALUES(子分区值2),SUBPARTITION  子分区1 VALUES(子分区值3),SUBPARTITION  子分区N VALUES(子分区值N), ),.......PARTITION 主分区N VALUES LESS THAN(主分区N上限值)(--子分区的定义SUBPARTITION  子分区1 VALUES(子分区值1),SUBPARTITION  子分区1 VALUES(子分区值2),SUBPARTITION  子分区1 VALUES(子分区值3),SUBPARTITION  子分区N VALUES(子分区值N), ),
)

--把入职日期看成主分区  部门为子分区
create table EMP_F_L(empno    NUMBER(4) not null,ename    VARCHAR2(10),job      VARCHAR2(9),mgr      NUMBER(4),hiredate DATE,sal      NUMBER(7,2),comm     NUMBER(7,2),deptno   NUMBER(2)
)PARTITION BY RANGE(HIREDATE)
SUBPARTITION BY LIST(DEPTNO)
( --入职日期在1982年之前的PARTITION RANGE_1981 VALUES LESS THAN(TO_DATE(19820101,'YYYYMMDD'))(--子分区SUBPARTITION EP10 VALUES(10),SUBPARTITION EP11 VALUES(20),SUBPARTITION EP12 VALUES(30),SUBPARTITION EP13 VALUES(40)),--入职日期1982年的PARTITION RANGE_1982 VALUES LESS THAN(TO_DATE(19830101,'YYYYMMDD'))(--子分区SUBPARTITION EP20 VALUES(10),SUBPARTITION EP21 VALUES(20),SUBPARTITION EP22 VALUES(30),SUBPARTITION EP23 VALUES(40)),--其余为一起PARTITION RANGE_MAX VALUES LESS THAN(MAXVALUE)(--子分区SUBPARTITION EP30 VALUES(10),SUBPARTITION EP31 VALUES(20),SUBPARTITION EP32 VALUES(30),SUBPARTITION EP33 VALUES(40))
);INSERT INTO EMP_F_L SELECT * FROM EMP;
SELECT * FROM EMP_F_L PARTITION(RANGE_1981);
SELECT * FROM EMP_F_L SUBPARTITION(EP10)

（2）主分区为范围子分区为哈希

CREATE TABLE 表名(.......)
PARTITION BY RANGE(主分区字段)---主分区
SUBPARTITION BY HASH(子分区字段) --=子分区
(PARTITION 主分区1 VALUES LESS THAN(主分区1上限值)(--子分区的定义SUBPARTITION  子分区1,SUBPARTITION  子分区2,...SUBPARTITION  子分区N),PARTITION 主分区2 VALUES LESS THAN(主分区2上限值)(--子分区的定义SUBPARTITION  子分区1,SUBPARTITION  子分区2,...SUBPARTITION  子分区N  ),.......PARTITION 主分区N VALUES LESS THAN(主分区N上限值)(--子分区的定义SUBPARTITION  子分区1,SUBPARTITION  子分区2,...SUBPARTITION  子分区N ),
);

(3)主分区和子分区都是范围分区（略）

7、分区表的维护

1、添加分区 
ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN(上限值) 
（添加分区的界限应高于最后一个分区的界限，如果是范围分区要先删除maxvalues分区）
ALTER TABLE EMP_F ADD PARTITION RANGE_1983 VALUES LESS THAN(
TO_DATE(19840101,'YYYYMMDD'));ALTER TABLE EMP_LIST ADD PARTITION P50 VALUES(50);
ALTER TABLE EMP_HASH ADD PARTITION P4;--hash分区最好满足2的整数次幂ALTER TABLE 表名 MODIFY PARTITION 分区名 ADD SUBPARTITION 子分区名
values LESS THAN (子分区界限)
ALTER TABLE 表名 MODIFY PARTITION 分区名 ADD SUBPARTITION 子分区名
values(子分区值)ALTER TABLE EMP_F_L MODIFY PARTITION RANGE_1981 ADD SUBPARTITION 
EP14 VALUES(50);2、删除分区
ALTER TABLE 表名 DROP PARTITION 分区名;
ALTER TABLE EMP_F DROP PARTITION RANGE_MAXALTER TABLE 表名 DROP SUBPARTITION 子分区名
ALTER TABLE EMP_F_L DROP SUBPARTITION EP14;
注：如果要删除的分区是表中唯一的分区 那么此分区不能被删除要想删除此分区必须删除该表 3、截断分区 --删除分区中的数据  表分区还在 不需要提交事务
截断分区是指删除某个分区的数据 不会删除分区也不会影响其他分区的数据
当表中只有一个分区的时候 也可以截断分区 
ALTER TABLE 表名 TRUNCATE PARTITION 分区名;
ALTER TABLE 表名 TRUNCATE SUBPARTITION 分区名;SELECT * FROM EMP_F PARTITION(RANGE_1981);
ALTER TABLE EMP_F TRUNCATE PARTITION RANGE_1981;SELECT * FROM EMP_F_L SUBPARTITION(EP10);
ALTER TABLE EMP_F_L TRUNCATE SUBPARTITION EP10;4、合并分区
合并分区是将相邻的分区合并成一个分区 最终结果采用较高分区的结果
分区界限低的分区合并时名字放在前面 
注：不能将分区合并到较低界限 
语法： P1 较低分区   P2 较高分区
ALTER TABLE 表名 MERGE PARTITIONS P1,P2 INTO PARTITION P2; ALTER TABLE EMP_F MERGE PARTITIONS RANGE_1982,RANGE_1983 
INTO PARTITION RANGE_1983;5、拆分分区
拆分分区是把一个分区拆分成两个分区  拆分之后原分区消失 
注：不能对hash拆分
ALTER TABLE 表名 SPLIT PARTITION 分区名 AT(分区点)  INTO (PARTITION 分区名1,PARTITION 分区名2);
分区上限以分区点为界限 从上一个上限值到分区点为分区名1的范围，分区点到下一个上限值为分区2的范围
ALTER TABLE EMP_F SPLIT PARTITION RANGE_1983 AT(TO_DATE(19830101,'YYYYMMDD'))
INTO (PARTITION RANGE_1982,PARTITION RANGE_1983);6、分区的重命名 
ALTER TABLE 表名 RENAME PARTITION 旧名 TO 新名
ALTER TABLE 表名 RENAME SUBPARTITION 旧名 TO 新名ALTER TABLE EMP_F RENAME PARTITION RANGE_1983 TO RANGE_198333
alter TABLE EMP_F_L RENAME SUBPARTITION EP10 TO EP1000