Re: Index on Year of date field

From: <seci_at_ludens.elte.hu>
Date: 1998/03/04
Message-ID: <1998Mar4.085536.48024@ludens>#1/1

hello,

In article <34FCE9D9.CFE4A526_at_KirchGruppe.de>, Martin Haltmayer <Martin_Haltmayer_at_KirchGruppe.de> writes:

> Because of the storage of dates do
> 
> select col1,to_char(date_col,'YYYY') year
> from mytable
> where date_col between
>     to_date ('01.01.' || '1997' || '00:00:00', 'DD.MM.YYYY HH24:MI:SS')
> and to_date ('31.12.' || '1997' || '23:59:59', 'DD.MM.YYYY HH24:MI:SS');
> 
> Martin Haltmayer
> 
> Stephan Schaefer wrote:

>>
>> I have a table with an index on the date field.
>> And I have a view created by:
>> create view myview
>> as select col1,to_char(date_col,'YYYY') year
>> from mytable ;
>>
>> The explain plan of the statement
>> select *
>> from myview
>> where year = '1997';
>> shows me a full table scan.
>>
>> Can I create an index on the date field so that selecting
>> by year that index is used?

the optimizer is using the index on date fields (really on every field) if there is a chance to reduce the number of reads. if you have a date field and the selection criteria is using only the year, the reduction is likely to nothing if you use the index [you have approx 10-20 diff. values] so a full table scan may be more effective [ using the index means that the index pages are read as well as data pages]

>>
>> Stephan Schaefer
>> stesch_at_camline.com

HTH
        peter Received on Wed Mar 04 1998 - 00:00:00 CST