Re: File Join question

[rob@xxxxxxxxx]

Michael,

I argued for the sql to have the ability to create an index over a view 
for quite some time.  I've even submitted a DCR (that I believe is still 
under review).

But I think the SQL philosophy is that the index is separate from the 
view.  The view is just to select certain rows and/or columns and the sql 
optimizer will pick the best index to perform the job, saving you that 
effort.  The only time this theory writhes in the dust is:
- when you wish to access the file using traditional HLL's and not using 
imbedded SQL, and these have various requirements that the files actually 
have the keys required.  For example RPG's external file declared that 
your accessing it by key.
- When your view joins multiple files together and you want to sort by 
fields from more than the first file (which DDS still has this limitation 
although I've railed against that too.)

The big workaround, in these cases, is to use imbedded SQL instead.

Now, if there is just no way to build the right indexes, and SQL keeps 
doing a copy then I can see your pain in the performance hit.  Especially 
since I get the impression that multiple programs need to access this same 
path in a days time.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin