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.

creating a partition table might make your queries run faster

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.

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.

Look at the number unique values for each column. Perhaps a MDC might be a good approach

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.