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

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.
> 
>