索引基础(一)

来源：互联网发布：知和行书局的书能买吗编辑：程序博客网时间：2024/06/10 00:25

一：基本索引概念

1、当前用户：

SQL> show user;                USER is "SCOTT"

2、实验的表结构：

SQL> desc emp Name                                     Null?    Type ------------------------------------------------- ---------------------------- EMPNO                                   NOT NULL  NUMBER(4) ENAME                                             VARCHAR2(10) JOB                                               VARCHAR2(9) MGR                                               NUMBER(4) HIREDATE                                           DATE SAL                                               NUMBER(7,2) COMM                                              NUMBER(7,2) DEPTNO                                            NUMBER(2)

3、创建索引：

SQL> create index emp_id1 onemp(empno,ename,deptno);Index created.SQL> create index emp_id2 on emp(sal);Index created.

4、查看创建的索引：

SQL> select table_name,index_name from user_indexes where table_name='EMP';TABLE_NAME                     INDEX_NAME------------------------------------------------------------EMP                            PK_EMPEMP                            EMP_ID2EMP                            EMP_ID1

5、查询对应索引的列：

SQL> column index_name format a12;SQL> column column_name format a8;SQL> column table_name format a8;SQL> select table_name,index_name,column_name,column_position 2  from user_ind_columns 3  order by 1,2,4; TABLE_NA INDEX_NAME   COLUMN_N  COLUMN_POSITION-------- ------------ --------- --------------DEPT     PK_DEPT      DEPTNO                 1EMP      EMP_ID1      EMPNO                  1EMP      EMP_ID1      ENAME                  2EMP      EMP_ID1      DEPTNO                 3EMP      EMP_ID2      SAL                    1EMP      PK_EMP       EMPNO                  16 rows selected.

二：不可视索引

把索引变为不可见，到达隐藏索引的作用，但数据库会一直维护这个索引。

基本语句：

alter index idx1 invisible;

alter index idx1 visible;

create index ... invisible;

开启执行计划：

SQL> set autotrace on;

1、创建一个不可见索引：

SQL> create table emp_inv as select * from emp;SQL> create index emp_inv_idx on emp_inv(deptno) invisible;Index created.

查看一下执行计划，执行了全表扫描：

SQL> select count(1) 2  from emp_inv 3  where deptno=30; COUNT(1)----------   6Execution Plan----------------------------------------------------------Plan hash value: 1073818948------------------------------------------------------------------------------| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0| SELECT STATEMENT   |         |    1 |    13 |      3  (0)| 00:00:01 ||   1|  SORT AGGREGATE    |         |    1 |    13 |            |          ||*  2|   TABLE ACCESS FULL| EMP_INV |    6 |    78 |     3   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2- filter("DEPTNO"=30)

2、hint可以强制走索引：

SQL> select /*+ use_invisible_indexes */ count(1) 2  from emp_inv 3  where deptno = 30; COUNT(1)----------   6Execution Plan----------------------------------------------------------Plan hash value: 836537606---------------------------------------------------------------------------------| Id | Operation         | Name        | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------|   0| SELECT STATEMENT  |             |     1 |   13 |     1   (0)| 00:00:01||   1|  SORT AGGREGATE   |             |     1 |   13 |            |||*  2|   INDEX RANGE SCAN| EMP_INV_IDX |     6 |   78 |     1   (0)| 00:00:01|---------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2- access("DEPTNO"=30)

3、设置索引为可见：

SQL> alter index emp_inv_idx visible;Index altered.

再次查看一下执行计划，执行了索引扫描：

SQL> select count(1) 2  from emp_inv 3  where deptno=30; COUNT(1)----------     6Execution Plan----------------------------------------------------------Plan hash value: 836537606---------------------------------------------------------------------------------| Id | Operation         | Name        | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------|   0| SELECT STATEMENT  |             |     1 |   13 |     1   (0)| 00:00:01||   1|  SORT AGGREGATE   |             |     1 |   13 |            |||*  2|   INDEX RANGE SCAN| EMP_INV_IDX |     6 |   78 |     1   (0)| 00:00:01|---------------------------------------------------------------------------------Predicate Information (identified byoperation id):--------------------------------------------------   2- access("DEPTNO"=30)

4、使用hint强制关闭索引，走了全表扫描：

SQL> select /*+ no_index(emp_inv emp_inv_idx) */ count(1) 2  from emp_inv 3  where deptno=30; COUNT(1)----------       6Execution Plan----------------------------------------------------------Plan hash value: 1073818948------------------------------------------------------------------------------| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0| SELECT STATEMENT   |         |    1 |    13 |      3  (0)| 00:00:01 ||   1|  SORT AGGREGATE    |         |    1 |    13 |            |          ||*  2|   TABLE ACCESS FULL| EMP_INV |    6 |    78 |     3   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2- filter("DEPTNO"=30)

5、将索引设置为不可见，查看视图：

SQL> alter index emp_inv_idx invisible;Index altered. SQL> select index_name,visibility from user_indexes where index_name='EMP_INV_IDX';INDEX_NAME                     VISIBILIT------------------------------ ---------EMP_INV_IDX                    INVISIBLE

注释：使用不可见索引，即使设置为不可见状态，但也依然被维护着。

三：组合索引

之前《一：基本索引概念》中建立了emp_id1 onemp(empno,ename,deptno) 的组合索引；

其中empno为前导列，如果查询中没有empno条件，而使用了ename或者deptno条件的话，会使用跳跃式扫描功能(9i及之后版本)

1、以组合索引的条件查询一下，执行计划走了emp_id1组合索引的范围扫描

SQL> select empno ,ename from emp  where empno=7369 and ename='SMITH';    EMPNO ENAME---------- ----------     7369 SMITHExecution Plan----------------------------------------------------------Plan hash value: 1397020097----------------------------------------------------------------------------| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0| SELECT STATEMENT |         |     1 |   10 |     1   (0)| 00:00:01 ||*  1|  INDEX RANGE SCAN| EMP_ID1 |     1 |   10 |     1   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   1- access("EMPNO"=7369 AND "ENAME"='SMITH')

2、不使用empno前导列，使用组合索引中的其他条件，执行计划里执行跳跃式扫描功能：

SQL> select empno ,ename,deptno from emp where deptno=30;    EMPNO ENAME          DEPTNO---------- ---------- ----------     7499 ALLEN              30     7521 WARD               30     7654 MARTIN             30     7698 BLAKE              30     7844 TURNER             30     7900 JAMES              306 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1294623169----------------------------------------------------------------------------| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0| SELECT STATEMENT |         |     5 |   65 |     1   (0)| 00:00:01 ||*  1|  INDEX SKIP SCAN | EMP_ID1 |     5 |   65 |     1   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   1- access("DEPTNO"=30)      filter("DEPTNO"=30)

四：索引抑制(索引失效的情况)

1、使用不等于符号(<>、!=)

使用不等于，查询一下数据，执行计划走了索引全扫描，没有使用主键的索引唯一扫描

SQL> select empno from emp where empno<>7369;    EMPNO----------     7499     7521      7566     7654     76985 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3770432633----------------------------------------------------------------------------| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0| SELECT STATEMENT |         |    13 |   52 |     1   (0)| 00:00:01 ||*  1|  INDEX FULL SCAN | EMP_ID1 |    13 |   52 |     1   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   1- filter("EMPNO"<>7369)

2、使用is null 或者 is not null

注释：Oracle在b-tree索引上不会有null值索引，但位图索引中可以

使用null，查询一下数据，执行计划走了索引全扫描，没有使用主键的索引唯一扫描(可以在建表时创建not null 来限制必须不为空)

SQL> select empno from emp where empno is not null;    EMPNO----------     7369     7499     7521     7566     7654     76986 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 3770432633----------------------------------------------------------------------------| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0| SELECT STATEMENT |         |    14 |   56 |     1   (0)| 00:00:01 ||   1|  INDEX FULL SCAN | EMP_ID1 |    14 |   56 |     1   (0)| 00:00:01 |

3、使用like

创建一个索引

SQL> Create table emp_like as select * from emp;Table created.SQL> create index emp_ename_nu on emp_like(ename);Index created.

%在值得前面时，查看执行计划，走了全表扫描

SQL> select empno,ename 2  from emp_like 3  where ename like '%BLAKE%';    EMPNO ENAME---------- ----------     7698 BLAKEExecution Plan----------------------------------------------------------Plan hash value: 1780523918------------------------------------------------------------------------------| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0| SELECT STATEMENT  |          |    1 |    20 |      3  (0)| 00:00:01 ||*  1|  TABLE ACCESS FULL| EMP_LIKE |    1 |    20 |      3  (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   1- filter("ENAME" IS NOT NULL AND "ENAME" LIKE '%BLAKE%')

%在值得后面时，查看执行计划，走了索引

SQL> select empno,ename 2  from emp_like 3  where ename like 'BLAKE%';    EMPNO ENAME---------- ----------     7698 BLAKEExecution Plan----------------------------------------------------------Plan hash value: 1324104591--------------------------------------------------------------------------------------------| Id | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)|Time     |--------------------------------------------------------------------------------------------|   0| SELECT STATEMENT            |               |     1 |    20 |      2  (0)| 00:00:01 ||   1|  TABLE ACCESS BY INDEX ROWID|     EMP_LIKE  |     1 |    20 |     2   (0)| 00:00:01 || *2 |   INDEX RANGE SCAN          |  EMP_ENAME_NU |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2- access("ENAME" LIKE 'BLAKE%')      filter("ENAME" LIKE 'BLAKE%')

4、使用函数

除非使用函数索引，不然oracle会忽略使用了函数的列的索引

创建一个索引

SQL> Create table emp_fun as select * from emp;Table created.SQL> create index emp_hire on emp_fun(hiredate);Index created.

使用了函数，查看执行计划，走了全表扫描

SQL> select empno,ename,deptno 2  from emp_fun 3  where trunc(hiredate) ='17-DEC-80';    EMPNO ENAME          DEPTNO---------- ---------- ----------     7369 SMITH              20Execution Plan----------------------------------------------------------Plan hash value: 4053772541-----------------------------------------------------------------------------| Id | Operation         | Name    | Rows | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0| SELECT STATEMENT  |         |     1 |   42 |      3  (0)| 00:00:01 ||*  1|  TABLE ACCESS FULL| EMP_FUN |     1 |   42 |     3   (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   1- filter(TRUNC(INTERNAL_FUNCTION("HIREDATE"))='17-DEC-80')

不使用函数的情况，执行了索引范围扫描

SQL> select empno,ename,deptno   from emp_fun   where hiredate   = '17-DEC-80';    EMPNO ENAME          DEPTNO---------- ---------- ----------     7369 SMITH              20Execution Plan----------------------------------------------------------Plan hash value: 2043383837----------------------------------------------------------------------------------------| Id | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0| SELECT STATEMENT            |         |     1 |    42 |     2   (0)| 00:00:01 ||   1|  TABLE ACCESS BY INDEX ROWID|EMP_FUN  |     1 |    42 |     2   (0)| 00:00:01 ||*  2|   INDEX RANGE SCAN          |EMP_HIRE |     1 |       |     1   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------   2- access("HIREDATE"='17-DEC-80')

0 0