Query on indexed date field doesn't use index

From: Larry Lopez <lopezl_at_dnt.dialog.com>
Date: 1995/10/27
Message-ID: <46r2sm$5fa_at_iserv.dnt.dialog.com>#1/1


        I've has wierd behavior with an index on a date field in
        a table.  The table has about 10,000,000 rows with an
        acct_date(type DATE) field.  There is a non-unique index on that
        field.  The problem:
        in SQL*Plus the query:
        select * from acct_records where acct_date > SYSDATE-1;
        takes foreeeveeeer to come back.  It appears that Oracle is not
        using the index, BUT, the query:
        select * from acct_records where acct_date >
                TO_DATE('10/10/1995 00:00:00', 'mm/dd/yyyy hh24:mi:ss');
        returns with data instantly.

        Any ideas why the first query doesn't use the index?

        Larry
Received on Fri Oct 27 1995 - 00:00:00 CET

Original text of this message