Re: dates and indexes
From: Balakrishna Avula <100610.52_at_compuserve.com>
Date: 1996/04/22
Message-ID: <4lfj6g$gpk_at_relay1.mdis.com>#1/1
Date: 1996/04/22
Message-ID: <4lfj6g$gpk_at_relay1.mdis.com>#1/1
yeazel_at_ix.netcom.com (Steve Yeazel) wrote:
>If a date column has an index on it, does referencing that column with
>a to_char within the where clause cause the index not to be used?
>For instance,
>select * from tbl where to_char(datecol,'YYYY') = '1996';
>If datecol has an index on it, will it be used? I believe the answer
>is no, but want to confirm.
>Steve
As I know, any functions applied on the column will invalidate index usage. Reformat your query by applying functions on the constant and this will cause indexes to be used. Eg.
datecol between '01-JAN-96' and '31-DEC-96' will use an index.
Hope this helps.
Bala
-- Balakrishna Avula 28 Ashby Court Alpha-Tek Associates Ltd. Hemel Hempstead Technical Consultant(on contract to Oracle UK) HERTS HP2 7QL 100610,52 on Compuserve UKReceived on Mon Apr 22 1996 - 00:00:00 CEST