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

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.