Hi all,

I need to create one or two indexes on date columns to support MAX and MIN aggregate functions on a very large table and to make the SQL perform/run faster. I remember a group discussion sometime ago that creating an index on the date column ASCENDING or DESCENDING will be the thing to do. Is that still true?

Does anyone have or know of a good discussion of the pros and cons of doing what I am trying to accomplish?

Any information or examples will be very much appreciated.

Okonita

due to enhancements in the usage of an index,
MAX() and MIN() can now take advantage of an index regardless of the ASC or DESC order.

to find the most effective index, the whole query must be considered. Creating an index only on the column used with MIN or MAX might not increase performance

Hi,
it is suggested to create index with 'allow reverse scan' option.
Sample:
create index index_name on table_name (column1, column2, ... columnN) allow reverse scan

It is important as umayer stated that the whole SQL should be considered. Can you post SQL statement. Please also post user db2 edition and version, fixpack level and operating system you are using.
Hope this helps,
Grofaty

DB2 z/OS doesn't have the ALLOW REVERSE SCAN option. But internally, indexes will be created with forward and backward pointers at the leaf level already. (The backward pointers are what ALLOW REVERSE SCAN adds on DB2 LUW.)

Thanks Knut. Just so I understand, are you saying adding date_column_only index for SQL constructs using MAX(date_column) is a good idea, all things considered?

Okonita

Yes, I'd think so. But you should verify that the index is used by looking at the access plan.