Re: To Key or Not To Key a Physical -- MIDRANGE-L

To add to what Charles said, the index on a PF is a part of that PF. If you left it out and created a second file, you are NOT effectively saving any disk space - the index will still be as big in either case. There is some header stuff in each object, however, that would be extra bytes in storage.

Indexes (LFs) are always built over the physical data - they are like tables whose columns are the index fields plus the relative record number that those index fields point to. At least, that's a conceptual view that makes sense to me.

LFs (indexes) are never built from indexes. That is a technique for building a temporary index that is used by the SQL optimizer. Elvis and I both mentioned this earlier. Of course, Elvis and I used to work together!

HTH
Vern

Charles Wilt wrote:

Walden has pretty much answered this, but let me reiterate.

Remember, the i is object-based.

With a keyed physical, the *FILE object has (conceptually) two parts,
a data part and an index/access path.

An additional logical file contains just an index/access path part
over the data part of the physical. It doesn't care that the physical
*FILE object also has an access path part.

As for why primary keys are standard, that's pretty much a result of
SQL and relational DB theory. Having a primary key makes life easier
if you are interfacing externally. Also, for example CPYF
MBROPT(*UPDADD) won't work without a unique keyed physical.

Way back when, it was recommended not to have key physicals due to
recovery concerns, but that hasn't been applicable in a long time.

Charles

On Thu, Mar 26, 2009 at 2:41 PM, Kurt Anderson
<kurt.anderson@xxxxxxxxxxxxxx> wrote:

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

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, March 26, 2009 1:21 PM
To: Midrange Systems Technical Discussion
Subject: Re: To Key or Not To Key a Physical

Huh?

If you create a logical with the same access path as the physical, the
logical will share the PF access path.

Now-a-days, the recomendation is to always define a primary (aka
unique, non-NULL) key for the physical.

Charles

On Thu, Mar 26, 2009 at 2:13 PM, Kurt Anderson
<kurt.anderson@xxxxxxxxxxxxxx> wrote:

If I'm going to have multiple logical files over a physical file, is it better to not key the physical file or is there no implication if I do key the physical file (and reduce the number of logicals needed by 1)?

I'm not that familiar with how indexing works, and the concern is that a logical over a keyed physical will be indexing from an index.

Thoughts?

Thanks,

Kurt Anderson
Sr. Programmer/Analyst
CustomCall Data Systems
--
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.