目录
索引
提示
1.在表中定义主键约束或者唯一性约束的时候,Oracle 会自动创建该字段上的索引
2.用户也可自定义某些字段的索引
3.索引创建完成有数据库自动使用,不需要特别指定
1、创建索引
创建空测试表tb_ind01,并添加主键
SQL> show user |
在ename上添加索引
SQL> alter table tb_ind01 add constraint pk_tb_ind0101 primary key (empno); |
查看索引信息
SQL> select index_name,index_type,table_name,uniqueness from user_indexes where table_name='TB_IND01'; |
查看表和索引的大小
SQL> select segment_name,bytes/1024/1024||'MB' from user_segments where segment_name in('TB_IND01','PK_TB_IND0101','IND_TB_IND01'); |
索引的优缺点
优点:通过索引可以提高检索数据的效率
缺点:索引也需要占据数据库空间,且需要维护(数据越多,索引越大,数据经常修改,索引也要对应修改)
2、修改索引
重建索引
SQL> alter index IND_TB_IND01 rebuild; |
删除索引,并建立新索引
SQL> drop index IND_TB_IND01 ; |
查询索引的相关信息
SQL> select c.index_name,c.column_name,c.column_position,x.uniqueness from user_indexes x,user_ind_columns c where x.index_name=c.index_name and c.table_name='TB_IND01'; |
uniqueness 表示唯一性索引(主键自动生成),还是非唯一性索引(手动生成,未添加唯一性约束)
导致索引失效的原因
1、where语句导致失效
某些select语句中where子句不适用索引,举例说明
1)‘!=’将不适用索引而'>'可以(索引只能查君什么在表中,而不能查询什么不在表里)
SQL> alter session set statistics_level=all; |
2)'||'字节连接符导致索引失效
SQL> select empno,ename from tb_ind01 where empno||ENAME='7839KING'; |
2、索引列上使用not
not会产生和在索引列上使用函数相同的影响,因此当oracle遇到not的时候会停止使用索引,转而全表扫描
SQL> select empno,ename from tb_ind01 where empno not in 7839; |
3、在索引列上使用计算
where子句中,如果索引列是函数的一部分,优化器将不适用索引而转用全表扫描
SQL> select EMPNO from tb_ind01 where empno*10 >7000; |
4、复合索引使用顺序错误
SQL> create table TB_IND02(id number(5),name varchar2(5),job varchar2(5),other varchar2(5)); |
5、改变索引类型
当比较不同数据类型的数据时,oracle对列进行简单的类型转换。
查看TB_IND01表结构
创建测试表tb_ind03并插入数据
SQL> create table tb_ind03 (id1 number(5),id2 varchar2(5)); |
id1 字符类型为number 而id2字符类型为varchar2
一下语句
select * from tb_ind03 where id1='1’; |
会被转换成
select * from tb_ind03 where id1=to_number('1'); |
不过查询后貌似没有影响到索引
那么来试试id2吧
select * from tb_ind03 where id2=1; |
将被转换成
select * from tb_ind03 where id2=to_number('1'); |
先看看正常情况,没有问题,用了索引
SQL> select * from TB_IND03 where id2='1'; |
不用引号呢?果然无论是否使用字符转换,都发生了转换。导致索引失效。
SQL> select * from tb_ind03 where id2=1; |
因此一定要注意索引的使用规范
基于函数的索引
测试
创建测试表tbi01定义参照emp,保留数据
SQL> create table tbi01 as select * from emp; |
如果不知道 scott 的名字大小写的情况下,查询 scott 相关信息
SQL> select * from tbi01 where lower(ename)='scott'; |
查看执行计划
在tbi01表上创建索引
SQL> create index ind_tbi01 on tbi01(ename); |
先用原始方法测试,生成执行计划
SQL> explain plan set statement_id='info 20210107' into plan_table for select * from tbi01 where ename='SCOTT'; select lpad(' ',2*(LEVEL-1))||operation operation,options,object_name,position from plan_table start with id= 0 AND statement_id = 'info 20210107' connect by prior id = parent_id AND statement_id ='info 20210107'; |
如果使用忽略大小写敏感性的查询呢,显示full,未使用索引
SQL> explain plan set statement_id='info 20210107' into plan_table for select * from tbi01 where lower(ename)='scott'; |
那么如何既能使用索引,又忽略大小写呢
就要使用基于函数的索引了
删除并从键索引
SQL> drop index ind_tbi01; |
删除索引
SQL> drop index ind_tbi01; |
如果是因为主键或者唯一性约束而产生的索引,再删除相应约束或者删除表的时候,索引也会自 动删除。
3、不可见索引
参见https://blog.csdn.net/u012778985/article/details/113103502