View previous topic :: View next topic
|
Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
|
|
|
|
Hi,
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.
Thanks |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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. |
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
|
|
|
|
Hi Rohit,
Thanks! for the reply. It's a new table and I am thinking to have Index on this IND_STATUS column. So I guess I can leave the column where it is now.
Thanks |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Yes, that should not harm you at all when comes to performance. Once it’s done make sure the query picks up the index that’s all. |
|
Back to top |
|
|
|