Re: PK's enforced via constraint or unique indexes?

Yes Charles, I do remember our discussion.  It was immensly helpful.

This "Access Path Sharing" sounds fantastically advantageous...but of
course, raises a couple of questions.  If you have Table1 (col1  col2  col3,
col4, cvol5, col6); IndexCovering (col1, col2, col3, col4), IndexSnglCol
(col1):

SELECT col6 FROM Table1 WHERE col1=X.  Let's pretend col1 is just barely
selective enought that the optimzer would want to use IndexSnglCol.  Based,
on what you've stated about Access path sharing, I am concerned that
IndexSnglCol would not physically exist as a separate entity, but would
instead redirect to IndexCovering.

If this is true, you could end up with a situation were paging in necessary
pages from IndexCovering and then paging in pages from Table1 (for Col6)
will be more expensive than having performed a table scan on Table1.

Do you know how this is prevented?

Thanks for all the help (now and in the past). RH


"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC01C496EC@xxxxxxxxxxxxxxxxxxxxxxxxxx
> Ryan,
>
> DB2 on the iSeries is unique in that it allows the PF object to store an
> access path along with the data.  (Remember us going over this a couple
> of weeks ago?)
>
> When you define a primary key for a table, the unique index is stored
> inside the table object, thus you won't see it separate.
>
> As far as the additional unique index being defined.  It is redundant,
> but it doesn't really hurt anything as under the covers the iSeries
> implicitly shares the primary key index anyway.
>
> This "access path sharing" is automatic.
>
> If you have an index, key1, key2, key3 and later create an index key1,
> key2 the iSeries will not build a separate index, instead it will share
> the first one.
>
>
> HTH,
>
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Ryan Hunt
> > Sent: Tuesday, May 02, 2006 9:42 AM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: PK's enforced via constraint or unique indexes?
> >
> > I just want to confirm something about DB2/400...  99 Percent
> > of the tables
> > I have on our AS400 are JDE OneWorld tables.  Now, JDE does
> > NOT use Primary
> > Key constraints.  Rather, they use unique indexes to enforce
> > integrity.
> >
> > Now in the MS SQL world this is a virtual non-difference
> > because Primary
> > Keys are enforced via unique indexes anyway.  Typically, the
> > only advantage
> > of a PK contstaint is that is shows up with a specfiic type of object
> > attribute - so it can be useful in writing code.  And, there
> > might be an
> > inherent tendancy for an optimizer to favor PK constraint
> > (indexes) for
> > joins (another simple benefit of PK constraints over unique indexes).
> >
> > Does DB2/400 act similarly, or is there NO benefit to using a
> > PK constraint
> > over a unique index?  Also, if DB2/400 does use unique
> > indexes to enfore PK
> > constraints, I should be able see it as a 64K/page index used
> > within the
> > "Visual Explain" tool right?
> >
> > This question came up because I've got a developer that has
> > created a new DB
> > schema.  In this schema each table has a PK constraint defined AND an
> > identical unique index defined for the PK columns- which I expect is
> > redundant.  Yet, in Operations Navigator I only see the unque
> > index definded
> > as a SQL index object and I cannot view the constaint as an
> > object anywhere.
> >
> > Thanks in advance.
> >
> > Ryan
> >
> >
> >
> > -- 
> > This is the Midrange Systems Technical Discussion
> > (MIDRANGE-L) mailing list
> > To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>