oracle索引优化

索引是表的一个概念部分，用来提高检索数据的效率。通常，通过索引查询数据比全表扫描要快。

同样在联结多个表时使用索引也可以提高效率。 另一个使用索引的好处是，它提供了对主键(primary key)的唯一性验证。

使用索引时也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4、5 次的磁盘I/O。因为索引需要额外的存储空间和处理，建立不必要的索引反而会使查询反应时间变慢。

--建立测试用大表的方式

1.  在原表的基础上用sql语句不断的翻倍数据，例如：

create table myEmp as select * from emp;

insert into myEmp select * from myemp;   --不断执行它，数据就不断翻倍

这种方式建立的大表基本都是重复数据，并不方便测试。

2. 用匿名代码块建立达标

--建立部门表，方便下面的大表可以与之关联，方便测试用

create table mydept
(
   deptId int constraint pk_mydept primary key,
   deptName varchar2(20) not null,
   remark varchar2(55)
)
;
insert into mydept values(10,'财务部','管理公司财务');
insert into mydept values(11,'销售部','负责公司销售');
insert into mydept values(12,'研发部','负责公司技术研发');
insert into mydept values(13,'后勤部','管理后勤事务');
insert into mydept values(14,'人事部','管理人力资源');
insert into mydept values(15,'产品部','管理公司产品');
insert into mydept values(16,'保安部','保护公司安全');
insert into mydept values(17,'秘书部','负责公司杂事');
insert into mydept values(18,'法务部','负责公司官司');
insert into mydept values(19,'公关部','负责公司危机公关');

--利用匿名块创建一张大表
create table bigTable
(
 id number,
 testName varchar2(20),
 sex varchar(6),
 randnum number,
 deptId int ,
 foreign key(deptId) references mydept(deptId)
);


declare
  mycount int:=5000000;
  mysex varchar2(20):='男';
  deptId int;
begin
  for i in 1..mycount
  loop
    if mod(i,2)=0 then
      mysex:='男';
    else 
      mySex:='女';
    end if;
    case mod(i,10) 
       when 0 then deptId:=10;
       when 1 then deptId:=11;
       when 2 then deptId:=12;
       when 3 then deptId:=13;
       when 4 then deptId:=14;
       when 5 then deptId:=15;
       when 6 then deptId:=16;
       when 7 then deptId:=17;
       when 8 then deptId:=18;
       when 9 then deptId:=19;
    end case;
  insert into bigTable
  values(i,dbms_random.string('x',8),mysex,abs(dbms_random.random)/100.0,deptId);
  if mod(i,1000)=0 then
    commit;
  end if;
  end loop;
end;

===========下面进行测试=================

（1） 在没有做任何优化措施的情况下，编写语句查询Randnum字段中的值在2000-3000之间的记录,在执行语句之前，首先执行命令set timing on ,可以显示每一次语句执行所花的时间。
Select count(*) from bigTable where randnum>=2000 and randnum<=6000;
执行语句
（2） 根据查询的语句，需要按照randnum字段查询，字段中的内容是随机生成的数字，因此在randnum列上创建B树索引。
  Create index rand_index on temptable(randnum);
（3） 然后重新执行查询语句，查看两次执行的时间有何不同。
（4） 按照性别作为查询条件，编写查询语句
（5） 在sex字段上建立位图索引

  create bitmap index idx_sex on bigTable(sex);
（6） 重新执行查询语句，查看执行时间的差别。