【SQL】索引 INDEX

目录

索引

提示

1、创建索引

索引的优缺点

2、修改索引

导致索引失效的原因

基于函数的索引

删除索引

3、不可见索引

---

---

索引

提示

1.在表中定义主键约束或者唯一性约束的时候，Oracle 会自动创建该字段上的索引

2.用户也可自定义某些字段的索引

3.索引创建完成有数据库自动使用，不需要特别指定

 

1、创建索引

创建空测试表tb_ind01，并添加主键

SQL> show user USER is "SCOTT" SQL> create table tb_ind01 as select * from emp;   Table created.   SQL> alter table tb_ind01 add constraint pk_tb_ind0101 primary key (empno);   Table altered.

在ename上添加索引

SQL> alter table tb_ind01 add constraint pk_tb_ind0101 primary key (empno);   Table altered.

查看索引信息

SQL>  select index_name,index_type,table_name,uniqueness from user_indexes where table_name='TB_IND01';   INDEX_NAME           INDEX_TYPE TABLE_NAME UNIQUENES -------------------- ---------- ---------- --------- PK_TB_IND0101        NORMAL     TB_IND01   UNIQUE IND_TB_IND01         NORMAL     TB_IND01   NONUNIQUE

查看表和索引的大小

SQL> select segment_name,bytes/1024/1024||'MB' from user_segments where segment_name in('TB_IND01','PK_TB_IND0101','IND_TB_IND01');   SEGMENT_NAME         BYTES/1024/1024 -------------------- --------------- TB_IND01             .0625MB PK_TB_IND0101        .0625MB IND_TB_IND01         .0625MB

索引的优缺点

优点：通过索引可以提高检索数据的效率

缺点：索引也需要占据数据库空间，且需要维护（数据越多，索引越大，数据经常修改，索引也要对应修改）

2、修改索引

重建索引

SQL> alter index IND_TB_IND01 rebuild;   Index altered.

删除索引，并建立新索引

SQL> drop index  IND_TB_IND01 ;   Index dropped.   SQL> create index ind_tb_ind02 on TB_IND01(ename,job,mgr);   Index created.

查询索引的相关信息

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';   INDEX_NAME           COLUMN_NAM COLUMN_POSITION UNIQUENES -------------------- ---------- --------------- --------- PK_TB_IND0101        EMPNO                    1 UNIQUE IND_TB_IND02         JOB                      2 NONUNIQUE IND_TB_IND02         ENAME                    1 NONUNIQUE IND_TB_IND02         MGR                      3 NONUNIQUE

uniqueness 表示唯一性索引(主键自动生成)，还是非唯一性索引(手动生成，未添加唯一性约束)

导致索引失效的原因

1、where语句导致失效

某些select语句中where子句不适用索引，举例说明

1)‘！=’将不适用索引而'>'可以(索引只能查君什么在表中，而不能查询什么不在表里)

SQL> alter session set statistics_level=all;   Session altered.   SQL> /   Session altered.   SQL> select empno,ename from tb_ind01 where empno!=0;    EMPNO ENAME ------ ----------   7369 SMITH   7499 ALLEN   7521 WARD   7566 JONES   7654 MARTIN   7698 BLAKE   7782 CLARK   7788 SCOTT   7839 KING   7844 TURNER   7876 ADAMS   7900 JAMES   7902 FORD   7934 MILLER   14 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 3935626125   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |    14 |   140 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_IND01 |    14 |   140 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("EMPNO"<>0)   SQL>  select empno,ename from tb_ind01 where empno>0;    EMPNO ENAME ------ ----------   7369 SMITH   7499 ALLEN   7521 WARD   7566 JONES   7654 MARTIN   7698 BLAKE   7782 CLARK   7788 SCOTT   7839 KING   7844 TURNER   7876 ADAMS   7900 JAMES   7902 FORD   7934 MILLER   14 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2928769922   ---------------------------------------------------------------------------------- |Id|Operation                           |Name         |Rows|Bytes|Cost(%CPU)|Time    | ---------------------------------------------------------------------------------- | 0|SELECT STATEMENT                    |             | 14 | 140 |  2   (0)| 00:00:01| | 1| TABLE ACCESS BY INDEX ROWID BATCHED|TB_IND01     | 14 | 140 |  2   (0)| 00:00:01| |*2|  INDEX RANGE SCAN                  |PK_TB_IND0101| 14 |     |  1   (0)| 00:00:01| ----------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("EMPNO">0)

2)'||'字节连接符导致索引失效

SQL> select empno,ename from tb_ind01 where empno||ENAME='7839KING';    EMPNO ENAME ------ ----------   7839 KING     Execution Plan ---------------------------------------------------------- Plan hash value: 3935626125   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    10 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_IND01 |     1 |    10 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(TO_CHAR("EMPNO")||"ENAME"='7839KING')

2、索引列上使用not

not会产生和在索引列上使用函数相同的影响，因此当oracle遇到not的时候会停止使用索引，转而全表扫描

SQL> select empno,ename from tb_ind01 where empno not in 7839;    EMPNO ENAME ------ ----------   7369 SMITH   7499 ALLEN   7521 WARD   7566 JONES   7654 MARTIN   7698 BLAKE   7782 CLARK   7788 SCOTT   7844 TURNER   7876 ADAMS   7900 JAMES   7902 FORD   7934 MILLER   13 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 3935626125   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |    13 |   130 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_IND01 |    13 |   130 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("EMPNO"<>7839)

3、在索引列上使用计算

where子句中，如果索引列是函数的一部分，优化器将不适用索引而转用全表扫描

SQL> select EMPNO from tb_ind01 where empno*10 >7000;    EMPNO ------   7369   7499   7521   7566   7654   7698   7782   7788   7839   7844   7876   7900   7902   7934   14 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 1033130739   ---------------------------------------------------------------------------------- | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |               |     1 |     4 |     1   (0)| 00:00:01 | |*  1 |  INDEX FULL SCAN | PK_TB_IND0101 |     1 |     4 |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("EMPNO"*10>7000)   SQL> select EMPNO from tb_ind01 where empno>70000/10;    EMPNO ------   7369   7499   7521   7566   7654   7698   7782   7788   7839   7844   7876   7900   7902   7934   14 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 1997662084   ---------------------------------------------------------------------------------- | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |               |    14 |    56 |     1   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| PK_TB_IND0101 |    14 |    56 |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("EMPNO">7000)

4、复合索引使用顺序错误

 

SQL> create table TB_IND02(id number(5),name varchar2(5),job varchar2(5),other varchar2(5));   Table created.   SQL> insert into TB_IND02 select 1,'AAA','AAA','OTHER' FROM DUAL;   1 row created.   SQL> insert into TB_IND02 select 2,'BBB','BBB','OTHER' FROM DUAL;   1 row created.   SQL> insert into TB_IND02 select 3,'CCC','CCC','OTHER' FROM DUAL;   1 row created.   SQL> insert into TB_IND02 select 4,'DDD','DDD','OTHER' FROM DUAL;   1 row created.   SQL> insert into TB_IND02 select 5,'EEE','EEE','OTHER' FROM DUAL;   1 row created.   SQL> SELECT * FROM TB_IND02;           ID NAME  JOB   OTHER ---------- ----- ----- -----          1 AAA   AAA   OTHER          2 BBB   BBB   OTHER          3 CCC   CCC   OTHER          4 DDD   DDD   OTHER          5 EEE   EEE   OTHER   SQL> create index ind_tb_ind02_01 on tb_ind02(other,id,name);   Index created.   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_IND02';   INDEX_NAME           COLUMN_NAM COLUMN_POSITION UNIQUENES -------------------- ---------- --------------- --------- IND_TB_IND02_01      ID                       2 NONUNIQUE IND_TB_IND02_01      NAME                     3 NONUNIQUE IND_TB_IND02_01      OTHER                    1 NONUNIQUE   SQL> SELECT * FROM TB_IND02 WHERE ID=1 AND name='AAA';           ID NAME  JOB   OTHER ---------- ----- ----- -----          1 AAA   AAA   OTHER     Execution Plan ---------------------------------------------------------- Plan hash value: 2925895819   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    25 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_IND02 |     1 |    25 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("ID"=1 AND "NAME"='AAA')   Note -----    - dynamic statistics used: dynamic sampling (level=2)   SQL> SELECT * FROM TB_IND02 WHERE other='OTHER' AND ID=1 AND name='AAA';           ID NAME  JOB   OTHER ---------- ----- ----- -----          1 AAA   AAA   OTHER     Execution Plan ---------------------------------------------------------- Plan hash value: 1452223693   -------------------------------------------------------------------------------------------- |Id |Operation                           |Name           |Rows|Bytes|Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |  0|SELECT STATEMENT                    |               |   1|   25|    2   (0)| 00:00:01 | |  1| TABLE ACCESS BY INDEX ROWID BATCHED|TB_IND02       |   1|   25|    2   (0)| 00:00:01 | |* 2|  INDEX RANGE SCAN                  |IND_TB_IND02_01|   1|     |    1   (0)| 00:00:01 | --------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OTHER"='OTHER' AND "ID"=1 AND "NAME"='AAA')   Note -----    - dynamic statistics used: dynamic sampling (level=2)

5、改变索引类型

当比较不同数据类型的数据时，oracle对列进行简单的类型转换。

查看TB_IND01表结构

创建测试表tb_ind03并插入数据

SQL> create table tb_ind03 (id1 number(5),id2 varchar2(5));   Table created.   SQL> insert into tb_ind03 values(1,'1');   1 row created.   SQL> insert into tb_ind03 values(2,'2');   1 row created.   SQL> insert into tb_ind03 values(3,'3');   1 row created.   SQL> create index ind_tb_ind0301 on tb_ind03(id1);   Index created.   SQL> create index ind_tb_ind0302  on tb_ind03(id2);   Index created.   SQL> desc tb_ind03;  Name                    Null?    Type  ----------------------- -------- ----------------  ID1                              NUMBER(5)  ID2                              VARCHAR2(5)   SQL>  _IND03';   INDEX_NAME           COLUMN_NAM COLUMN_POSITION UNIQUENES -------------------- ---------- --------------- --------- IND_TB_IND0301       ID1                      1 NONUNIQUE IND_TB_IND0302       ID2                      1 NONUNIQUE

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';          ID1 ID2 ---------- -----          1 1     Execution Plan ---------------------------------------------------------- Plan hash value: 372920922   ------------------------------------------------------------------------------------------------------ | Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                    |                |     1 |    17 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TB_IND03       |     1 |    17 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IND_TB_IND0302 |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID2"='1')   Note -----    - dynamic statistics used: dynamic sampling (level=2)

不用引号呢？果然无论是否使用字符转换，都发生了转换。导致索引失效。

SQL> select * from tb_ind03 where id2=1;          ID1 ID2 ---------- -----          1 1     Execution Plan ---------------------------------------------------------- Plan hash value: 697787577   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    17 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_IND03 |     1 |    17 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(TO_NUMBER("ID2")=1)   Note -----    - dynamic statistics used: dynamic sampling (level=2)   SQL> select * from tb_ind03 where id2=to_number('1');          ID1 ID2 ---------- -----          1 1     Execution Plan ---------------------------------------------------------- Plan hash value: 697787577   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     1 |    17 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_IND03 |     1 |    17 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(TO_NUMBER("ID2")=1)   Note -----    - dynamic statistics used: dynamic sampling (level=2)

因此一定要注意索引的使用规范

 

基于函数的索引

测试

创建测试表tbi01定义参照emp，保留数据

SQL> create table tbi01 as select * from emp;   Table created.   SQL> select * from tbi01;    EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM  DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- -------   7369 SMITH      CLERK      7902 1980-12-17 00:00:00   800            20   7499 ALLEN      SALESMAN   7698 1981-02-20 00:00:00  1600   300      30   7521 WARD       SALESMAN   7698 1981-02-22 00:00:00  1250   500      30   7566 JONES      MANAGER    7839 1981-04-02 00:00:00  2975            20   7654 MARTIN     SALESMAN   7698 1981-09-28 00:00:00  1250  1400      30   7698 BLAKE      MANAGER    7839 1981-05-01 00:00:00  2850            30   7782 CLARK      MANAGER    7839 1981-06-09 00:00:00  2450            10   7788 SCOTT      ANALYST    7566 1987-04-19 00:00:00  3000            20   7839 KING       PRESIDENT       1981-11-17 00:00:00  5000            10   7844 TURNER     SALESMAN   7698 1981-09-08 00:00:00  1500     0      30   7876 ADAMS      CLERK      7788 1987-05-23 00:00:00  1100            20   7900 JAMES      CLERK      7698 1981-12-03 00:00:00   950            30   7902 FORD       ANALYST    7566 1981-12-03 00:00:00  3000            20   7934 MILLER     CLERK      7782 1982-01-23 00:00:00  1300            10   14 rows selected.

如果不知道 scott 的名字大小写的情况下，查询 scott 相关信息

SQL> select * from tbi01 where lower(ename)='scott';    EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM  DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- -------   7788 SCOTT      ANALYST    7566 1987-04-19 00:00:00  3000            20

查看执行计划

在tbi01表上创建索引

SQL> create index ind_tbi01 on tbi01(ename);   Index created.   SQL> create index ind_tbi01 on tbi01(ename);   Index created.   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='TBI01';   INDEX_NAME COLUMN_NAM COLUMN_POSITION UNIQUENES ---------- ---------- --------------- --------- IND_TBI01  ENAME                    1 NONUNIQUE

先用原始方法测试，生成执行计划

SQL> explain plan set statement_id='info 20210107' into plan_table for select * from tbi01 where ename='SCOTT';   Explained.   SQL> 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';   OPERATION            OPTIONS                        OBJECT_NAM   POSITION -------------------- ------------------------------ ---------- ---------- SELECT STATEMENT                                                        2   TABLE ACCESS       BY INDEX ROWID BATCHED         TBI01               1     INDEX            RANGE SCAN                     IND_TBI01           1

如果使用忽略大小写敏感性的查询呢，显示full，未使用索引

SQL> explain plan set statement_id='info 20210107' into plan_table for select * from tbi01 where lower(ename)='scott';   Explained.   SQL> 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';   OPERATION            OPTIONS                        OBJECT_NAM   POSITION -------------------- ------------------------------ ---------- ---------- SELECT STATEMENT                                                        2   TABLE ACCESS       FULL                           TBI01               1

那么如何既能使用索引，又忽略大小写呢

就要使用基于函数的索引了

删除并从键索引

SQL> drop index ind_tbi01;   Index dropped.   SQL> create index ind_tbi01 on tbi01(lower(ename));   Index created.   SQL>  explain plan set statement_id='info 2021010702' into plan_table for select * from tbi01 where lower(ename)='scott';   Explained.   SQL> select lpad(' ',2*(LEVEL-1))||operation operation,options,object_name,position from plan_table start with id= 0 AND statement_id = 'info 2021010702' connect by prior id = parent_id AND statement_id ='info 2021010702';   OPERATION            OPTIONS                        OBJECT_NAM   POSITION -------------------- ------------------------------ ---------- ---------- SELECT STATEMENT                                                        2   TABLE ACCESS       BY INDEX ROWID BATCHED         TBI01               1     INDEX            RANGE SCAN                     IND_TBI01           1

删除索引

SQL> drop index ind_tbi01;   Index dropped.

如果是因为主键或者唯一性约束而产生的索引，再删除相应约束或者删除表的时候，索引也会自 动删除。

 

3、不可见索引

参见https://blog.csdn.net/u012778985/article/details/113103502