SQL 不走索引

Question

执行的SQL:
SELECT id,PROGRAM_ID,PROPER_TITLE,DURATION,LAYER_ID,PROGRAM_CLASS,CREATE_TIME,PROGRAM_CREATOR,RIGHT_LEVEL,FIELD_1 FROM 
       (SELECT TEMP_TAB.ID,TEMP_TAB.PROGRAM_ID,TEMP_TAB.PROPER_TITLE,TEMP_TAB.DURATION,TEMP_TAB.LAYER_ID,TEMP_TAB.PROGRAM_CLASS,
                TEMP_TAB.CREATE_TIME,TEMP_TAB.PROGRAM_CREATOR,TEMP_TAB.RIGHT_LEVEL,TEMP_TAB.FIELD_1,RowNum as RN FROM                 
                ( SELECT MCM_NAVIGATION.ID,MCM_NAVIGATION.PROGRAM_ID,MCM_NAVIGATION.PROPER_TITLE,MCM_NAVIGATION.DURATION,MCM_NAVIGATION.LAYER_ID,MCM_NAVIGATION.PROGRAM_CLASS,
                MCM_NAVIGATION.CREATE_TIME,MCM_NAVIGATION.PROGRAM_CREATOR,MCM_NAVIGATION.RIGHT_LEVEL,MCM_NAVIGATION.FIELD_1 
                FROM MCM_NAVIGATION                 
                --INNER JOIN MCM_FULLTEXT ON MCM_NAVIGATION.ID = MCM_FULLTEXT.ID 
                --WHERE ((MCM_FULLTEXT.FULL_TEXT LIKE '%新闻%') 
                --AND MCM_FULLTEXT.STATE = 100 
                --AND MCM_NAVIGATION.PROGRAM_ID NOT IN (SELECT ID FROM MCM_NAVIGATION WHERE RIGHT_LEVEL>2))         
                where EXISTS 
        (select id from mcm_fulltext where mcm_fulltext.id = mcm_navigation.id and MCM_FULLTEXT.FULL_TEXT LIKE '%DÂÎÅ%' and mcm_fulltext.state = 100)
                order by MCM_NAVIGATION.PROGRAM_ID,MCM_NAVIGATION.LAYER_ID,MCM_NAVIGATION.PROPER_TITLE  ) TEMP_TAB)  
WHERE RN <= 20
执行计划:Elapsed: 00:00:25.37Execution Plan
----------------------------------------------------------
Plan hash value: 236871974---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                | 38635 |    53M|       | 42695   (1)| 00:08:33 |
|*  1 |  VIEW                    |                | 38635 |    53M|       | 42695   (1)| 00:08:33 |
|   2 |   COUNT                  |                |       |       |       |            |          |
|   3 |    VIEW                  |                | 38635 |    52M|       | 42695   (1)| 00:08:33 |
|   4 |     SORT ORDER BY        |                | 38635 |    32M|    33M| 42695   (1)| 00:08:33 |
|*  5 |      HASH JOIN RIGHT SEMI|                | 38635 |    32M|    28M| 35462   (1)| 00:07:06 |
|*  6 |       TABLE ACCESS FULL  | MCM_FULLTEXT   | 38635 |    27M|       | 23608   (1)| 00:04:44 |
|   7 |       TABLE ACCESS FULL  | MCM_NAVIGATION |   802K|   107M|       |  4686   (1)| 00:00:57 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("RN"<=20)
   5 - access("MCM_FULLTEXT"."ID"="MCM_NAVIGATION"."ID")
   6 - filter("MCM_FULLTEXT"."FULL_TEXT" LIKE '%????????%' AND "MCM_FULLTEXT"."STATE"=100)
Statistics
----------------------------------------------------------
        271  recursive calls
          0  db block gets
     110631  consistent gets
     115269  physical reads
          0  redo size
        983  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         39  sorts (memory)
          0  sorts (disk)
          0  rows processed两张表中的索引情况：
SQL> r
  1  select user_ind_columns.index_name,user_ind_columns.column_name,
  2       user_ind_columns.column_position,user_indexes.uniqueness
  3       from user_ind_columns,user_indexes
  4       where user_ind_columns.index_name = user_indexes.index_name
  5*      and user_ind_columns.table_name = 'MCM_FULLTEXT'INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION UNIQUENES
------------------------------ --------------- --------------- ---------
BIT_LAYER_ID                   LAYER_ID                      1 NONUNIQUE
BIT_STATE_ID                   STATE                         1 NONUNIQUE
BIT_UPDATE_TIME_ID             UPDATE_TIME                   1 NONUNIQUE
BIT_INDEX_STATE_ID             INDEX_STATE                   1 NONUNIQUE
P_MCM_FULLTEXT                 ID                            1 UNIQUEElapsed: 00:00:00.01
SQL> select user_ind_columns.index_name,user_ind_columns.column_name,
  2       user_ind_columns.column_position,user_indexes.uniqueness
  3       from user_ind_columns,user_indexes
  4       where user_ind_columns.index_name = user_indexes.index_name
  5       and user_ind_columns.table_name = 'MCM_NAVIGATION';
INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION UNIQUENES
------------------------------ --------------- --------------- ---------
P_MCM_NAVIGATION               ID                            1 UNIQUE
BIT_STATE                      STATE                         1 NONUNIQUE
BIT_PARENT_ID                  PARENT_ID                     1 NONUNIQUE
BIT_RIGHT_LEVEL                RIGHT_LEVEL                   1 NONUNIQUEElapsed: 00:00:00.10
     我想知道为什么这条sql语句没有走索引，需要如何优化，优化思路是什么？

Answer 1

可以试试分析一下表。sqlplus环境下执行
exec dbms_stats.gather_table_stats(user,'MCM_FULLTEXT',cascade=>true);
exec dbms_stats.gather_table_stats(user,'MCM_NAVIGATION',cascade=>true);

Answer 2

接 1#，还有一些原因可以引起不走索引：
第一：索引列中存在null，致使oracle认为会产生计算错误的情况下不走索引；
第二：索引列和要比较的数据的数据类型不一致。比如
where mcm_fulltext.id = mcm_navigation.id and MCM_FULLTEXT.FULL_TEXT LIKE '%DÂÎÅ%' and mcm_fulltext.state = 100
语句中 mcm_fulltext.state 列的类型是否是 int 类型？

Answer 3

1、索引列没有空值
2、索引比较的数据类型是一致的SQL> desc mcm_fulltext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 LAYER_ID                                  NOT NULL NUMBER(38)
 FULL_TEXT                                          CLOB
 SERIES_TEXT                                        NVARCHAR2(1000)
 SERIES_ID                                          NUMBER(38)
 STATE                                     NOT NULL NUMBER(38)
 UPDATE_TIME                               NOT NULL TIMESTAMP(6)
 INDEX_STATE                               NOT NULL NUMBER(38)
 COLUMN_TEXT                                        NVARCHAR2(1000)
 ROLE_TEXT                                          NVARCHAR2(1000)
 PROGRAM_TYPE                                       NVARCHAR2(300)SQL> desc mcm_navigation
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 PARENT_ID                                 NOT NULL NUMBER(38)
 LAYER_ID                                  NOT NULL NUMBER(38)
 PROPER_TITLE                              NOT NULL NVARCHAR2(1000)
 DURATION                                           NVARCHAR2(12)
 START_POINT                                        NVARCHAR2(12)
 PROGRAM_TYPE                                       NUMBER(38)
 PROGRAM_CLASS                                      NVARCHAR2(50)
 VIEW_TIMES                                         NUMBER(38)
 PROGRAM_CREATOR                                    NVARCHAR2(100)
 CREATE_USERID                                      NUMBER(38)
 CREATE_TIME                               NOT NULL TIMESTAMP(6)
 UPDATE_TIME                                        TIMESTAMP(6)
 STATE                                     NOT NULL NUMBER(38)
 RIGHT_LEVEL                                        NUMBER(38)
 PROGRAM_ID                                         NUMBER(38)