问题描述
请问SQL什么情况下不走索引，帮忙总结一下
专家解答
有索引但没走上索引的可能情况：
1、谓词中出现NULL过滤条件导致没有走索引：
创建索引：
SQL> create index ind_test_objects_object_name on TEST_OBJECTS(Object_Name); Index created. SQL> select count(*) from system.test_objects A where object_name is null; COUNT(*) ---------- 0 Elapsed: 00:00:31.52 Execution Plan ---------------------------------------------------------- Plan hash value: 3799704240 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 24 | 55569 (1)| 00:11:07 | | 1 | SORT AGGREGATE | | 1 | 24 | | | |* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 1 | 24 | 55569 (1)| 00:11:07 |
如果是单列索引，则该列上值为空的记录不会被存入索引。如果创建一个常量和该列构成的2列的组合索引，由于常量为定值，恒不为空，所以，即使列上的值为空，但由于不是构成索引的2列均为空值，所以，访记录仍会存入索引。那么，这时再用 object_name is null的条件做检索时，就可以在索引中定位相关记录了。
优化后：
SQL> create index ind_test_objects_object_name2 on SYSTEM.TEST_OBJECTS(Object_Name,0);
Index created.
SQL> select count(*) from system.test_objects A where object_name is null;
COUNT(*)
----------
0
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 431813695
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | INDEX RANGE SCAN| IND_TEST_OBJECTS_OBJECT_NAME2 | 1 | 24 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
4 physical reads
0 redo size
525 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1 rows processed2、谓词中出现函数转换导致没有走索引：
原SQL：
SQL> select count(status) from SYSTEM.TEST_OBJECTS where round(object_id)=20;
COUNT(STATUS)
-------------
256
Elapsed: 00:00:23.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3799704240
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 55648 (1)| 00:11:08 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 143K| 1681K| 55648 (1)| 00:11:08 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROUND("OBJECT_ID")=20)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
204682 consistent gets
204676 physical reads
0 redo size
532 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed检索条件是“round(object_id)=20”，如果希望能够用上索引，要么，我们用某种方法，不对OBJECT_ID列使用ROUND（）函数，而还能确保SQL等价；要么，我们创建一个新的函数索引。以ROUND（OBJECT_ID）的形式创建。
创建索引：
SQL> create index ind_object_id2 on SYSTEM.TEST_OBJECTS(round(Object_id));
SQL> select count(status) from SYSTEM.TEST_OBJECTS where round(object_id)=20;
COUNT(STATUS)
-------------
256
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1772782182
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 55448 (1)| 00:11:06 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 143K| 2803K| 55448 (1)| 00:11:06 |
|* 3 | INDEX RANGE SCAN | IND_OBJECT_ID2 | 57410 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(ROUND("OBJECT_ID")=20)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
135 consistent gets
132 physical reads
0 redo size
532 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed3、统计信息不准确导致SQL不走索引：
SQL> delete from system.test_objects; SQL> insert into system.test_objects select * from dba_objects where rownum < 2;
收集统计信息：
EXEC DBMS_STATS.gather_table_stats('SYSTEM','TEST_OBJECTS');
SQL> insert into system.test_objects select * from dba_objects;
SQL> select * from system.test_objects a where object_name='abc';
no rows selected
Elapsed: 00:00:04.53
Execution Plan
----------------------------------------------------------
Plan hash value: 3570092908
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 1 | 73 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='abc')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
25598 consistent gets
25588 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed表TEST_OBJECTS有1794048行数据：
SQL> select count(*), count(distinct object_name) from system.TEST_OBJECTS; COUNT(*) COUNT(DISTINCTOBJECT_NAME) ---------- -------------------------- 1794048 68166
object_name字段上也存在索引：
INDEX_NAME COLUMN_NAME COLUMN_POSITION --------------------------------- -------------------- --------------- IND_TEST_OBJECTS_OBJECT_NAME OBJECT_NAME 1
原因：
由于标的统计信息不准确，导致优化器在选择错误的访问方式。以下是标的统计数据。
SQL> select NUM_ROWS, BLOCKS from dba_tab_statistics a where table_name='TEST_OBJECTS'; NUM_ROWS BLOCKS ---------- ---------- 1 1
收集表的统计信息，确保统计信息与实际情形相符。
SQL> EXEC DBMS_STATS.gather_table_stats('SYSTEM','TEST_OBJECTS');
SQL> select NUM_ROWS, BLOCKS from dba_tab_statistics a where table_name='TEST_OBJECTS';
NUM_ROWS BLOCKS
---------- ----------
1794048 25587
SQL> select * from system.test_objects a where object_name='abc';
no rows selected
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 260470024
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 2450 | 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 25 | 2450 | 20 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| IND_TEST_OBJECTS_OBJECT_NAME | 25 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='abc')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed4、在WEHRE条件中like中关键字两边都有"%"导致无法使用索引
SQL> create index ind_OBJECT_NAME on system.test_objects(object_name);
SQL> select object_id, object_name from system.test_objects where object_name like '%TORA%'
12160 rows selected.
Elapsed: 00:00:10.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3570092908
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 717K| 19M| 55607 (1)| 00:11:08 |
|* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 717K| 19M| 55607 (1)| 00:11:08 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" LIKE '%TORA%' AND "OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
205493 consistent gets
204676 physical reads
0 redo size
518803 bytes sent via SQL*Net to client
9430 bytes received via SQL*Net from client
812 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12160 rows processed将WHERE条件中的like条件从'%TORA%'改成'TORA%'（前提是去除前面的%后，结果是等价的。）优化后：
SQL> select object_id, object_name from system.test_objects where object_name like 'TORA%';
128 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3527391799
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 5800 | 141 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 200 | 5800 | 141 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_OBJECT_NAME | 200 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'TORA%')
filter("OBJECT_NAME" LIKE 'TORA%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
141 consistent gets
3 physical reads
0 redo size
2762 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
128 rows processed5、查询条件值与列类型不一致造成未使用上索引
SQL> create table t1 (id varchar2(10),c1 varchar2(32));
Table created.
SQL> insert into t1 select rownum id,lpad('T1',20,'*') c1 from dual connect by rownum<=10000;
10000 rows created.
SQL> commit;
Commit complete.在ID列上创建索引：
SQL> create index ind_t1_id on t1(id); Index created.
发出以下查询SQL：
SQL> set autot traceonly
SQL> select * from t1 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 25 | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
94 consistent gets
0 physical reads
0 redo size
604 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed将条件值改为字符1，而不是数值1，就可以避免隐式转换的发生，从而使用上索引。优化后：
SQL> select * from t1 where id='1';
Execution Plan
----------------------------------------------------------
Plan hash value: 2892477391
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='1')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
608 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed6、查询条件列进行数学运算造成未使用上索引
创建表T1，其中ID列上创建索引
SQL> create table t1 as select rownum id,lpad(rownum,10,'*') c1 from dual connect by rownum<=10000; Table created. SQL> create index ind_t1_object_id on t1(id); Index created.
发出以下查询SQL：
SQL> select * from t1 where id+1=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 35 | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"+1=10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed将id+1=10做等价数学转换为id=10-1，从而避免在查询条件列上进行数学运算。优化后：
SQL> select * from t1 where id=10-1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3177125663
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
599 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed7、索引列在 IN 或者多个 OR 语句中；
8、是否使用的是不可见索引；
SQL> create table test as select * from SH.sales;
Table created.
SQL> create index INDEX_CUST_ID on test(CUST_ID);
Index created.
SQL> select PROD_ID from test where CUST_ID=8888;
192 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4137905133
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 192 | 4992 | 201 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 192 | 4992 | 201 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | INDEX_CUST_ID | 192 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_ID"=8888)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
158 consistent gets
0 physical reads
0 redo size
4016 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
192 rows processed
修改索引为不可见索引：
SQL> alter index INDEX_CUST_ID invisible;
Index altered.
SQL> select PROD_ID from test where CUST_ID=8888;
192 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1274 | 1206 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| TEST | 49 | 1274 | 1206 (1)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_ID"=8888)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
4519 consistent gets
4428 physical reads
0 redo size
4016 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
192 rows processed最后修改时间：2019-04-14 10:49:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
