Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Red face Unanswered: 10 million data storage in db2

    I need to build a history table in DB2 (Version-9.1.5) with 8 columns which will queried frequently with all the 8 columns in its where clause. I dont have a primary key or coposite key to query this table. This table is expected to have around 10 million records in it. whats the best way to query it or shall i move this big table to data warehouse ? Pls advice.

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    creating a partition table might make your queries run faster
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    Aug 2010
    Posts
    2
    Thanks for your reply. But I think this might not work because...I have 8 columns and all are text data. The query will be built dynamically for eg: if the user wants to search with only one column in where condition we will query that column alone in this table which has 10 million records..similaryl if 8 columns are needed we will search with 8 columns in where condition.

    Now your idea is to break the tables in to 2 or 3 means...data will be replicated and i wont have a primary key to join the tables..Pls advice...Else shall i give it to datawarehouse and will it be easy for us to access data from data warehouse.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    krishnabemtech, If you have no other information, you should probably create 8 indexes (1 on each column). This would cover any query on a Single column and DB2 can get a RID list from multiple indexes and AND/OR them to access the appropriate rows when multiple columns are use in the Where clause.

    Another approach is to ask the people who will be querying the table how they do it. Is there a column or set of columns they 'always' use. If you can determine a tendency, you could create a multi-key index (or more than one) to handle the majority of the request and individual indexes on other columns.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Look at the number unique values for each column. Perhaps a MDC might be a good approach

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by krishnabemtech View Post
    Now your idea is to break the tables in to 2 or 3 means...data will be replicated and i wont have a primary key to join the tables..Pls advice...Else shall i give it to datawarehouse and will it be easy for us to access data from data warehouse.
    Table partitioning does not use primary keys. You just need some meaningful column to partition your table. Most of the time it is month, quoter, or year column. Could be geo location too. Something to make your query run within limited number of partitions instead of scanning the whole table.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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