This talk of the difference between DDS and SQL created access paths has caused me to question long and deeply held beliefs when it came to database design on the iseries. When designing databases, i'd always tried to follow these few simple guidelines (others may disagree with them, but let's not quibble over that part - it's always worked pretty well for me). 1. most physical files have a key, a unique one if appropriate. 2. logical files were built for any access paths needed for inquiries, or batch pgms that would be used daily or more (generally). 3. everything else would be handled via opnqry. 3. opnqryf would be used for record selection rather than creating a bunch of single purpose logicals. 4. if performance problems are a concern or are encountered, logicals are created as indicated to help out. How does the apparent superiority of SQL generated access paths change these rules? Would you make a point of replacing existing logicals with SQL indices where possible? Would you build SQL indices and views next to any logicals (or physical keys) that can't be directly translated to SQL? If not (or not always), why not and/or when? and one more question... Can you specify on an SQL view or index the Access path maintenance (MAINT keyword) or Access path recovery (RECOVER keyword)? If so, how? Thanks, Rick --------original message--------- midrange-l-request@xxxxxxxxxxxx wrote: > 1. Re: Query optimizer tells me to build an access path that > already exists (Clare Holtham) > >That's one of the indexes you ALWAYS want to build. This subject does show a glimpse of the differences between PFs/LFs and tables/views/indexes. I've seen that if an LF is created over a PF and both share the same key, then the LF file description shows the PF as owning the access path. But a SQL CREATE INDEX using the same field doesn't appear to share the existing PF access path; it seems that a brand new one is created. (There might be ways of getting LFs and SQL indexes to coincide better, but I haven't learned them.) Two obvious considerations come to mind -- double access-path maintenance and double space for storing the access paths. Tom Liotta
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.