IDS Forum

Re: Primary Keys

Posted By: MARK SCRANTON
Date: Friday, 12 February 2016, at 2:52 p.m.

In Response To: Re: Primary Keys (Art Kagel)

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

Messages In This Thread

• Primary Keys
  BRYAN KATULKA -- Thursday, 11 February 2016, at 7:11 p.m.
  • Re: Primary Keys
    Art Kagel -- Thursday, 11 February 2016, at 7:30 p.m.
    • Re: Primary Keys
      MARK SCRANTON -- Friday, 12 February 2016, at 2:52 p.m.
      • Re: Primary Keys
        BRYAN KATULKA -- Saturday, 13 February 2016, at 6:33 p.m.
        • Re: Primary Keys
          Art Kagel -- Saturday, 13 February 2016, at 6:56 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.