今天遇到问题,同事问我,order by 主键列 desc 与order by 日期列(加了索引) desc ,怎么执行时间差距这么大;于是查看执行计划不一样:
order by 主键列 desc 对应 INDEX FULL SCAN DESCENDING
order by 日期列(加了索引) desc 对应 TABLE ACCESS FUL
我加hint /*+ index(table index_name) */ 强制使用索引,计划还是使用的全表扫描,很奇怪为什么没有使用索引呢?我加了hint就不应该是数据问题了吧。
最后明白了原因:主键 not null而日期列可为null,如果将日期列改为not null,执行计划会一致。
以上将sid字段重新改为null,在sql索引字段上添加 is not null WHERE条件,计划选择INDEX FULL SCAN DESCENDING。
总结:索引列为null在有些情况下会影响oracle选择使用该索引,而选择使用全表扫描。在很多情况下应该注意null。
order by 主键列 desc 对应 INDEX FULL SCAN DESCENDING
order by 日期列(加了索引) desc 对应 TABLE ACCESS FUL
我加hint /*+ index(table index_name) */ 强制使用索引,计划还是使用的全表扫描,很奇怪为什么没有使用索引呢?我加了hint就不应该是数据问题了吧。
最后明白了原因:主键 not null而日期列可为null,如果将日期列改为not null,执行计划会一致。
总结:Oracle可能因为索引字段列可以为Null而放弃使用索引。以下为INDEX FULL SCAN
SQL> create table null_valid(id int primary key,sid int null,sdate date);
Table created.
SQL> alter table null_valid drop column sdate;
Table altered.
SQL> desc dba_objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> insert into null_valid select object_id,object_id from dba_objects where rownum < 100;
99 rows created.
SQL> create index idx_sid on null_valid(sid);
Index created.
SQL> set autot trace explain
SQL> select * from null_valid order by id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1230549944
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 99 | 2574 | 4 (25)| 00:00:01
|
| 1 | SORT ORDER BY | | 99 | 2574 | 4 (25)| 00:00:01
|
| 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 2574 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Note
-----
- dynamic sampling used for this statement
SQL> exec dbms_stats.gather_table_stats(user,'NULL_VALID');
PL/SQL procedure successfully completed.
SQL> select * from null_valid order by id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 600338710
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 99 | 594 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| NULL_VALID | 99 | 594 | 2 (0)|
00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| SYS_C005257 | 99 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
SQL> select * from null_valid order by sid desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1230549944
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 99 | 594 | 4 (25)| 00:00:01
|
| 1 | SORT ORDER BY | | 99 | 594 | 4 (25)| 00:00:01
|
| 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 594 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
SQL> select /*+ index(null_valid idx_sid) */ * from null_valid order by sid desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1230549944
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 99 | 594 | 4 (25)| 00:00:01
|
| 1 | SORT ORDER BY | | 99 | 594 | 4 (25)| 00:00:01
|
| 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 594 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
以上sid字段(可以为null)加了索引,使用hint强制使用索引,计划依然使用全表扫描。
SQL> alter table null_valid modify sid not null;
Table altered.
SQL> select * from null_valid order by sid desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2266710330
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 99 | 594 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| NULL_VALID | 99 | 594 | 2 (0)|
00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| IDX_SID | 99 | | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
以上将sid列改为not null,计划选择使用了INDEX FULL SCAN DESCENDING。
SQL> set linesize 120
SQL> alter table null_valid modify sid null;
Table altered.
SQL> select * from null_valid order by sid desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1230549944
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 594 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 99 | 594 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 594 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
SQL> select * from null_valid where sid is not null order by sid desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2266710330
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 594 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| NULL_VALID | 99 | 594 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN DESCENDING| IDX_SID | 99 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SID" IS NOT NULL)
以上将sid字段重新改为null,在sql索引字段上添加 is not null WHERE条件,计划选择INDEX FULL SCAN DESCENDING。
总结:索引列为null在有些情况下会影响oracle选择使用该索引,而选择使用全表扫描。在很多情况下应该注意null。