Hi,
1. What Release you are on?
2. Did you check which query engine runs the SQL statement (CQE or SQE)? -
Run the query through Visual Explain
3. Does it always need 30 minutes or did it take 30 minutes only for the
first time?
According what you describe it could have been executed by the CQE.
Putting the parenthesis around the sub-select could force the optimizer to
execute the sub-select first and then sort the result.
If the query is executed by the SQE it seems to be a bug that the sub-select
is executed including the order by. You may open a PMR at IBM.
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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Gerald Magnuson
Gesendet: Thursday, 18.6 2015 20:02
An: midrange-l@xxxxxxxxxxxx
Betreff: sql - why doesn't the query engine know this???
so I have a bill of material query (parent, child) joining to a item master
file (twice, once on parent, once on child) and a status file( joined on
parent)... I want the list ordered by parent...
select parent,child,t02.desc,t03.desc,osipo# from plan_bill t01 join items
t02 on t02.item = t01.parent join items t03 on t03.item = chlid join
ordstatus t04 on ositm# = t01.parent where parent like '%MTG%' and child
like 'S%' and osid = 'OS'
order by bprod ;
I have built all advised indexes, and when I run it takes 30 minutes to
return 800 records.
however, when I take off the order by, it runs in 2 seconds.
then, I tried: (parens around select statement)
(select parent,child,t02.desc,t03.desc,osipo# from plan_bill t01 join items
t02 on t02.item = t01.parent join items t03 on t03.item = chlid join
ordstatus t04 on ositm# = t01.parent where parent like '%MTG%' and child
like 'S%' and osid = 'OS' ) order by bprod ;
and that ran in 2 seconds...
--
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.
midrange.com
