oracle不使用索引的原因有哪些？ - 数据库编程

-------- Plan hash value: 257372123 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 494 | 6 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL | | 13 | 494 | 6 (0)| 00:00:01 | 1 | 4 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP | 13 | 494 | 6 (0)| 00:00:01 | 1 | 4 | |* 3 | INDEX FULL SCAN | EMP_IDX1 | 13 | | 3 (0)| 00:00:01 | 1 | 4 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("EMPNO"<>7900) SQL>

可知，当使用<>就会跳过索引，但是我们可以使用hints（提示）让数据库强制使用索引，注意not in或是in 关键字类似<>依然会跳过索引，那么替代办法要么更改sql查询语句，要么使用case when条件，那么也需要在建立函数索引了。

第四种：统计信息过旧

在基于成本的optimizer更具数据进行估计cast，当数据已经被修改（如进行了大量的dml操作），那么统计信息肯定过旧，那么oracle在更加统计信息进行执行计划选择的时候往往可能出现选择错误的执行计划。

在10g开始oracle会有自动收集统计信息的任务在运行。

17:15:22 sys@REPDB>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

17:15:28 sys@REPDB>select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_jobs;

JOB_NAME                       LAST_START_DATE                                                             COMMENTS
------------------------------ --------------------------------------------------------------------------- ------------------------------------------------------------
PURGE_LOG                      07-2   -14 03.00.01.146182 é    +08:00                                      purge log job
FGR$AUTOPURGE_JOB                                                                                          file group auto-purge job
GATHER_STATS_JOB                                                                                           Oracle defined automatic optimizer statistics collection job
AUTO_SPACE_ADVISOR_JOB                                                                                     auto space advisor maintenance job
MGMT_CONFIG_JOB                06-2   -14 10.00.02.198895      +08:00                                      Configuration collection job.
MGMT_STATS_CONFIG_JOB          01-2   -14 01.01.01.762793 é    +08:00                                      OCM Statistics collection job.
RLM$EVTCLEANUP                 07-2   -14 04.18.33.923785      +08:00
RLM$SCHDNEGACTION              07-2   -14 05.02.43.177946      +08:00

ò     8DD ￡

但在11g该内容开始取消；

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select JOB_NAME,LAST_START_DATE,comments from dba_scheduler_jobs;

JOB_NAME                       LAST_START_DATE                                    COMMENTS
------------------------------ -------------------------------------------------- --------------------------------------------------
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE      07-FEB-14 12.00.00.522780 AM PST8PDT               auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT   07-FEB-14 12.00.00.242715 AM PST8PDT               auto clean job for recoverable script
FGR$AUTOPURGE_JOB                                                                 file group auto-purge job
BSLN_MAINTAIN_STATS_JOB        06-FE

oracle不使用索引的原因有哪些？(二)