October 15, 2006 at 5:22 pm
Lets say I have two tables: AccountData and AccountManager. I set my primary key AcctNo + AcctManager in the table AccountData. In AccountManager I set the primary key AcctManager.
This may seem like a lame question, but is AcctManager in AccountData indexed for purposes of a join with AccountManager? Thanks!
AccountData:
AcctNo
AcctManager
Etc
AccountManager:
AcctManager
ManagerName
October 16, 2006 at 4:53 am
There will be a unique index on the AccountData table containing the AcctNo and AcctManager fields. If the AcctNo field comes first in the index, it won't be optimal for the join you're talking about. If AcctManager comes first in the index, it will be. However, I'm sure you have other queries which would prefer it the other way around, i.e. you've got an account number and wish to find the manager. In that case you may choose to have two indexes on the AccountData table, one with AcctNo and AcctManager, the other with AcctManager and AcctNo. Be wary of doing that if there are a lot of inserts on that table though.
October 16, 2006 at 5:01 am
Thanks. In table AccountData AcctNo is unique. By adding AcctManager to the primary key it sounds like I have not gained any indexing benefit (or do I gain some benefit, just not as much as if it was first in the primary key)?
AccountData:
AcctNo
AcctManager
October 16, 2006 at 5:08 am
I'd expect there to be some benefit, but impossible to know without checking the execution plans for both versions, i.e. with AcctManager in the key and without. I'd remove AcctManager from the key if it doesn't do anything to uniquely identify an account (which is all a primary key is supposed to do) and check the exection plans for a join without any additional indexes, and with an additional, non-unique index on AcctMagaer on the AccountData table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply