9 Replies Latest reply: Feb 6, 2013 2:52 AM by jeneesh RSS

    Index on date field

    Manjusha Muraleedas
      Please see my table
      create table DATE_TEST
      (
        ID         VARCHAR2(6) not null,
        ID_DESC    VARCHAR2(250),
        START_DATE DATE,
        END_DATE   DATE
      );
      -- Create/Recreate primary, unique and foreign key constraints 
      alter table DATE_TEST
        add constraint DATE_TEST_PK primary key (ID);
      -- Create/Recreate indexes 
      create index DATE_TEST_IDX1 on DATE_TEST (END_DATE));
      create index DATE_TEST_IDX2 on DATE_TEST (START_DATE);
      No I added some data into table.My problem is it is not taking index for between query.

       EXPLAIN PLAN FOR SELECT * FROM       DATE_TEST WHERE           start_date between to_date('01/01/2012','DD/MM/YYYY') AND to_date('31/12/2012','DD/MM/YYYY')           OR  end_date between to_date('01/01/2012','DD/MM/YYYY') AND to_date('31/12/2012','DD/MM/YYYY')
        2  /
      
      Explained
      
      SQL>      SELECT * FROM  TABLE( DBMS_XPLAN.display);
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      Plan hash value: 4189439861
      -------------------------------------------------------------------------------
      | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |           |   954 |   139K|     7   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| DATE_TEST |   954 |   139K|     7   (0)| 00:00:01 |
      -------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         1 - filter("START_DATE">=TO_DATE(' 2012-01-01 00:00:00',
                    'syyyy-mm-dd hh24:mi:ss') AND "START_DATE"<=TO_DATE(' 2012-12-31
                    00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "END_DATE">=TO_DATE('
                    2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                    "END_DATE"<=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:s
      Note
      -----
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
         - dynamic sampling used for this statement (level=2)
      
      21 rows selected
      Why it is not taking index and what are the other solutions for this