RE: Is the query engine always smart enough to know when to generate an internal CTE on a query?

First CQE might only be used in a few situation.
Since Release 7.4 everything should be executed by the SQE.
... how the query optimizer really works is IBM's secret.
... we know several things and have learned a lot of things by experience,
learned a lot from the descriptions in the Query Optimization Book, learned
from analyzing Data Monitors, Plan Cache Snapshots and Visual Explain ...

For optimizing SQL Queries there are only 2 things we can affect.
1. Rewrite the query (or parts of the query) ... but I don't think replacing
WHERE Conditions with a CTEs will do a big difference. 
    The query optimizer analyses the complete query and can rewrite it
differently. 
    This can happen for example if you have several inner joins and only
where conditions for the 3 joined table. In this way he might rewrite the
query, so the 3rd table is read first and then the other tables are joined.
2. Creating the right indexes 

... but without knowing your database and having seen the query ... it is
hard to give you any advice.
Query analysis and optimization is not as easy.
If you need help, contact me directly

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect  
IBM Champion since 2020

"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!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Dan
Bale via MIDRANGE-L
Sent: Thursday, 6 August 2026 23:23
To: Midrange Systems Technical Discussion <[email protected]>
Cc: Dan Bale <dan.bale-EkiVQpMPPJH8R359/[email protected]>
Subject: Is the query engine always smart enough to know when to generate an
internal CTE on a query?

We've got a long-running query that has a tendency to get canceled because
it hits our temporary storage threshold.

The current table has a lot of conditions in the WHERE clause on the primary
table that greatly narrow the rows from that table.  There are also 15 left
joins on that primary table.  I've thought about modifying the query to use
a CTE on the primary table and move the conditions in the WHERE clause to
the CTE.

I'm just wondering if the query engine optimizes this anyway without the
explicit CTE.  Thoughts?

- Dan Bale
*** CONFIDENTIALITY NOTICE: The information contained in this communication
may be confidential, and is intended only for the use of the recipients
named above. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution, or copying of
this communication, or any of its contents, is strictly prohibited. If you
have received this communication in error, please return it to the sender
immediately and delete the original message and any copy of it from your
computer system. If you have any questions concerning this message, please
contact the sender. ***
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: [email protected] To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related
questions.

You can help support midrange.com by visiting https://donate.midrange.com
and making a contribution.

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

Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.

You can help support midrange.com by visiting https://donate.midrange.com and making a contribution.