RE: SQL, Logical Files, Unions & Indexes.

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Great discussion on the topic so far.  I'll try to add my 2 cents that might
help in your specific situation.

Please, don't exclude your SQL developers from building appropriate indexes.
There are number of situations where building an index is a no brainer, case
in point single key index on join columns.  These should not wait for a DBA
to be built.

Yes, DBA should be involved once the application is rolled out to production
or even to a test bed that realistically models production database (in size
and scope).

Joined LF is almost guaranteed not to help you in your specific situation (5
UNIONed tables).  
Approach this in a modularized fashion, namely, optimize each of the
individual SQL statements in the UNION.  This usually means building a right
index over individual tables.  
When running your UNIONed SELECT verify that indexes are being used over
individual tables, and in the ideal situation, bitmap merge is used in the
final select.  Chances of final select using bitmap merge on the final
SELECT are slim, but nevertheless, it doesn't hurt to be optimistic :)
BTW, EVI indexes have better chance of being utilized for bitmap merge, but
there are number of special circumstances that restrict their usage on
System i so I don't usually hold high hopes for them.
Even if bitmap merge can't be used in final SELECT, if your individual
selects are selective enough, this step of select can be performed in a
reasonable fashion using many other data structures available to the query
optimizer (hash groups, sort lists, temp indexes etc.).

As for VIEWs, I view them as simply stored SQL statements.  Where they prove
helpful is in simplifying final select statement you use in your embedded
SQL program.  Also, they may help query optimizer in caching saved access
plans so subsequent executions of the same or similar statement don't have
to be reoptimized.  In your scenario, view could prove quite helpful as
it'll hide the complexity of your multitable union.

Finally, you mentioned that 'proper' way to handle your scenario from design
point is to have a single table with a column signifying each state.  This
is one way for sure.  This would leave you free to pursue 3rd normal form
and create satellite tables with relatively static data for each state (i.e.
fiscal period, capital, population etc.).  But talking with realism in mind,
25 years old applications don't change their design just to be convenient to
a lonely SQL developer, so there's really no point in bringing this into
discussion (unless talking about designing a new app).

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
From: midrange-l-bounces+ebudimlic=centerfieldtechnology.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+ebudimlic=centerfieldtechnology.com@xxxxxxxxxxxx]
On Behalf Of eftimios pantzopoulos
Sent: Saturday, March 31, 2007 10:45 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL, Logical Files, Unions & Indexes.

I'm curious (and confused) about the following scenario:

We have an embedded SQL which selects records from a number of files over a 
number of libraries in order to provide a nationwide view of a business 
function.

The files are duplicate objects in different libraries, each library 
representing a state, and of course containing data pertaining to the state 
represented by the library.

The SQL brings all the data together into one ODP.

I thought we could speed it up by creating an index as advised by the index 
advisor, but of course an index can only be created over a single table.

However a logical file can be built over a number of files each in different

libraries.

If I create the logical however, I understand that SQL will route the 
request to the CQE instead of the SQE if a logical file is used and we would

take a 15-20% hit (according to a tuning red-book I read)

Are there any benefits to using a logical to provide an index or should we 
just create an index in every library we want to access the file from?


On a more philosophical note:

I get the impression that if we had a pure SQL based environment - in other 
words if only SQL tables, views, and indexes were used - then a programmer 
should only create the table required to hold the data, and a DBA would then

create the views and indexes required to support the functions required?

Thanks In Advance

Regards,
Mike Pantzopoulos

_________________________________________________________________
Advertisement: Find new & used iPods; designer clothing and more. Join free 
at http://www.ebay.com.au 
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Frover%2Eebay%2Ecom%2Frover%2
F1%2F705%2D10129%2D5668%2D323%2F4%2F%3Fid%3D3&_t=760348364&_r=Findnew&_m=EXT