RE: SQL Optimizer Issue

["Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>]

>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? 

Lies, damn lies and STATISTICS! What's the distribution of data in the
file? And is there an index that the optimizer can use to determine it?

Just a guess, but the statistics that are out there says there are 5
records that start w/S, 5 that start w/Q, 5 that start w/M and
30,000,000 that start w/J. So when it goes to process 'Jones' it assumes
it would do better building a new index. Actually, I'll guess the
problem isn't on the name field, but on the plan field. Bet the _vast_
majority of your data is on plan 'C' -- or at least the statistics the
analyzer is using think they are.

I'm not sure how you can dump the statistics of a file. Anyone? Anyone?
Bueller?

-Walden


------------
Walden H Leverich III
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Allen
Sent: Friday, October 14, 2005 5:23 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Optimizer Issue

when they run EXPLAIN over the SEQUEL it ALWAYS says it will use the
correct index

rob@xxxxxxxxx wrote:No obscure logical file that has a selection
criteria specifying plan='B' 
so it can't use that access path, is there?

Rob Berendt