文章目录

1.索引原理2.索引的目的3.索引的类型3.1. 唯一索引和非唯一索引：3.2. 主索引和次索引3.3. 组合索引 4.索引的种类4.1. B-tree索引（默认）：4.2. 位图索引4.3. 单列索引和复合索引4.4.函数索引4.5.反键索引 5.索引压缩6.升序或降序7.查看索引8.重建、维护索引9.索引重命名10.收集统计信息11.隐藏索引12.删除索引13.注意事项

1.索引原理

原理：通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O 创建索引准则： 1.需要访问的数据不超过表中的4%-5%。使用索引查询数据需要进行两次数据读取的操 作，即读索引和读表。 2.相对较小的表可以不使用索引。 3.为所有表创建主键，在指定一列作为主键时，oracle自动创建唯⼀索引。 4.对包括使用多表连接操作中使用的列，建立索引。 5.在where自句中频繁使用的列上创建索引。 6.在包括 order by、group by操作的列，以及涉及排序操作的union、distinct操作中涉及 到的列上创建索引，因为索引是已经被排序的。 7.被频繁更改的列由于涉及开销问题，理论上不建议创建索引。 8.对高选择性的表建立索引。即对几乎没有相同值的表建立索引。

输出执行计划

explain plan for select * from emp where ename = ‘SCOTT’；

查看执行计划

@?/rdbms/admin/utlxplp.sql

2.索引的目的

优化查询速度

3.索引的类型

3.1. 唯一索引和非唯一索引：

唯一索引是基于唯一列的索引。当使用了唯一约束时，由oracle自动创建。唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 创建唯一索引：

create union index ind_obj_id on test_objects(object_id) tablespace tbs_ind_emp;

创建非唯一索引：

create index ind_obj_id on test_objects(object_id) tablespace tbs_ind_emp;

3.2. 主索引和次索引

主索引是表的唯一索引，它是非空且唯一的。次索引是表中其他列上的索引，不是唯一索引。

3.3. 组合索引

组合索引：是包含了表中的两个列或者更多列，还成为拼接索引。 创建组合索引：

create index ind_obj_id on test_objects(object_id , object_name) tablespace tbs_ind_emp;

4.索引的种类

4.1. B-tree索引（默认）：

一棵树，叶子节点（双向链表）包含索引列和指向表中每个匹配行的rowid值 所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同 能够适应精确查询、模糊查询和比较查询

4.2. 位图索引

创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建⽴⼀个位图，最后通过 位图索引中的映射函数完成位到⾏的rowid转换 create bitmap index index_sno on emp (sno);

4.3. 单列索引和复合索引

如果索引建立在多个列上，它的第一个列被where子句引用时，优化器会使用该索引，至 少要包含组合索引的第一列。

create index ind_no_name on emp(empno , ename);

4.4.函数索引

当经常要访问⼀些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已 经计算出来了，可以加快查询速度；当函数结果不确定时采用B树索引，结果是固定的某 一个值时使用位图索引；函数索引中可以使用len,trim,substr,upper(每行返回独立结果），不能使用sum,max,min,avg等

create index fbi on emp(upper(name)); select from emp where upper(name) =‘WISH’;

4.5.反键索引

除了在索引过程中键值和列数据的字节是相反的以外，本质上与B树相同；不适用于范围 扫描。

create index mgr_idx on emp1(mgr) reverse;

5.索引压缩

索引键的压缩可以减小索引的大小，从而减少I/O量 重复键值只存储一次，重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符 串。

create index comp_index on emp1(sal) compress;

6.升序或降序

叶节点中的键值排列默认是升序的。

create index depth_job_idx on emp1(depno desc , job asc);

7.查看索引

查看表上有哪些索引

select index_name , index_type , tablespace_name , uniqueness from all_indexes where table_name = ’TABLE_NAME’;

8.重建、维护索引

索引的数据是跟着基表的DML活动而经常发生变化的。 基表删除数据后，对应的索引叶节点中空间不会被释放、不会被重用。 在DML操作十分频繁的表上的索引，有可能会变得非常庞大。

方式一：删除原来的索引，重新建立索引（ drop index ； create index ） 方拾二：alter index ind_obj_id rebuild ; alter index ind_obj_id rebuild online ; alter index ind_obj_id rebuild online nologging ; 方式三：合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引，合并索引方式好一些，无需额外存储空间，代价较低） alter index index_sno coalesce ;

9.索引重命名

alter index old_name rename to new_name ;

表重命名

alter table old_name rename to new_name ;

10.监控索引 监控索引的有效性，可请求Oracle对索引进行监控。 索引的拥有者执行以下命令：

alter index ind_obj_id monitoring usage ;终止监控活动

查看是否已经终止：

select * from v$object_usage where index_name = ‘IND_OBJ_ID’;

10.收集统计信息

analyze table table_name compute statistics ;

11.隐藏索引

11g版本后，可将索引设置为隐藏模式。主要是对优化器隐藏索引，用于调试。 在删除index前，设为隐藏模式

create index ind_xxx on xxx( id ) invisible ; alter index ind_xxx invisible ; alter index ind_xxx visible ;

查看索引是否为隐藏模式

select index_name , status , visibility from dab_indexes ;

不对优化器隐藏索引

alter session set optimizer_use_invisible_indexes=true ;

12.删除索引

drop index index_sno ;

13.注意事项

通配符在搜索词首出现时，oracle不能使用索引不要在索引列上使用not，可以采用其他方式代替（不能使用<>,!=;可以使用>100 or <100)索引上不记录空值，操作将不会使用到索引

如何查看SQL或存储过程在SQL*plus中的执行时间？

Ans：可以使用命令set timing on; set timing on; 在SQLPLUS中使⽤，时间精确到0.01秒。也就是10毫秒。 在PL/SQL DEVELOPER 中，时间精确到0.001秒： 还有一个很接近的命令set time on，这个是用于展示当前时间。 SQL>set time on netca 欢迎来到oracle网络配置管理器，选择你想要的配置 监听的配置 相对监听做什么操作：添加 默认名字 TCP网络通信协议默认下一步 查看表的自动统计信息 select * from user analyze table 表名 compute statistics 监听的配置文件： /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 日志文件： /u01/app/oracle/diag/tnslsnr/db01/listener/alter/log.xml sqlplus scott/tiger @192.168.56.177:1521 alter system register； developer sql 左上⻆新建 连接 - 数据库连接 确定 左边是曾经连接的数据库 右边是新建的数据库 连接名：to_188_oradb 用户名：scott 口令：tiger 保存口令 端口1521 SID oradb 测试，成功