【oracle资料整理】--【12】索引

索引
  作用:加快查询  select
       索引一定是建立在表上的
  如何建立索引?

  a.有的建立表的时候的约束可以自动建索引
        primary key ------- 唯一性索引
        unique      ------- 唯一性索引

     create table t1(
       xh number(2) primary key,
       name varchar2(10) unique,
       age number(2)
);
 
  b.自己建索引
      加快查询
      select * from t1 where age> 70;
      建立索引
      create index ind_t1_age on t1(age);
       //在表t1的age字段上建立索引ind_t1_age
     索引是自动维护的
        insert
        delete
        update 操作花费的时间变慢了
     一个表上的索引最好不要超过6个(有频繁的增删改操作的表)

 

      create index ind_t1_age1 on t1(age);
      
      create index ind_dept on dept(dname,loc);
      //联合索引 分次序的
      create index ind_dept1 on dept(loc,dname);
 
      --删除索引
      drop index ind_t1_age;
 
      create unique index ind_x on t1(age);
      //保证age的值唯一 (唯一性索引)

  <a>select语句是如何来使用索引的?
      select * from t1 ; --不能用索引,全表扫描
      select * from t1 where age > 20;
                    --能用age上的索引
      select * from t1 where name like '李%';
                    --能用上name上的索引
      select * from t1 where xh <45;
                    --能用上xh上的索引;

      select * from t1 where age > 20;
                   --能用索引
      select * from t1 where age+10>30;
                   --不能用age上的索引
                   --<1>规则1:索引的字段不能参与运算

      select * from t1 where substr(name,1,1)='李';
                     --不能用索引
                     --<2>规则2:索引的字段上不能使用函数
      select * from t1 where name like '李%';
                    --能用索引

  查询emp表中hiredate在1982年10月到1999年9月的员工??

   select * from emp where to_char(hiredate,'yyyymm')
>= '198210' and to_char(hiredate,'yyyymm') <='199909';
  create index ind_hiredate on emp(hiredate);

                --用不上hiredate上的索引

   select * from emp where hiredate <= to_date('19990901','yyyymmdd')  and 
hiredate >= to_date('19821001','yyyymmdd');
                --能用上hiredate上的索引     

  察看索引的使用情况 看执行计划
     执行计划 -- 表示ORACLE是如何来执行你的select语句的

  对于复杂的查询的效率 只能看执行计划来确定
    关联查询
      select dname,ename from dept a,emp b
       where a.deptno = b.deptno;

 

     特殊索引
    <1> 位图索引
      create bitmap index ind_aa on emp(job);
     数据的不同值对于记录的行数来说 是个很小的数
     这种字段适合使用位图索引
      select * from emp where job='CLERK';
      --用bitmap的索引 会比普通索引的效率要高

      create index ind_aa_c on emp(job);

    <2> 簇（cu）
      关联查询的时候,让相关联的字段在物理位置上放在 
     一起
    select dept.dname,empno,ename
   from dept , emp
   where dept.deptno = emp.deptno;

 --使dept的deptno字段和emp的deptno字段 在物理上放在   
    相邻的位置上，这样使得关联查询加快
   但是 会使得emp和dept的数据的插入变慢

如何建立簇
   --簇cluster
   --建立一个簇表
     --a)簇键
create cluster emp_dept(deptno_key number(2));

--b)建立表使用簇
create table emp1(empno number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) references dept(deptno)
) cluster emp_dept(deptno);

create table dept1 (deptno number(2) primary key,
dname varchar2(14),
loc varchar2(10)) cluster emp_dept(deptno);

--c)建立簇表的索引
create index clu_emp_dept on cluster emp_dept;