Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: DB2 Date index design question

    Hi all

    I am fairly new to DB2 and its performance tricks and need to design a fairly big datamart - around 100 million rows when mature. (growing at about 100k records a day - keeping a year's history)
    The main fact table will contain data on various processes, each identified by a process_id, type, business segment and time

    The issue surrounds time and how to best index it for peformance:

    My time data, i.e. when the process was active is delivererd as 2 fields: valid_from and valid_to.

    These dates can span multiple days but the smallest unit will always be a day. - no seconds/hours etc.

    So in order to filter our a process, i need to use a "Between" filter

    My question is:

    Cand DB2 properly index for such a range filter, or do I rather modify the recodrs in my ETL to create a single date column which can then be indexed as where date = 'DD/MM/YYYY'

    I.e. a record such as this:

    Poc_id, valid_from, valid_to
    1 , 01.01.2012, 03.01.2012


    Becomes:

    Poc_id, Valid_date
    1 , 01.01.2012
    1 , 02.01.2012
    1 , 03.01.2012

    Obviously the mentioned solution will massively expand the data volume, but will there be an better overall peformance now that I can index my date on a single column and my query will not need to use a range filter?

    Hope that made sense

    Any comments will be much appreicated

    Thanks

    j

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    Your solution will extremly expand the data.......
    I think you can choose one date column as a range partition key ,and create a regular index on an other date column.
    the one distributed more evenly should be chosn as parttion key column。

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    Thanks Fengsun: I see what you are getting to however considering the that we are running on days, partioning a table at day level could result in 365 partitions for a table - somehowe that does not seem like a good idea to me - Do you agree?

    Furthermore partioining will not get me away from using a range filter which is the real reason I posted this thread:
    Do I need to worry about range filters in DB2? or is it possible to index the date columns in such a way that peformance would be the same as an index on a single value column?




    Quote Originally Posted by fengsun2 View Post
    Your solution will extremly expand the data.......
    I think you can choose one date column as a range partition key ,and create a regular index on an other date column.
    the one distributed more evenly should be chosn as parttion key column。

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    1、why do you think 365 partitions for a table is not a good idea?
    2、I can not understand what is the "range filter" you metioned above? can you explain it more elaborately or provide some example queries here?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jluckhoff View Post
    Do I need to worry about range filters in DB2?
    I don't think so.

    Quote Originally Posted by jluckhoff View Post
    or is it possible to index the date columns in such a way that peformance would be the same as an index on a single value column?
    It's quite easy to model and test both scenarios and see for yourself.

Posting Permissions

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