Bergquist, Brett wrote:
Yes, this is an old database that has been extensively used. So 2010 is not
out of the question.
I just finished running:
SELECT schemaname, tablename,
SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename)
FROM sys.sysschemas s, sys.systables t
WHERE s.schemaid = t.schemaid;
And all tables come back clean so the database appears to be okay from that
point of view.
I did a test and created a table like:
CREATE TABLE TEST_TAB_1
(
ID INTEGER PRIMARY KEY NOT NULL
);
CREATE TABLE TEST_TAB_2
(
ID INTEGER PRIMARY KEY NOT NULL
);
ALTER TABLE TEST_TAB_2
ADD CONSTRAINT TEST_TAB_2_FK_1
FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);
This creates two indexes on TEST_TAB_2 which surprised me. The first is a
unique index on ID and the second is a non-unique index on ID.
This is the part I was referring to before. I assume you are reading
the catalogs to see the 2 indexes. At a lower physical layer only one
index is created and both of these indexes are pointed at it. This
level of indirection happens somewhere in the system catalogs, but I
don't remember off hand where. If you monitor the files created and
dropped in seg0 in your test program you will see what physically is
happening.
That indirection is pretty low level, so not surprised that the
statistics level of the system sees 2 indexes. There definitely is
some optimization that could be had by making update statistics, and
possibly the optimizer aware that these 2 indexes are really the
same. I do think the optimizer should be smart enough to always pick
the unique index vs the non-unique one, and in that case it should not
even need the statistic info.
As a first pass I would concentrate on the problems the old row is causing.
/mikem
