有一张表,其表结构是create table BL_LOG
(
  BL_BASIC_UUID   NUMBER(20) not null,
  BL_HISTORY_UUID NUMBER(20) not null,
  BL_REF_CDE      VARCHAR2(12) not null,
  SEQ_NUM         NUMBER(4) not null,
  CRE_DT_GMT      DATE,
  CRE_DT_LOC      DATE,
  CRE_TIME_ZONE   VARCHAR2(3),
  CREATOR         VARCHAR2(8),
  CRE_OFCE        VARCHAR2(3),
  TYPE            VARCHAR2(20),
  REMARKS         VARCHAR2(240),
  VERSION         NUMBER(3),
  CHNGE_FM        VARCHAR2(25),
  CHNGE_TO        VARCHAR2(25),
  BL_STATE        VARCHAR2(25),
  IMAGE_STATE     VARCHAR2(15),
  IMAGE_TYPE      VARCHAR2(40),
  PRT_TYPE        VARCHAR2(10),
  NUM_OF_COPIES   NUMBER(9),
  COMPONENT       VARCHAR2(12),
  REC_UPD_DT      DATE
)
REC_UPD_DT字段上已经建了index.   create index BL_LOGI01 on BL_LOG (REC_UPD_DT)这张表记录数有2亿多条。现在有一个sql查询,
SELECT   *   FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')这个sql需要跑将近6分钟才能出结果。我用语句alter index BL_LOGI01 monitoring usage;  来开启对索引的监控。然后我执行那句sql,然后我查询 select * from v$object_usage,发现 索引 BL_LOGI01 并没有被使用。
然后我在sql上指定索引,
SELECT /*+ INDEX(BL_LOG BL_LOGI01)*/  *   FROM BL_LOG A
WHERE A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1
AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy')
再次执行这个sql,发现这个索引还是没有被使用。请教各位高手,那句sql该如何写才能让索引被使用? 或者我需要怎么建索引,才能提高查询速度啊?

解决方案 »

  1.   

    where条件有函数,索引就不能用,你把to_date去掉试一下应该没问题了的
      

  2.   


    那请问 我把to_date去掉的话, 我那个sql该怎么写? 你能不能帮我改好?
      

  3.   


    我在那个列上建了个to_char的函数索引,然后跑下面的sql速度很快。
    SELECT * FROM BL_LOG A
    WHERE to_char(REC_UPD_DT, 'yyyy-mm-dd') <= '2009-02-02' 
    AND to_char(REC_UPD_DT, 'yyyy-mm-dd') >= '2009-01-01' 但是为什么第一个sql那么慢呢? 
      

  4.   

    呵呵 你建的是to_char的函数索引
    在where条件中使用函数to_char当然跑的速度快了
    用to_date就不行了
      

  5.   


    你那个to_char的函数索引是怎样创建的?能把代码贴出来看看吗?
      

  6.   


    拜托看仔细点,我跑第一个sql时,to_char的函数索引没有建。
    后来建了to_char的函数索引后,sql也做了修改,改成第二个样子。
      

  7.   

    create index BL_LOGI3 on BL_LOG (to_char(rec_upd_dt , 'yyyy-mm-dd'));
      

  8.   


    你在4楼说你建了TO_CHAR的索引
    而你的语句中确实是用了TO_CHAR函数
    这样速度就快了可是你是在问第一句SQL 也就是那个是用TO_DATE函数的SQL  没错吧?
    速度为什么慢
    因为你没有建立TO_DATE函数索引呀
    TO_CHAR函数索引在使用TO_DATE函数的时候是不会起作用的
      

  9.   

    这么多数据,怎么能这样查,Oracle是按一定量查出后,返回的结果,再继续查。
      

  10.   

    这两种方法的执行计划,有一点区别,不知道是不是问题所在。
    [TEST@ora10gr1#2009-11-22/21:07:23] SQL>create table test(rq date);Table created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>create index test_idx01 on test(rq);Index created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>create index test_idx02 on test(to_char(rq,'yyyy-mm-dd'));Index created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>
    [TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>insert into test values(to_date('2009-09-01','yyyy-mm-dd'));1 row created.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>
    [TEST@ora10gr1#2009-11-22/21:07:23] SQL>explain plan for
      2   select * from test where rq < to_date('02012009', 'mmddyyyy') AND rq >= to_date('01012009', 'mmddyyyy');Explained.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------
    Plan hash value: 3746981722-------------------------------------------------------------------------------
    | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |            |     1 |     9 |     1   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| TEST_IDX01 |     1 |     9 |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------Predicate Information (identified by operation id):
    ---------------------------------------------------PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------   1 - access("RQ">=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd
                  hh24:mi:ss') AND "RQ"<TO_DATE('2009-02-01 00:00:00', 'yyyy-mm-dd
                  hh24:mi:ss'))Note
    -----
       - dynamic sampling used for this statement19 rows selected.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>
    [TEST@ora10gr1#2009-11-22/21:07:23] SQL>explain plan for
      2   select * from test where to_char(rq,'yyyy-mm-dd') <= '2009-02-02' AND to_char(rq,'yyyy-mm-dd') >= '2009-01-01';Explained.[TEST@ora10gr1#2009-11-22/21:07:23] SQL>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------
    Plan hash value: 418310500------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |            |     1 |     9 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     1 |     9 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | TEST_IDX02 |     1 |       |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------   2 - access(TO_CHAR("RQ",'yyyy-mm-dd')>='2009-01-01' AND
                  TO_CHAR("RQ",'yyyy-mm-dd')<='2009-02-02')Note
    -----
       - dynamic sampling used for this statement19 rows selected.
      

  11.   


    请看清楚楼主的sql,列上面没有用函数,根本不是这个原因!!A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1 
    AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy') 这个范围内的数据量多大?oracle的cbo会根据数据量走不同的执行计划
      

  12.   

    我觉得有几个朋友对函数索引有错误的认识。在第一个sql中,where 语句是 
    A.REC_UPD_DT <= to_date('02012009', 'mmddyyyy') + 1 
    AND A.REC_UPD_DT >= to_date('01012009', 'mmddyyyy') 
    REC_UPD_DT字段上没有用函数,所以这个sql不需要对REC_UPD_DT字段建函数所引,只需要建一个普通索引就行了,建的语句类似 create index BL_LOGI01 on BL_LOG (REC_UPD_DT) 而在第二个sql中,where语句是
    WHERE to_char(REC_UPD_DT, 'yyyy-mm-dd') <= '2009-02-02' 
    AND to_char(REC_UPD_DT, 'yyyy-mm-dd') >= '2009-01-01' 
    REC_UPD_DT字段上有函数to_char,所以必须要建一个函数索引,create index BL_LOGI3 on BL_LOG (to_char(rec_upd_dt , 'yyyy-mm-dd'));是不是要用函数索引,区别点在where的等号左边的列字段上是否用了函数,而不是看等号右边的写法。