RE: SQL Optimizer Issue

Do a STRDBG, then run the query interactive and look at the job log, see
what index it is using when it runs quickly. Then you have to take a
look at that index and figure out why it is excluding it. You may have
to build more indexes just to provide the optimizer with enough
information to know when to use your index of choice.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Allen
Sent: Friday, October 14, 2005 4:15 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL Optimizer Issue

Posted for a colleague:
 
What I was told:  A SEQUEL select statement is run over a very large
(50-60 Million records), select by name and a plan field.  MOST of the
time results are returned quickly but for "some" names the statement
does not use the existing index and thus creates one, which causes about
a 20-30 minute response time.  IBM support to date has not been able to
help and they ahve submitted a PMR to IBM but no response yet and it is
becoming (is) an issue.
 
Example:
 
Name=Smith   Plan=B, quick response
Name=Jones Plan=C, takes forever
 
I was told that they have also tried it as a straight interactive SQL
and get the same issue.  They have not been able to find any rhyme or
reason as to why "some" names/plan combinations take so long.  Anyone
run across this or have any ideas?

                
---------------------------------
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.