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

No problem.  Thanks. RH
"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC01C49761@xxxxxxxxxxxxxxxxxxxxxxxxxx
> Ryan,
>
> I was wrong (see the message from Brigitta), IndexSnglCol would exist
> independently.
>
> So the concern you have wouldn't be a factor.
>
> My guess that your concerns are one of the reasons SQL indexes (access
> paths) work different than DDS described access paths.
>
> Sorry about the mis-direction.
>
> 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 10:28 AM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: 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@xxxxxxxxxxxxxxxx
> > A.local...
> > > 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.
> > >
> > >
> >
> >
> >
> > -- 
> > 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.
>
>