RE: Logical File or OPNQRYF or anyother way ?

Charles

I don't remember if I posted a reply to this bit - at any rate, if I did, it 
was to mention the combining of bitmaps from either regular or EVI indexes.

There is a concept of building an index from another index. The Query 
Optimization manual has this bit:

This message indicates that a temporary index was created from the access path 
of an existing keyed table or index.
Generally, this action should not take a significant amount of time or resource 
because only a subset of the data in the table needs to be read. This is 
normally done to allow the optimizer to use an existing index for selection 
while creating one for ordering, grouping, or join criteria. Sometimes even 
faster performance can be achieved by creating a logical file or SQL index that 
satisfies the index requirement stated in the message help.
Just thinking - a rare even some days.
Vern
-------------- Original message -------------- 

> > -----Original Message----- 
> > From: midrange-l-bounces@xxxxxxxxxxxx 
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta 
> > Sent: Monday, March 07, 2005 5:16 PM 
> > To: 'Midrange Systems Technical Discussion' 
> > Subject: RE: Logical File or OPNQRYF or anyother way ? 

---snip---
> > > For example, lets say you have three fields: key1, key2, key3. To 
> > provide 
> > > for all possible sorts, you'd need 9 separate logicals with the 
> > overhead 
> > > or performance penalties associated with maintaining the 
> > access paths. 
> > On 
> > > the other hand, you could simply use 3 indexes, one over 
> > each key. As 
> > > needed the DB2 query engine make composite keys from the separate 
> > indexes. 
> > > 
> > > Does this make sense? 
> > 
> > Actually, no it doesn't. How do you think this magic occurs? 
> > The nine 
> > different sort sequences are still required, and there is a very 
> > definite overhead when trying to combine two single-field 
> > keys to create 
> > a composite key. Like most indexing operations, this overhead can be 
> > disastrous on large files. 
> 
> Yeah, I shouldn't have said sorting here. Really what I was thinking about is 
> the way the query engine can AND or OR indexes together for selection. 
> 
> Off the top of my head, I'm not sure how the query engine deals with multiple 
> single key indexes when the ordering needed uses multiple keys. I might check 
> more into this. 

---snip---