USING DATES AS INDEXES

[VergeResources]

We have a rather large table that contains ~10 million records (it's a general ledger). Most of the queries users want to perform are for certain date ranges. Currently, we don't have any indexes on the date fields - my DBA says that they won't work effectively(OS/390 DB2 V6). Will version 7 handle dates any better? I implemented the exact same database for another client using Oracle and the date indexes made a huge difference.

 

[PruSQLer]

We have lots of very large ( over 100 million rows) tables that use dates as part of the index. Currently we're using Version 7 in AIX but we were using Version 6 prior. Some of the runs we process wouldn't finish if not for the indexes on date. I don't have extensive experience with this but I've never noticed any drag due to using a date as an index. Is the DBA concerned about inserts to the table perhaps?

 

[VergeResources]

He just didn't think it would make a difference since the dates are not unique and we are doing dynamic SQL. The updating is all done by nightly batch, so he is not concernend with that.

Thanks for the feedback!

 

[MarcLodge]

Hi all,
Your DBA might be thinking that you wish to put an index on a table which contains just a date, in which case what you are describing would be true (lots of non unique indexes etc.). When used with another field, they are, as PruSQLer points out, a very useful tool.
Marc