Primary Keys

Work for a shop that has occasional issues of table locks. A few of the tables in question do not have primary keys. I know all the tables do have row level locking. Does the lack of a primary key cause any potential locking issues? Are there any other issues that would make adding a primary key to these tables a priority?

No primary key as in it doesn't have the constraint or there is no set of columns that form a unique key? The latter could cause locking problems, yes, as several to many rows will be locked for updates, deletes, etc. Every table should have a unique key. Period. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 11, 2016 at 7:11 PM, BRYAN KATULKA <bjkatulka@gmail.com> wrote: > Work for a shop that has occasional issues of table locks. A few of the > tables > in question do not have primary keys. I know all the tables do have row > level > locking. Does the lack of a primary key cause any potential locking issues? > Are there any other issues that would make adding a primary key to these > tables a priority? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bfe9fe8d2c0e2052b87c5e0

I agree with Art - every table should have a primary key. At a shop now that has many large tables (500M+ rows) and lots of large, messy (as in "all tangled up with other indexes") duplicate indexes. An index like that with a high level of duplicity is high overhead for the engine to maintain IF they're hot/very active. This is due to the way the btree handles rowids ... the rowids must stay sorted low to high WITHIN the btree. It's essentially "an index within the index" with each rowid pointing to the data row. (I'll stay away from frag'd or non-fragmented for ease of this discussion). A row gets modified (ins/upd/del) with a value that lives in the index: 1) the key value needs to find a home in the sorted btree if new/updated. 2) the rowid must find it's home within the existing list of sorted rowids for that key value as well (or a new list is created if new, etc.). These steps can cause shuffle/split/merge in the btree if necessary. We had a large index like this years ago that the month-end processes were dragging - as it turns out we were waiting on bitmap pages for the index due to the maintenance of it and high duplicity. Added another column to a composite - didn't make it unique, but made it less duplicate. Helped a ton. I taught this topic in the IDS Internals class for years. If anyone wants more detail, let me know at mark@markscranton.com. Fun topic to talk about. Understanding the internals of the btree structure can be very helpful in tuning/planning. HTH - Mark Scranton The Mark Scranton Group mark@markscranton.com

Thank you for the quick reply Art and Mark! So, these are a few of the tables in question: Is it too hard to go back and add the primary keys now? What is the best way to fix this? { TABLE "informix".p_group_station row size = 8 number of columns = 3 index size = 16 } create table "informix".p_group_station ( phys_group_id integer, log_station_id smallint default 0, station_mode smallint default 0 ); { TABLE "informix".p_group_function row size = 6 number of columns = 2 index size = 9 } create table "informix".p_group_function ( phys_group_id integer, function_id smallint default 0 );

Bryan: It looks like the first table has two indexes and the other has at least one. Are they UNIQUE indexes? If so, then THAT key is your primary key and you are fine. You could declare the same key to be a primary key constraint if you like. No problem there. However, if the indexes are not now unique, what would the primary key be? Which column (s)? Art On Feb 13, 2016 6:34 PM, "BRYAN KATULKA" <bjkatulka@gmail.com> wrote: > Thank you for the quick reply Art and Mark! > > So, these are a few of the tables in question: > > Is it too hard to go back and add the primary keys now? What is the best > way > to fix this? > > { TABLE "informix".p_group_station row size = 8 number of columns = 3 index > size > > = 16 } > > create table "informix".p_group_station > > ( > > phys_group_id integer, > > log_station_id smallint > > default 0, > > station_mode smallint > > default 0 > > ); > > { TABLE "informix".p_group_function row size = 6 number of columns = 2 > index > size > > = 9 } > > create table "informix".p_group_function > > ( > > phys_group_id integer, > > function_id smallint > > default 0 > > ); > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013a226888f783052baf8868