本来以为对数据库小有了解，结果是冰山一角，完全赶不上人家啊，面试官随口一问，问了两个问题，结果全错。好吧
问题1：建好索引后，在select中使用like是否会使用索引？
问题2：给定组合索引，什么情况下会使用索引，什么情况不会使用索引？
（1）首先来看第一个问题从网上找个例子，很能说明问题啊？
总结一句就是like中通配符%在前就不会使用索引，在后以常量开头就可以使用索引
scott@DB01> create table t as select empno,ename,sal from emp;
Table created.
scott@DB01>
select * from t;
EMPNO
ENAME
SAL
----- ------ ----------
7369
SMITH
1000
7499
ALLEN
1600
7521
WARD
1250
7566
JONES
2975
7654
MARTIN
1250
7698
BLAKE
2850
7782
CLARK
2450
7788
SCOTT
3000
7839
KING
5000
7844
TURNER
1500
7876
ADAMS
1100
7900
JAMES
950
7902
FORD
3000
7934
MILLER
1300
14 rows selected.
在ename列上建一个b*tree索引
scott@DB01> create
index t_n_idx on t(ename);
Index created.
打开autotrace功能，查看语句的执行计划
scott@DB01> set
autotrace trace exp
下面的语句会使用到索引t_n_idx,因为这是一个精确匹配
scott@DB01> select *
from t where ename='KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 539952282
---------------------------------------------------------------------------------------
| Id |
Operation
| Name |
Rows | Bytes | Cost (%CPU)|
Time
|
---------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
|
1 | 33
|
2 (0)| 00:00:01 |
| 1 | TABLE
ACCESS BY INDEX ROWID|
T
|
1 | 33
|
2 (0)| 00:00:01 |
|* 2 | INDEX
RANGE
SCAN
| T_N_IDX
|
1
|
|
1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='KING')
Note
-----
- dynamic sampling used for
this statement
测试索引
t_n_idx在like条件下的使用，这里我们要注意like后面通配符出现的位置，通配符出现的位置不同，会导致oracle选择不同的执行计划
使用索引的场景
scott@DB01> select *
from t where ename like 'KI%';
Execution Plan
----------------------------------------------------------
Plan hash value: 539952282
---------------------------------------------------------------------------------------
| Id |
Operation
| Name |
Rows | Bytes | Cost (%CPU)|
Time
|
---------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
|
1 | 33
|
2 (0)| 00:00:01 |
| 1 | TABLE
ACCESS BY INDEX ROWID|
T
|
1 | 33
|
2 (0)| 00:00:01 |
|* 2 | INDEX
RANGE
SCAN
| T_N_IDX
|
1
|
|
1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME" LIKE
'KI%')
filter("ENAME" LIKE 'KI%')
Note
-----
- dynamic sampling used for
this statement
不使用索引的场景
scott@DB01> select *
from t where ename like '%NG';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id |
Operation
| Name | Rows | Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
|
1 | 33
|
3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL|
T
|
1 | 33
|
3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME" LIKE '%NG')
Note
-----
- dynamic sampling used for
this statement
对于这种情况是可以进行优化的，通过调用reverse就又可以使用索引了
select * from t where reverse(ename) like reverse('%NG');
注意reverse是这样的
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /
表已分析。
SQL> SELECT * from T WHERE OBJECT_TYPE='LOB';
已选择20行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=20 Bytes=1740)
2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=2 Card=20)
正如我们所期望的，由于使用了组合索引的前导列并且访问了表中的少量记录，Oracle明智地选择了索引扫描。
1) 没有查询条件，或者查询条件没有建立索引
2) 在查询条件上没有使用引导列
3) 查询的数量是大表的大部分，应该是30％以上。
4) 索引本身失效
5) 查询条件使用函数在索引列上（见12）
6) 对小表查询
7) 提示不使用索引
8) 统计数据不真实
9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),
但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn='13333333333';
11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
12)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where round(id)=10;
说明，此时id的索引已经不起作用了 正确的例子：首先建立函数索引，
create index test_id_fbi_idx on test(round(id));
然后 select * from test where round(id)=10; 这时函数索引起作用了 1,<> 2,单独的>,<,(有时会用到，有时不会)
3,like "%_" 百分号在前.
4,表没分析.
5,单独引用复合索引里非第一位置的索引列.
6,字符型字段为数字时在where条件里不添加引号.
7,对索引列进行运算.需要建立函数索引.
8,not in ,not exist.
9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
10, 索引失效。
11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上
12,有时都考虑到了 但就是不走索引,drop了从建试试在
13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
14,联合索引 is not null 只要在建立的索引列（不分先后）都会走,
in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,
其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,
或者=一个值；当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,
以上两种情况索引都会走。其他情况不会走。
posted on
