Joined: 22 Dec 2007 Posts: 104 Location: Bangalore
I need one small suggestion. It is related to designing a new DB2 table. In current assignment we need to create 2 tables. Now sometime back someone had told me that while designing a table we need to keep all frequently used columns in the beginning of the table and it is good for query performance.
Now in our current table we have one column (it's kind of IND_STATUS column) we are assuming that we gonna use frequently in our WHERE clause (that column will be updated also couple of times with certain values). Now in the original design they kept the column towards end of the table and so I proposed to move that column towards the beginning (just after our primary key column). I got below reply...
"I do not believe that physical column order makes any difference to a table, except for a primary key consisting of multiple fields."
So I just wanted to know if my understanding is wrong. Please share your valuable thought so I can either go with them or I should reply with correct info.
We always add new columns at the end of the existing Table and as long as it has a index on it (IND_STATUS), you are good to go. Once you are done with the creation just run your sql query in BMC or Run a EXplain to confirm the required index is used and no table scan is performed.
What you heared might probably for clustering indexes to have the columns together otherwise I don’t see why that would impact the performance.