RE: Which of the SYSIBM tables/views show the row count for

I'll chime in to offer a different perspective on the relationship of
constraints and maintenance overhead.

Primary, Unique and Foreign Key constraints are implemented implicitly using
indexing technology in DB2 for i5/OS.
Being that Primary & Unique constraints tell query optimizer explicitly "all
of these rows are distinct", they are a great peace of statistic to have
around for query optimizer to use.
Being that all 3 types of constraints I mentioned are indexed, you now have
additional query implementation options for query optimizer (i.e. index only
access).

Back to the overhead. True, updates/inserts/deletes will incur additional
overhead. That said, keep in mind that (non-scientific) observation is that
most applications have 25 to 1 ratio of reads versus writes (on average).
This is especially true for applications written in SQL.

My point is that by introducing constraints on System i, you're not only
enhancing database data integrity, you're potentially IMPROVING the
performance of your application(s).
I've seen this scenario played out more than once (the ever elusive "magical
index").

If, on the other hand, the write to read ratio I mentioned earlier doesn't
hold for your application (i.e. update intensive batch load), maintenance
overhead of additional indexes may trump the potential benefit you'd gain on
your read-only queries, so your mileage WILL vary.

Hope this helps people in decision to use or not use constraints in their
database. I believe that any INFORMED decision is a good decision,
regardless of widely adopted practices.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Which of the SYSIBM tables/views show the row count for

<snip>
We've had the UNIQUE keyword on logicals and keyed physicals forever, and
not always used it for the simple reason that it added overhead. Should
every primary key have a UNIQUE constraint? That's a business decision.
But if I choose not to have it, it doesn't make my database design bad. It
just means I trust my application and would prefer not to have the extra
overhead of the constraint.
</snip>