Date column and table scan

[news.swissonline.ch]

Hi,

DB2 7 FP 13
Windows XP

I have a table in which on of the columns has the data type DATE. The date
column is indexed (asc non-unique index). Every SQL I create which tries to
filter by this date column produces a table scan, even a simple

select DATECOL from MYTABLE where DATECOL=date('2 004-09-16')

Does searching by dates in general produce a table scan?
Does it make sense to index a date column?
How should one best index a column of type date, if at all necessary?

Note: runstats were performed on the table. There are only 30000 rows in the
table of which 10 match the where clause.

Regards

Rudolf Bargholz

[ak_tiredofspam]

have you run reorgchk against MYTABLE recently?
did you run RUNSTATS ON TABLE xxxx WITH DISTRIBUTION AND DETAILED
INDEXES ALL?

[ak_tiredofspam]

have you run reorgchk against MYTABLE recently?
did you run RUNSTATS ON TABLE xxxx WITH DISTRIBUTION AND DETAILED
INDEXES ALL?

[Rudolf Bargholz]

Hi Ak,

<ak************ @yahoo.com> schrieb im Newsbeitrag
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

have you run reorgchk against MYTABLE recently?
did you run RUNSTATS ON TABLE xxxx WITH DISTRIBUTION AND DETAILED
INDEXES ALL?

Yes, I ran both, first the reorg, then the runstats.

What I have found in the mean time is, if I allow reverse scans on the date
columns, only then is an index scan and not a table scan used. Have changed
our database table/index synchronizer appropriately.

Regards

Rudolf Bargholz

[ak_tiredofspam]

Rudolf,
it is very interesting. Which optimization level are you using?
What if you create a table ONE_DATE(ONE_VA LUE DATE), enter the value
and try
select DATECOL from MYTABLE
JOIN ONE_DATE ON MYTABLE.DATECOL = ONE_DATE.ONE_VA LUE

[ak_tiredofspam]

Rudolf,
it is very interesting. Which optimization level are you using?
What if you create a table ONE_DATE(ONE_VA LUE DATE), enter the value
and try
select DATECOL from MYTABLE
JOIN ONE_DATE ON MYTABLE.DATECOL = ONE_DATE.ONE_VA LUE