This discussion is archived
8 Replies Latest reply: Jul 29, 2013 4:29 AM by foxtrot2 RSS

Why is index not being used?

foxtrot2 Newbie
Currently Being Moderated

I need some help in trying to work out why an index isn't being used. I hope someone here can help.

 

Table definition for ACT_TOTALS includes

 

STARTTIME TIMESTAMP(6)

 

There is an index on to_char(starttime, 'YYYY-MM-DD HH24')

 

There is a vew definition ACT_TOTALS_HOURLY2 which includes

 

select to_char(starttime, 'YYYY-MM-DD HH24') STARTTIMSE

..

...

from ACT_TOTALS

group by to_char(starttime, 'YYYY-MM'DD HH24'), TRANSTYPE

 

I have run a query

 

select starttime from ACT_TOTALS_HOURLY2

where starttime <= to_char(sysdate, 'YYYY-MM-DD HH24');

 

The table access comes out as full despite there being > 2 million rows in the table.

 

The predicate looks like

TO_CHAR(INTERNAL_FUNCTION(''STARTTIME''), 'YYYY-MM-DD HH24')<= ..........

 

I thinkg the INTERNAL_FUNCTION is an implicit cast from DATE to TIMESTAMP.

Can anyone please throw any light on why the index on the table wouldn't be used in this case?

Thanks in advance for any help.

  • 1. Re: Why is index not being used?
    michaelrozar17 Pro
    Currently Being Moderated

    You have not used the indexed column in the WHERE clause in the View's definition. Maybe that is the reason. Try if the below helps..

     

    select to_char(starttime, 'YYYY-MM-DD HH24') STARTTIMSE

    ..

    ...

    from ACT_TOTALS

    where starttime <= ....

    group by to_char(starttime, 'YYYY-MM'DD HH24'), TRANSTYPE

  • 2. Re: Why is index not being used?
    nagarw31 Newbie
    Currently Being Moderated

    Because you used the operator <=..that caused this indexed not to be used..

     

    where starttime <= to_char(sysdate, 'YYYY-MM-DD HH24');

  • 3. Re: Why is index not being used?
    nagarw31 Newbie
    Currently Being Moderated

    I referred the earlier query posted for the same cause on this forum

     

    https://forums.oracle.com/thread/346698

     

    Thanks!!

  • 4. Re: Why is index not being used?
    Lalit Kumar B Explorer
    Currently Being Moderated

    One of the most common Oracle SQL tuning problems are full-table scans that are imposed by invalidating a WHERE clause predicate with an Oracle built-in Function (BIF). This problem is especially prevalent when constraining SQL queries for date ranges because of the intrinsic encryption of the relational database DATE datatype.


     

    Here are details on using function based indexes.


     

    For example, these WHERE clause predicates might invoke an unexpected full-table scan:


     

    WHERE trunc(ship_date) > trunc(sysdate-7);

     

    WHERE to_char(ship_date,’YYYY-MM-DD’) = ‘2004-01-04’;

    etc.

  • 5. Re: Why is index not being used?
    chris227 Guru
    Currently Being Moderated

    Since you search for dates lower than sysdate one can assume that nearly all rows fall into consideration. Since you are looking for the date but not for the indexed to_char there is no reason to use the index as we probably go for all rows.

    It is better not to rely on implizit type conversions, here from timestamp to varchar.

    I would be better to create the index on the plain timestamp column and work with date arithmetic in the predicates

    Just apply the to_char in the projection in the projection if neccesarry.

    For example

     

    where starttime <= sysdate

  • 6. Re: Why is index not being used?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Chris,

     

    Under in any circumstances one of the first questions to ask when someone says "why isn't Oracle using the index?" is "how much of the data has to be acquired?" - and your observations about the likely interpretation of the column name is a good one.

     

    From the description given - and allowing for a couple of typos - I can't see any reason why Oracle couldn't use the index, so it seems likely that the problem is purely one of statistics: either the predicate identifies a lot of the data, or Oracle thinks it identifies a lot of the data.

     

    For the OP:  does the internal_function() come from running "explain plan", or does it come after running the query and making a call to dbms_xplan.display_cursor() - sometimes the two mechanism don't show exactly the same output, and you may find that one of them shows you a predicate involving something like a SYS_NC00005$ column.

     

    You might not be aware of it, but you could also use trunc(date_col,'hh24') rather than the to_char() approach - it would give you a smaller index, and use less CPU.

     

    If you're example is a faithful copy of your actual code then adding the hint /*+ index(act_totals_hourly2.act_totals) */ may be enough to demonstrate that te indexed access path is legal.

     

     

     

    Regards

    Jonathan Lewis

    .

  • 7. Re: Why is index not being used?
    Mohamed Houri Pro
    Currently Being Moderated

    Can you supply a test case for that?


    This is what I have done to engeneer your case

     

    drop table t1;

    create table t1 (n1 number, d1 date, STARTTIME TIMESTAMP(6));

     

    insert into t1(n1,d1, starttime)  select rownum, trunc(sysdate) + rownum, sysdate

    from dual connect by level <= 2e6;

     

    create or replace view t1_v as select n1, d1, to_char(starttime, 'YYYY-MM-DD HH24') starttimse

    from t1;

     

    SQL> explain plan for

      2  select starttimse from t1_v

      3  where starttimse <= to_char(sysdate, 'YYYY-MM-DD HH24');

     

    Explained.

     

    SQL> select * from table(dbms_xplan.display);

     

    Plan hash value: 838529891

    --------------------------------------------------------------------------

    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------

    |   0 | SELECT STATEMENT  |      |   109K|  1394K|  2004  (14)| 00:00:12 |

    |*  1 |  TABLE ACCESS FULL| T1   |   109K|  1394K|  2004  (14)| 00:00:12 |

    --------------------------------------------------------------------------

     

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - filter(TO_CHAR(INTERNAL_FUNCTION("STARTTIME"),'YYYY-MM-DD

                  HH24')<=TO_CHAR(SYSDATE@!,'YYYY-MM-DD HH24'))

    Note

    -----

       - dynamic sampling used for this statement

     

    SQL> create index t1_ind on t1 (to_char(starttime, 'YYYY-MM-DD HH24'));

     

    Index created.

     

    SQL> explain plan for

      2  select starttimse from t1_v

      3  where starttimse <= to_char(sysdate, 'YYYY-MM-DD HH24');

     

    Explained.

     

    SQL> select * from table(dbms_xplan.display);

     

    Plan hash value: 3884685049

    ---------------------------------------------------------------------------

    | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

    ---------------------------------------------------------------------------

    |   0 | SELECT STATEMENT |        |   109K|  1394K|    68   (2)| 00:00:01 |

    |*  1 |  INDEX RANGE SCAN| T1_IND |   109K|  1394K|    68   (2)| 00:00:01 |

    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - access(TO_CHAR(INTERNAL_FUNCTION("STARTTIME"),'YYYY-MM-DD

                  HH24')<=TO_CHAR(SYSDATE@!,'YYYY-MM-DD HH24'))

     

    Note

    -----

       - dynamic sampling used for this statement

     

    The above selects are done against the view t1_v.

     

    While the following one is done against the table t1

     

    SQL> set autotrace traceonly explain

    SQL> select starttime from t1

      2  where starttime <= to_char(sysdate, 'YYYY-MM-DD HH24');

     

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 838529891

    --------------------------------------------------------------------------

    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

    --------------------------------------------------------------------------

    |   0 | SELECT STATEMENT  |      | 29875 |   379K|  1790   (4)| 00:00:11 |

    |*  1 |  TABLE ACCESS FULL| T1   | 29875 |   379K|  1790   (4)| 00:00:11 |

    --------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - filter("STARTTIME"<=TO_TIMESTAMP(TO_CHAR(SYSDATE@!,'YYYY-MM-DD

                  HH24')))

     


    If you can post a clear test case of your issue may be one of us can help you


    Best regards

    Mohamed Houri

    www.hourim.wordpress.com

  • 8. Re: Why is index not being used?
    foxtrot2 Newbie
    Currently Being Moderated

    Thanks for all the help.

    I tried the "between" suggested and it seemed to work.

     

    Old query

    select extract(hour from a.starttime) as "tod", sum(a.active_n) as "In Progress"

    from act_totals_hourly a

    WHERE to_char(a.starttime, 'YYYY-MM-DD') = to_char(current_date, 'YYYY-MM-DD')

    and extract(hour from a.starttime) <= extract(hour from current_timestamp)

    group by extract(hour from a.starttime)

    order by extract(hour from a.starttime);


    I changed the view and the query. It used the index and is approx 400 times faster.

    select substr(a.starttime,12,2) as "tod", sum(a.active_n) as "In Progress"

    from act_totals_hourly2 a

    WHERE a.starttime between concat(to_char(sysdate, 'YYYY-MM-DD'), ' 00') and to_char(sysdate, 'YYYY-MM-DD HH24')

    group by substr(a.starttime,12,2)

    order by substr(a.starttime,12,2);


    There are some subtle differences that I need to work out

    a) I think one is using BST (sysdate?) and another GMT (current_timestamp?). There is a difference somewhere.

    b) getting one digit for hour rather than two.


    But these are minor issues compared with getting the idex used.


    Thanks again

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points