索引应该是sql优化中的一员大将了，近期学习了一下索引，希望能在此梳理一下我看到的和理解的有关索引的一些知识点。

1、索引列有null值，索引会失效，这是真的吗？

经过试验，是真的！索引列只要有null值，即使加上/*+INDEX(TABLE_NAME,INDEX_NAME)*/查询过程中也不会走索引

CREATE INDEX PK_TEST ON EMP(COMM);

SQL> select/*+INDEX(emp,pk_test)*/ comm from emp;

已选择13行。

执行计划

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12 | 24 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 12 | 24 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

1.1 如果是组合索引又会出现什么情况呢?好好奇啊，带我来一探究竟。。。。

SQL>create index index_emp on emp(empno,comm);

SQL> select empno,comm from emp;

EMPNO COMM

---------- ----------

7369

7499 300

7521 500

7566

7654 1400

7698

7782

7788

7839

7844 0

7876

EMPNO COMM

---------- ----------

7900

7902

7934

已选择14行。

执行计划

----------------------------------------------------------

Plan hash value: 1443012493

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 364 | 1 (0)| 00:00:01 |

| 1 | INDEX FULL SCAN | INDEX_EMP | 14 | 364 | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------

可以看出来，组合索引中有一个字段存在空值，还是可以通过走索引进行查询。但是如果存在两个值都是空值的情况呢？

SQL> select * from t;-----t中存在组合唯一索引t_idx(x,y)

X Y

---------- ----------

1 1

1

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 4 | 104 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

SQL> select /*+index(t,t_idx)*/ * from t;

X Y

---------- ----------

1 1

1

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 4 | 104 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

SQL> analyze index t_idx validate structure;

索引已分析

SQL> select name,lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T_IDX 3

1

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| T | 4 | 104 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

SQL> analyze index t_idx validate structure;

索引已分析

SQL> select name,lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

T_IDX 3

最后一行（null,null）并不在索引列中

create table t(x int,y int);

create unique index t_idx on t(x,y);

insert into t(x,y) values (1,null);

insert into t(x,y) values (null,1);

insert into t(x,y) values (null,null);

insert into t(x,y) values (1,1);

insert into t(x,y) values (1,2);

select* from t;

说明oracle并不认为（null，null）相等，但是（1，null）与（1，null）是相等的。只有在索引列中至少有一个not null值，才会使用索引。

2.视图能使用索引，这是真的么？

SQL> create or replace view v_emp as select * from emp;

视图已创建。

SQL> create index id_test1 on V_EMP(empno);

create index id_test1 on V_EMP(empno) 第 1 行出现错误:

ORA-01702: 视图不适用于此处

SQL> create index id_test1 on V_EMP(ename);

create index id_test1 on V_EMP(ename)

第 1 行出现错误:

ORA-01702: 视图不适用于此处

SQL> select empno from v_emp;

执行计划

----------------------------------------------------------------------------------------

Plan hash value: 179099197

--------------------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12 | 48 | 1 (0)| 00:00:01 |

| 1 | INDEX FULL SCAN | PK_EMP | 12 | 48 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

经过试验可以看出，使用视图查询时，可以完全考虑使用基表中已有的索引，对视图建立索引实际上是对基表建立索引

（以上可看，普通视图不能建立索引，但是对于物化视图则可以）。

当基表被删除时，视图会失效，索引会自动被清除。

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OBJECT_T

YPE='VIEW' AND OWNER='SCOTT';

OWNER OBJECT_NAM OBJECT_TYP STATUS

---------- ---------- ---------- -------

SCOTT V_EMP_DEPT VIEW INVALID

10

SCOTT V_EMP_DEPT VIEW INVALID

AVG

SCOTT V_AVG VIEW INVALID

SCOTT V2_DEPT10 VIEW INVALID

3.太长了，不好布局，后面再接着好了。。。嘿嘿

ITPub博客

关于索引的几个问题

最新文章