Re: AW: AW: Ideas for logicals (index).

[CRPence <CRPbottle@xxxxxxxxx>]

  INDEX and /resulting order/ of rows are unrelated for the SQL of 
a RDBMS.  Although an INDEX *may* be used to effect ordering for an 
SQL result set, there is no guarantee that an INDEX would be used. 
Thus how or whether an INDEX may affect or may be used to effect any 
particular collation is not relevant to any given result set. 
Sharing is AFaIK an implementation detail only; i.e. not part of 
[the] standards.

  I have actually forgotten the algorithm [rules] for disallowing & 
allowing of sharing, that were specific to the SQL [binary radix] 
INDEX.  I thought at least in part that the restriction in sharing 
of access paths for an SQL INDEX was for lack of any ability to 
present that /sharing/, presented via the SQL [i.e. sharing details 
are not part of the catalogs].  I thought sharing except of an 
identical INDEX was prohibited by the SQL in order to prevent 
logically, [though the database will do so for the SQL, because the 
DDS LF may share such an access path.?] infinite loops that might 
occur if an UPDATE were implemented using index for the selection, 
such that a change to a lesser key might move the row into a 
position that then causes the row to be updated again, etc..  I know 
for non-SQL, i.e. RLA, the programmer & DBA [or sysadmin for restore 
for example which might increase sharing] must ensure that such a 
scenario does not become a problem [as a usage problem].

  create table abc (a int, b int, c int)
  ;
  insert into  abc values
     (1, 1, 1), (1, 1, 2), (1, 1, 3)
   , (1, 2, 1), (1, 2, 2), (1, 2, 3)
   , (1, 3, 1), (1, 3, 2), (1, 3, 3)
   , (1, 3, 5), (1, 4, 5), (1, 5, 5)
  ;
  create index abcabc on abc (a,b,c)
  ;
  create index abcab on abc (a,b)
  ;
  create index abca on abc (a)
  ;
  update abc set c=c+1
   where a<2 and b<3 and c>1

  If no sharing as with the SQL INDEX objects created in the above, 
using equivalent keyed DDS LF instead, the query optimizer having 
chosen the key AB to implement the update, due to sharing access 
path where the actual key is ABC, each change of C=C+1 makes the 
current changed row become eligible *again* for the next update of 
C=C+1.  Such an update query would not end until an overflow 
exception for the value of C.

Regards, Chuck

Charles Wilt wrote:
> Interesting thanks Mark!
>
> On Fri, Nov 20, 2009 at 12:07 PM, Mark S. Waterbury wrote:
> > See my reply to Birgitta's clarification.
> >
> > As I understand it, the SQL ANSI Standard requires that indexes
> > cannot be shared if the resulting order records would be
> > presented would be different because of the sharing.  DDS LFs
> > impose no such restriction. Thus, SQL can share access paths
> > with identical keys only.
> >
> > Hope that helps,
> >
> > Charles Wilt wrote:
> > > That's a new one to me.  I always thought SQL indexes could
> > > share another SQL index.
> > >
> > > A quick test seems to confirm it, but where is this
> > > documented?
> > >
> > > The "conformance to ANSI SQL standards" seems a bit fishy to
> > > me.  As I'd expect the sharing to be an implimentation detail
> > > tucked deep inside the OS.  I wonder if Chuck or somebody has
> > > more information.
> > >
> > > On Fri, Nov 20, 2009 at 10:08 AM, Mark S. Waterbury wrote:
> > >
> > > > Do not forget about Access Path Sharing.
> > > >
> > > > DDS logical files can "share" access paths (including with
> > > > pre-existing SQL indexes), while SQL indexes never share
> > > > access paths (due to conformance with ANSI SQL standards,
> > > > apparently.)
> > > >
> > > > So it is probably still a "good idea" to create SQL indexes
> > > > first, then create any DDS Logical Files, so that those LFs
> > > > can potentially share access paths with some of your SQL
> > > > indexes.
> > > >
> > > > Birgitta Hauser wrote:
> > > >
> > > > > Yep. BTW that's why I wrote "per Default". Sometime ago I
> > > > > asked Mike Cain something about indexes and page sizes.
> > > > > He suggested not to talk about the different page sizes
> > > > > that can be specified since V5R4 for logical files and
> > > > > SQL indexes. The IBMers will not speak about this subject
> > > > > either.
> > > > >
> > > > > Michael_Schutte wrote:
> > > > > > Instead of creating the SQL index first, can I just
> > > > > > specify 64k into the PAGESIZE keyword?  FYI our default
> > > > > > in  *KEYLEN not 8k.