当前位置:Gxlcms > 数据库问题 > Oracle 基础——索引

Oracle 基础——索引

时间:2021-07-01 10:21:17 帮助过:10人阅读

  索引是一种快速访问数据的途径,可提高数据库性能。索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需的数据,就像书的目录,可以快速查找所需的信息,无须阅读整本书。

二、索引存在的问题

  1.索引占用表空间,创建太多索引可能造成代码冗余

  2.索引影响DML(INSERT、UPDATE、DELETE)语句的性能

三、使用索引的条件

  1.一个字段包含大量的值

  2.一个字段包含多个空值

  3.多个字段经常出现在where 查询中

  4.表非常大并且查询返回的数据量低于总数据的20%

四.索引的分类

  (一).按列值得唯一性

    1.非唯一索引:B树索引 就是一个 非唯一索引

    2.唯一索引:主键或者唯一的约束

  (二).按索引列的个数

    1.单列索引

    2.复合索引:为多个列创建一个索引

    注:

    (1).当SQL语句的WHERE字句中用到复合索引的主导列时

    (2).当某几个列在SQL语句的 【WHERE子句】 中经常通过and操作符联合一起使用,

      并且这些列合在一起是选择性比,比各自单个列的选择性要好时。

    (3).当有几个查询语句都是查询同样的几个列值时

  (三).按索引类的物理组织方式

    1.B树索引:

      适用于列中重复数据较多的情况

    2.反向键索引:就是将当前列中的值反转后生成的索引

      用途:用于大型的集群系统(被多个系统同时访问的系统)

      创建反向键语法:

        CREATE  INDEX  索引名 ON 表名 (列名) reverse

        TABLESPACE 表空间名;

    3.基于函数的索引:基于一个或者多个列上的函数或者表达式创建的索引

  注意:表达式中不能包含聚合函数(sun、count、avg、min、max)

      用途:一个表中的字段经常被函数所调用、那么这个字段就可以使用基于函数索引

         (频繁使用查询的、插入删除dml语句比较少)

      基于函数的索引语法:

        CREATE  INDEX  索引名 ON 表名 (函数(列名))

        TABLESPACE 表空间名;

    4.位图索引

      用途:主要用于处理数据的聚合关系、用于一个重复数据很多的情况

      位图索引语法:

        CREATE BITMAP  INDEX  索引名 ON 表名 (列名)

        TABLESPACE 表空间名;

五.使用索引的原则

    1、装载数据后再建立索引。

    2、频繁搜索的列可以作为索引。

    3、在联接属性上建立索引(主外键)。

    4、经常排序分组的列。 

    5、删除不经常使用的索引。

    6、指定索引块的参数,如果将来会在表上执行大量的insert操作,建立索引时设定较大的ptcfree。

    7、指定索引所在的表空间,将表和索引放在不同的表空间上可以提高性能。

    8、对大型索引,考试使用NOLOGGING子句创建大型索引

 

    不要在下面的列创建索引:   

    1、仅包含几个不同值得列。

    2、表中仅包含几条记录。

 

六.索引的使用

  1.创建索引

    语法:

  CREATE [UNIQUE] INDEX index_name(索引名) ON table_name(表名) column_list(列名))

  [TABLESPACE tablespace_name(表空间名)]

 

  说明:  

  unique:指定索引列中值必须是唯一的

  index_name:索引名   

  table_name:指定要建立索引的表名   

  column_list:要建立索引的列,可以是多列,那样的索引叫多列索引   

  table_space_name:索引存储的表空间

实例1: 技术分享
如果我们经常对emp表进行按照empno来查询某个员工的信息,
SQL> select * from emp where empno=7788;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
我们就应该对empno这一列建一个索引。
create index indx_on_empno 
on emp(empno) 
tablespace users;
技术分享

  实例2:

技术分享
如果我们经常查询某个部门工资大于1000的员工信息,
那么我们就可以在job和sal列上建立所以,这叫组合索引:
SQL> select * from emp
  2  where job=SALESMANand sal>1000;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

create index indx_on_job_sal 
on emp(job,sal)
tablespace users;
技术分享

  实例3:

技术分享
我们还可以创建基于函数的索引,不过在此前,我们必须设置一下初始化参数:
conn system/chage_on_install as sysdba;
alter system set query_rewrite_enabled=true;

create index indx_lower_job
on emp(lower(job))
tablespace users;
技术分享

 

  2.修改索引


 

就像修改表一样,我们在创建索引后可以进行修改
实例1:分配和释放索引空间
alter index indx_on_empno
allocate extent(size 1m);

实例2:释放多余的索引空间
alter index indx_on_empno
deallocate unused;

实例3:重建索引
alter index indx_on_job_sal rebuild;

实例4:联机重建索引,
使用rebuild,若其他用户正在表上执行dml操作,重建会失败,
使用如下语句,就可以成功重建
alter index indx_on_job_sal rebuild online;

实例5:合并索引
当相邻索引块存在剩余空间,通过合并索引将其合并到一个索引块上
alter index indx_on_job_sal coalesce;

实例6:重命名索引
alter index indx_on_job_sal rename to indx_on_jobandsal;

 

  3.删除索引


如果索引不再需要了,留在数据库中将会占用资源,我们可以将其删除
drop index indx_on_job_sal;


  4.查看索引


我们可以从unser_indexes视图中查看有关某表的索引信息,
下面我先来看看unser_indexes视图的信息:
SQL> desc user_indexes;


 


  

 

Oracle 基础——索引

标签:

人气教程排行