RE: SQL Optimizer Issue

SELECT COUNT(*) FROM the_FILE

SELECT COUNT(*) FROM the_FILE WHERE PLAN = 'C'

SELECT COUNT(*) FROM the_FILE WHERE PLAN = 'B'

Would the above select statements help figure out the stats of the file?





"Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>@midrange.com on 10/14/2005
04:43:17 PM

Please respond to Midrange Systems Technical Discussion
       <midrange-l@xxxxxxxxxxxx>

Sent by:    midrange-l-bounces@xxxxxxxxxxxx


To:    "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc:

Subject:    RE: SQL Optimizer 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?

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
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Mark Allen
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/14/2005 03:14 PM
Please respond to
Midrange Systems Technical Discussion


To
midrange-l@xxxxxxxxxxxx
cc

Fax to

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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




---------------------------------
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.