Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: Is creating indexes on date cols fo MAX /MIN aggregate functions good?

    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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2007
    Posts
    72
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, I'd think so. But you should verify that the index is used by looking at the access plan.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •