db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Question on why indexStat deamon is being triggered
Date Sat, 31 Mar 2012 22:40:43 GMT
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

Mime
View raw message