IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

General Query on DB2 table design


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Thu Feb 14, 2019 12:49 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2569
Location: NYC,USA

PostPosted: Thu Feb 14, 2019 1:17 pm
Reply with quote

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
View user's profile Send private message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 116
Location: Bangalore

PostPosted: Thu Feb 14, 2019 1:44 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2569
Location: NYC,USA

PostPosted: Thu Feb 14, 2019 10:02 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need Help with on of the coalesce query DB2 4
No new posts Need to read duplicate rows from tabl... DB2 3
No new posts Hex Table COBOL Programming 2
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
No new posts Catalog table SYSSTATSFEEDBACK DB2 2
Search our Forums:

Back to Top