AW: SQL Question

Hi Jeff,

May be you'll be disapointed, but if your complete select statement looks as
you showed (with the like predicate as single WHERE clause), always a table
scan will be performed, no way around. 

The optimizer cannot use any access path (neither a SQL index nor a DDS
described logical file, because: 
1. The optimizer can only use access paths if the original fields are
specified on 
   the left hand of the comparison sign or predicate, 
   i.e. adding TRIM and concating two fields modifies the original fields 
   and an access plan cannot be used. (Trim(HHORN1) concat Trim(HHORN2) like
'%user data%'). 
2. Using the percent sign in the first position after the like ('%user ...) 
   will also prevent the optimizer to use an index.

Because of the Order By clause an additional sort of the result is
necessary. Adding Fetch first x rows only also will not speed up the
execution, because first ALL rows must be found, a temporary object built
and the result sorted. After the first 70 rows will be returned.

If, according to your statement, you could use the DDS described logical
file where HHORN1 must not be blank, you may change your SQL-Statement as
follows:
Select *
  From OEHISHED
  Where HHORN1 > ' ' and (HHORN1 like '%user data%' or HHORN2 like '%user
data%')
  Order By HHORN1

Writing the SQL-Statement in this way, the CQE-Optimizer may use the access
path in the DDS described logical file.
(SQE optimizer will not be used due to the fact that DDS described logical
files with select/omit clauses are defined over the base table)

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@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Young
Gesendet: Thursday, March 29, 2007 21:44
An: midrange-l@xxxxxxxxxxxx; rpg400-l@xxxxxxxxxxxx
Betreff: SQL Question


I have an application that needs to search a physical file based on the
value entered by the user appearing anywhere in either of 2 fields, each of
which is 50 bytes. The SQL that I have set up for this is as follows:
Declare data Cursor for Select * from OEHISHED where TRIM(hhorn1) ||
TRIM(hhorn2) like('%user data%) order by Hhorn1. The statement works fine,
but the file in question has a very large number of records and the initial
OPEN of the cursor takes over 30 seconds.  This is an interactive program.
Running the program in DEBUG mode, I do not get any recommendation for
indexes, just a statement that the optimizer selected arrival mode. Does
anyone have any suggestions for improving the time to open the cursor? Once
I have opened the cursor, the select takes almost no time. I am processing a
maximum of 70 records per selection, and am using the feature FOR 70 ROWS on
the FETCH.

There is a logical file keyed by HHORN1 with a selection criteria that
HHORN1 must not be blank, but the optimizer rejects this file due to the
static select. When I remove the select, the optimizer rejects this file due
to performance reasons. It produces a recommendation that I create an index
using '0' of the leftmost order by fields.

TIA,
 
Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions
V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3 IBM  Certified Specialist- e(logo)server i5Series Technical
Solutions Implementer V5R3


 
____________________________________________________________________________
________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html