AW: SQL, Logical Files, Unions & Indexes.

Hi,

First how are you "joining" your tables?
Are you using joins or unions?

Creating an index as advices may help, even if it is not possible to specify
an index in an SQL statement.

May be I should explain a little how query optimization works:
1. The query optimizer rewrites the SQL statement. 
   If you specified a DDS described logical file the query optimizer
retrieves the field selection, 
   joins and select/omit clauses specified in the logical file and rewrites
the SQL-Statement 
   based on the physical files with these information.
   Only the classic query engine can retrieve these information from the DDS
described logical files.
   That means the SQL statement will be rerouted to the classic query
engine. 
   This rerouting may cost between 10 and 15 % of performance.

2. After the SQL statement is rewritten, the query optimizer builts or at
least validates an access plan.
   An access plan describes, which access plans (indexes or keyed logical
files) must be used, 
   which temporary objects (for example Hash tables, bitmaps, temporary
datastructrues ...) must be built.
   In this optimization process ALL access paths are checked. 
   Because the SQL statement was rewritten the optimizer did not know that
you originally specified an access path.
   If access path you in the logical file, you specified in the SQL
statement is used, it is nothing more than hazard.
   (And believe me the query optimizer is much better in opimization as you
ever can be)

3. After the access plan is built, the data path will be opened (ODP). 
   That means, the temporary objects described in the access plan are built 
   and filled with data by using either an index access or table scan. 

An index contains an access path like any keyed DDS described logical file
and a SQL index can be used with native I/O like any keyed logical file.
There are some architectural differences between an index an a DDS described
logical file. A DDS described logical file has a page size (reserved size in
the pool) of 8K while a SQL index has a page size of 64K. A DDS described
logical file can share an access path with an other logical file or an SQL
index if it has the same key or less key fields in the same sequence. A SQL
index can only share access path with an other SQL index and only if the key
fields are an exact match (the same key fields in the same sequence).
For example: 
I have a DDS described index with the following key fields Key1, Key2, Key3,
Key4
If I create now a SQL index with the same keyfields in the same sequence, an
new access path will be built.

I have a SQL Index with the following key fields Key1, Key2, Key3, Key4
If I create now a DDS described logical file with the same key fields in the
same sequence the access path will be shared and the DDS described logical
file inherits the larger page size of the SQL index.
If I create an other DDS described logcil file with Key1, Key2 the access
path will be shared.
If I create an SQL index instead with Key1 and Key2 a new access path will
be built.

... And yes the time is over where IBM proclaimed that no DBA is needed.

Sometimes I really hate, that the guys in Rochester think this is basic know
how! Because it is NOT!!!

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx] Im Auftrag
von eftimios pantzopoulos
Gesendet: Sunday, April 01, 2007 05:45
An: midrange-l@xxxxxxxxxxxx
Betreff: 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