OnDemand User Group

Support Forums => Report Indexing => Topic started by: Bill Dennis on July 24, 2008, 02:13:19 PM

Title: Why not have DATE as an Index field?
Post by: Bill Dennis on July 24, 2008, 02:13:19 PM

The Client online help for creating an AppGrp says that date fields should almost always be filters, not indexes.  Is this reasoning something only a DBA would understand?

Maybe because the segment table points to tables by date range already?

We use Single Table for All Loads. Is a date index OK here?

Any  comments welcome.

Thanks,
Bill

Title: Re: Why not have DATE as an Index field?
Post by: Justin Derrick on July 26, 2008, 06:46:04 AM

Hi Bill.

Indexes are best used for fields that are 'mostly unique' -- like a customer account number.

The great thing about the most recent versions of CMOD is that you can turn indexing on or off at any time.  So, while I would normally recommend indexing date fields, you can try running queries with it on or off and compare response times.  Just be forewarned that if your App Group has a lot of documents, building the index is non-trivial, and can take a considerable amount of time depending on your CPU speed and IO bandwidth.

Hope this helps!

-JD.

Title: Re: Why not have DATE as an Index field?
Post by: leodejong on September 15, 2008, 02:27:51 AM

Bill,

I assume you are using CMOD on z/Os. In that environment i would definitely put an index on the Date column in combination with "Single table for all loads". Software segmentation is typically a unix solution for partitioning. On z/OS i would suggest using DB2 native partitioning if the table gets too large.  Leo