RE: To Key or Not To Key a Physical

Option 1 and Option 2 are not identical in your example. With Option 1 you
have two 2-field keys ([FieldA,FieldB], [FieldB,FieldA]) and with Option 2
you have two 1-field keys ([FieldA],[FieldB]).

Regardless, I believe I understand what you're asking and the answer is it
doesn't really matter, there will always only be two keyed access paths that
DB2 maintains.

As a general rule, I'm with Charles, you really ought to have a primary key
on your physical file. Most of the PC side development tooling pretty much
expects you to have it. Ideally it would be a meaningless, single-field
binary column (BIGINT or INT if possible), to serve as a surrogate key. But
it doesn't have to be, you could use your approach 2 (primary key using two
or more table columns).
Then build however many natural keys you want (either SQL indexes or keyed
LFs).

As for index-from-index reference... that doesn't make sense in this context
either. Are you referring to shared access paths? That has performance
implications only, not functional implications.
Unless you're referring to index-from-index capabilities query optimizer
possesses? That's a whole different topic, but again, it has performance
implications only.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: To Key or Not To Key a Physical

I tried looking online for more information about how indexing occurs and
couldn't pinpoint it.

Maybe an example will help.
I want two keys over a file:
Key1: FieldA, FieldB
Key2: FieldB, FieldA

Option 1 is to have an arrival sequence Physical and 2 Logicals.
Option 2 is to key the Physical (let's say Key1) and then make a Logical,
Key2.

I've always preferred Option 2 for the simple reason of: hey, 2 files
instead of 3; however I've been approached with the thought that Option2
will index over an index which would be less preferable to indexing over a
non-keyed Physical. Is this a valid concern?

Charles, you say that it's the general standard to always key the Physical.
I guess I'm looking for the reasoning behind that.

Thanks,
Kurt